Well, I believe it is in 3NF even with ‘id’ present. Also the ‘order no’ and ‘product no’ are not seperate keys by themselves but are rather a combined composite key. The individual key fields, you mentioned, are not keys by themselves as they would be non unique.
I personally find it useful to retain the ‘id’ field as it is normally an auto increment field and allows me to select records from the table in order of data entry.
Sent from my XT316 using Tapatalk 2
yes, that usually works, except in professional scenarios
also, could you get your XT316 to please not include that last line in each post – that’s what signature files are for
Not that I see what difference it makes where the signature comes from I think I haved changed the setting to remove it. Also Tapatalk doesn’t have signature files so your suggestion is unworkable for me.
Back to the topic: The ‘id’ field is used by professional projects like djangoproject.com and I think Ruby on Rails does also so I am not sure what “professionals” you are referring to.
i meant sitepoint forum signature
the use of an auto_increment is not problematic, it is relying on it for sequence of insert
if you want sequence of insert, use a datetime column
surrogate keys should be used for uniqueness and absolutely nothing else
Yes, thats what I understood. However I have no access to sitepoint signatures from Tapatalk. (I suspect they don’t even work from Tapatalk.) If this site wishes to welcome Tapatalk users then I would hope it would not make unreasonable requests and thereby alienate mobile users.
Most mature database engines should allow for resequencing of auto increment fields should it be required. However I agree date time fields are more useful in that context.
All keys should be unique, not just surrogate, otherwise its not unique and therefore not strictly a key either. Individual fields comprising a composite key may not be unique but then its only the composite that is a key, not the individual key components.
One argument in favour of ‘id’ fields over a date field is that the semantics are clearer for ‘id’. With a date field you have to specify what time zone it refers to. Is it server time? browser time? Its worse if their are multiple servers in multiple time zones. So I think I will revisit my last comment and say ‘id’ is better but only if its done right with the id’s kept in proper sequence. However I believe it is easier to do ‘id’ right than a date field.
[This is getting a bit off topic as none of this has to do with 3NF. Maybe better to discuss the relative advantages of ‘id’ fields vs date fields in another thread.]
I personally would keep well clear of using a data field as a PK. Say you were using a date field for a PK, and the server is located in say Australia. Something happens and you end up with your site migrated onto a server located in California. You’re going to get anomalies in the data as records added between the time of the sever move over 19 hours (time difference between California and Eastern Australia) will appear to be newer then the existing records.
Edited by Debbie…
So, r937, if I add a Surrogate Key like “id”, does having that along side the Composite Natural Key formed by “order_no” and “product_no” break 3NF or not?
Some people say “Yes”, others imply “No”.
And that was one of my big questions in my OP because I have adopted the approach of every Table having an AutoIncrement “id” as the PK, and then making fields like “order_no” + “product_no” a Composite Unique Key to ensure physical uniqueness while still getting the benefits of the Surrogate Key.
(I probably won’t stop that approach, but I just wanted to keep my honest and verify if what “I” thought was 3NF was indeed among Database Experts?!) 
Sincerely,
Debbie
I wonder what the definition of a database “expert” is. Does everyone get to nominate themselves? 
Its interesting that on sitepoint forums even facts are up for debate.
Maybe just do whatever you feel is right seeing everyone is going to have their own opinion.
If wikipedia carrys any weight on sitepoint forums you can find the article here: http://en.m.wikipedia.org/wiki/Third_normal_form
Every forum on SP has a few “experts”.
In this forum, r937 is definitely one of the best!!
If and when he blesses my designs, I usually sleep better…
Debbie
Hehe, anyone who doesn’t use TapaTalk is not likely to understand how it works, so it’s an innocent mistake that you can just ignore. Yes, TT focuses just on content and strips out most of the distracting content, such as sigs, ads and what not.
I am sure everyone has their own opinion on who is “definitely one of the best”.
However, I still think wikipedia is worth a look. Sitepoint “experts” don’t always trump wikipedia in my opinion.
At the end of the day its your choice and whichever one you make it will be a learning experience for you.
whoa… it might, if we could undestand it
that gobbledygook may be fine for a university classroom, but otherwise it’s severely lacking, if i may say so, in practicality
at the risk of being accused as a self-professed expert, i’m going to answer debbie, since she asked me, and say that the answer is no, it does not break 3NF to have both a surrogate PK and a composite alternate unique key
AMEN!!! :tup:
(I’m re-reading it, and still trying to figure out what it says?! Feel like I’m in a graduate Math class… )
Coming from you, r937, that will make me sleep better, because I was starting to fear that maybe I drifted away from “good” Database Design.
Sounds like what I am doing is okay, and the example I gave was modeled properly.
As always, THANK YOU!!
Sincerely,
Debbie
P.S. If you need some salve for those bite-marks from Kiwiheretic, just holler!! 
Well, you can’t win them all. I was actually just trying to help. You’ll get far worse bites than that out in other forums. Freenode springs to mind.
I didnt realize I was going to end up in what feels like a presidential election race and people have their favourite candidates for all sorts of reasons and one cannot fathom why.
Also I assume r937 has areas in which he shines. I just happen to have a different opinion in this case.
Anyway time for me to unsubscribe from this thread.
Thanks to everyone for helping to resolve things.
We all know that everyone has different areas of lesser or greater ability, and different ideas of what is best practice.
As the topic of this thread has been adequately discussed.
Thread Closed