SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 37
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Derived Key, Natural Key and 3rd NF

    Let's say I have a many-to-many relationship like this...

    Code:
    ORDER --||-----|<-- ORDER_DETAILS -->|--------||-- PRODUCTS

    And in my junction table, I have these fields...

    Code:
    ORDER_DETAILS table
    - id
    - order_no
    - product_no
    - quantity
    - order_price
    and so on...

    Questions:

    1.) Does having both a Derived Key ("id") and Natural Keys ("order_no", "product_no") invalidate 3NF?

    2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?

    3.) Is there anything that would prevent this table from being in 3NF?

    Sincerely,


    Debbie

  2. #2
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    307
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    3NF is best described as saying everything in the table depends on the primary key, the whole primary key and nothing but the key.

    Another way to look at is to say there should be no redundant cells in your table.

    Sent from my XT316 using Tapatalk 2

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,869
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    1.) Does having both a Derived Key ("id") and Natural Keys ("order_no", "product_no") invalidate 3NF?
    Yes - you wouldn't have the derived key in your 3NF logical design - you'd add it to the physical design if there are reasons for not using the natural key as the primary index.

    Quote Originally Posted by DoubleDee View Post
    2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?
    No.

    Quote Originally Posted by DoubleDee View Post
    3.) Is there anything that would prevent this table from being in 3NF?
    Yes, to convert that to 3NF you'd delete the derived keys. Derived keys only exist in 3NF when there is no combination of fields that could form a natural key.

    Your logical database design should be fully normalised but the actual physical design that you implement may undo normalisations for a variety of reasons (eg, the natural key being too long or involving too many fields, or the database accesses being 99% writing and almost no reading of data)
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    307
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I don't think I agree. There is certainly a reason not to have wide tables but I have not found a good reason yet to have duplicated data in tables (which is basically what non-3NF causes or allows to happen).

    Also having alternate or secondary keys in a table doesn't, in itself, break 3NF.

    Sent from my XT316 using Tapatalk 2

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1.) Does having both a Derived Key ("id") and Natural Keys ("order_no", "product_no") invalidate 3NF?
    Not really, because 3NF is not about how you create the PK, it is about how the rest of the data depends on the PK.
    Or as Codd put it: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key."

    Your surrogate key would be generated to be unique in the table so this table itself makes the id dependant on the candidate key of order_no+product_no.

    In fact, your id would be an orderline_no, which is something you want as way for your customers to reference in their order.

    2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?
    Composite keys are just keys, they change nothing about the first three NFs.

    3.) Is there anything that would prevent this table from being in 3NF?
    Not as long as you put your constraints in place properly.

    In fact, without the id column joining requires fun queries like:

    SELECT * FROM ORDER_DETAILS INNER JOIN othertable ON (ORDER_DETAILS.order_no, ORDER_DETAILS.product_no) = (othertable.product_no, othertable.product_no);


    As a sidenote to make your life easier:
    Don't use the nondescriptive name "id" for the surrogate key. The reason is that many tables will get this 'id' column and when you join them, the result will get several 'id' columns that you cannot tell apart in your application. You can work around that by aliassing the columns in the queries (SELECT orderdetails.id AS orderdetail_id), but you can imagine how that's going to work; you'll forget aliases, you'll alias the wrong id's, you'll use different aliasses, poop will fly. But the simplest argument is that if you are going to provide a different name for the id colujmns in just about every query, why not just name the column so that you don't have to alias it in the first place? :-)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    But the simplest argument is that if you are going to provide a different name for the id colujmns in just about every query, why not just name the column so that you don't have to alias it in the first place? :-)
    you make a great case, but i'm gonna go ahead and disagree anyway

    use "id" for the PK when it's an obvious surrogate key (like an auto_increment)

    use "entity_id" for every FK that references the PK

    that way, in your queries, you can immediately discern the one-to-many relationships involved

    nothing worse than trying to understand a query and every key is named foo_id and bar_id and qux_id

    trust me, a column alias is the least of your worries, clear and unambiguous relationships are worth it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    trust me, a column alias is the least of your worries, clear and unambiguous relationships are worth it
    In my experience nondescript names like 'id' and aliases provide 90% of the ambiguity. :-)

    But we agree that you should use names that make clear what you are referering to. How you do that is a matter of personal preference.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    In my experience nondescript names like 'id' and aliases provide 90% of the ambiguity. :-)
    by this comment, i'm not sure you understood my point

    "id" used only for PK, and "foo_id" used only for FK

    that's clearly unambiguous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "id" used only for PK, and "foo_id" used only for FK
    that's clearly unambiguous
    :-) Untill you do a join, when you'll have two "id" columns and where you cannot alias foo.id to foo_id anymore because that's already a real column in the other table.

    Yes, I am pedantic :-)

    Ideally, and I admit I rarely do this, you'd prefix the PK and FK with pk_ and fk_ so you'd get pk_foo_id and fk_foo_id. Then when you join foo to bar you have pk_foo_id, pk_bar_id and fk_bar_id. The only occasion where you can get ambiguity then is if you join multiple tables that have an FK to the PK, and you're doing an OUTER JOIN. Yopu could solve that by mentioning the relation in the FK, but that's taking it very very far indeed: fk_bar_foo_id, which just looks silly.

    But, this is above all a matter of taste and what you're used to. I put this in here mainly to show that there is no generally accepted "best practice" for this, each method has it's pro's and con's.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    :-) Untill you do a join, when you'll have two "id" columns and where you cannot alias foo.id to foo_id anymore because that's already a real column in the other table.
    you wouldn't need to see both foo.id and bar.foo_id, because in a join they'd be equal!!

    unless of course you're using the dreaded, evil "select star" to bring in more columns than you really need
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We're getting a little distracted by this discussion :-)

    Quote Originally Posted by r937 View Post
    you wouldn't need to see both foo.id and bar.foo_id, because in a join they'd be equal!!
    They wont be equal in a LEFT JOIN or in any other join that does not require that foo.id=bar.foo_id.
    In fact, they will only be equal if you do a straight INNER JOIN on foo.id=bar.foo_id. Which are the simplest queries where you typically don't get confused about where the fields are coming from.


    unless of course you're using the dreaded, evil "select star" to bring in more columns than you really need
    You do have a colourful way of saying "it's usually not the best choice". :-)

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    We're getting a little distracted by this discussion :-)
    Yes, Vinny, you are being very distracting... (This thread isn't about how you like to label fields.)


    Debbie

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    We're getting a little distracted by this discussion :-)
    you started it... "As a sidenote to make your life easier:"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, Vinny, you are being very distracting... (This thread isn't about how you like to label fields.)
    I'm so sorry i tried to help.

    you started it... "As a sidenote to make your life easier:"
    I just gave some extra advice, but apparently contradicting the local experts is "not done" here, as it appears to be on most forums.

    Well nevermind, I'll leave you to handle it.

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Quote Originally Posted by DoubleDee
    1.) Does having both a Derived Key ("id") and Natural Keys ("order_no", "product_no") invalidate 3NF?
    Yes - you wouldn't have the derived key in your 3NF logical design - you'd add it to the physical design if there are reasons for not using the natural key as the primary index.
    So having a Derived Key ("id") and a Natural Composite Key ("order_no" + "product_no") in the Physical Design breaks 3NF?



    Quote Originally Posted by felgall View Post
    Quote Originally Posted by DoubleDee
    2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?
    No.
    I used to know this stuff cold, but when I went to refresh my memory last night, I found link after link of *confusing* and often very theoretical descriptions.

    From what I was able to gather, if you have a Composite PK, you need to make sure that every non-Key field is dependent on the *entire* Composite PK.

    Is that correct?

    And if it is, and I had to defend the fields in my OP, then can you help me figure out how to do that?

    I guess my response would be that "quantity" is a function of the Order and Product together. Likewise, "order_price" is a function of the Product at the time of the Order.



    Quote Originally Posted by felgall View Post
    Quote Originally Posted by DoubleDee
    3.) Is there anything that would prevent this table from being in 3NF?
    Yes, to convert that to 3NF you'd delete the derived keys. Derived keys only exist in 3NF when there is no combination of fields that could form a natural key.
    Is that true for the Physical Model as well?

    Sincerely,


    Debbie

  16. #16
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    I'm so sorry i tried to help.

    I just gave some extra advice, but apparently contradicting the local experts is "not done" here, as it appears to be on most forums.

    Well nevermind, I'll leave you to handle it.
    Start another thread on what you think about Field Naming and I'll tell you my thoughts there.

    My goal here is to make sure my *rusty* memory of 3NF is correct.

    Sincerely,


    Debbie

  17. #17
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    307
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kiwiheretic View Post
    3NF is best described as saying everything in the table depends on the primary key, the whole primary key and nothing but the key.

    Another way to look at is to say there should be no redundant cells in your table.

    Sent from my XT316 using Tapatalk 2
    Ok, I see where I went wrong.

    1NF is that table data should be atomic (MyBB forum software heartily breaks this rule.)

    2NF is every table field depends on the key (but not that you can't have secondary keys or composite keys.) This was my error in my first post to this thread.

    3NF is that no table field should depend on only part of a composite key because that would cause duplicates.

    1NF, 2NF are the obvious cases. 3NF is about not duplicating table data in order to satisfy a bad table design.

    There is nothing in 3NF to say you can't or shouldn't have secondary keys.

    PS: I'm no resident sitepoint expert. I doubt anyone is censured for disagreeing with me. Maybe if we learn to leave our egos at the front entrance we might enjoy the conversation more. After all its only a forum and why do we want to let the odd spate of immaturity ruin our day. Time to get a bit of perspective folks.

    Sent from my XT316 using Tapatalk 2

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    ... but apparently contradicting the local experts is "not done" here, as it appears to be on most forums.
    where did this come from? i loved our interchange, and i hope to have more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So having a Derived Key ("id") and a Natural Composite Key ("order_no" + "product_no") in the Physical Design breaks 3NF?
    there are no normal forms in physical design -- just tables


    Quote Originally Posted by DoubleDee View Post
    ... if you have a Composite PK, you need to make sure that every non-Key field is dependent on the *entire* Composite PK.
    correct

    see? this stuff is easy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there are no normal forms in physical design -- just tables

    correct

    see? this stuff is easy
    So is the table in my OP in 3rd NF (if the "id" field was removed)?

    Here it is again...

    Code:
    ORDER_DETAILS table
    - order_no (PK1)
    - product_no (PK2)
    - quantity
    - order_price
    Sincerely,


    Debbie

  21. #21
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    307
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So is the table in my OP in 3rd NF (if the "id" field was removed)?

    Here it is again...

    Code:
    ORDER_DETAILS table
    - order_no (PK1)
    - product_no (PK2)
    - quantity
    - order_price
    Sincerely,


    Debbie
    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

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kiwiheretic View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    307
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kiwiheretic View Post
    Also Tapatalk doesn't have signature files so your suggestion is unworkable for me.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    307
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i meant sitepoint forum signature
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •