SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    foreign key-auto increment

    I have 3 tables. 2 child and 1 parent.

    The child tables have foreign keys that reference the parent.

    I assume(and tell me if I am wrong) that the foreign keys in the child tables(which also serve as Primary keys in these table) should not be set to auto-increment
    since their values will be taken from the parent table.


    Am I right?

  2. #2
    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)
    you are right, they should not

    but why are the FKs also PKs in their table? this means the 2 child tables are each in a one-to-zero-or-one relationship with the parent -- perhaps you should think about merging them all into one table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you are right, they should not

    but why are the FKs also PKs in their table? this means the 2 child tables are each in a one-to-zero-or-one relationship with the parent -- perhaps you should think about merging them all into one table
    Well...here is the rational.

    This site will have 2 kind of users, business users and end_users.
    Each of these will be in separate tables(the foreign keys will be there)

    And the foreign keys from the above 2 will reference a table named credentials where the passwords and e-mails will be kept of the above 2.
    So, for organisational purposes I have these 3 tables, I think it is better that a big monolithic table where business user and end users get mixed up.

    What do you think?

  4. #4
    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)
    sounds like a supertype / subtype arrangement

    could be quite acceptable, depending on how you define "better than a big monolithic table"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If You are talking about
    "I think it is better that a big monolithic table where business user and end users get mixed up."
    or
    "Each of these will be in separate tables"
    You are talking about
    Single Table Inheritance - table per concrete class
    Look at
    http://martinfowler.com/eaaCatalog/c...heritance.html
    http://martinfowler.com/eaaCatalog/c...heritance.html
    http://www.martinfowler.com/eaaCatal...heritance.html

    Use that best suits for problem

    Of course for some cases You can't use auto increment or sequence
    Best way is that ID is generated by object itself for example GUID

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I assume business users and end users have *very different data/column requirements?
    The only code I hate more than my own is everyone else's.


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
  •