SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 37 of 37
  1. #26
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    277
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.

  2. #27
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    277
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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.]

  3. #28
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #29
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Edited by Debbie...


    Quote Originally Posted by r937 View Post
    surrogate keys should be used for uniqueness and absolutely nothing else
    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
    Last edited by DoubleDee; Nov 24, 2013 at 18:01. Reason: Rewrote

  5. #30
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    277
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I just wanted to keep my honest and verify if what "I" thought was 3NF was indeed among Database Experts?!)
    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

  6. #31
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kiwiheretic View Post
    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

  7. #32
    It's all Geek to me silver trophybronze trophy
    ralph.m's Avatar
    Join Date
    Mar 2009
    Location
    Melbourne, AU
    Posts
    24,109
    Mentioned
    448 Post(s)
    Tagged
    8 Thread(s)
    Off Topic:

    Quote Originally Posted by Kiwiheretic View Post
    If this site wishes to welcome Tapatalk users then I would hope it would not make unreasonable requests and thereby alienate mobile users.
    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.
    Facebook | Google+ | Twitter | Web Design Tips | Free Contact Form

    Forum Usage: Tips on posting code samples, images and more

    Forrest Gump: "IE is like a box of chocolates: you never know what you're gonna get."

  8. #33
    SitePoint Addict
    Join Date
    Aug 2013
    Location
    New Zealand
    Posts
    277
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    In this forum, r937 is definitely one of the best!!
    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.

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

  10. #35
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    AMEN!!!!!

    (I'm re-reading it, and still trying to figure out what it says?! Feel like I'm in a graduate Math class... )


    Quote Originally Posted by r937 View Post
    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
    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!!

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

  12. #37
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,014
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)
    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


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
  •