Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Join Date
    Jan 2010
    Posts
    19

    Neatest way of modelling zero-or-one to many rels?

    What do you think it the neatest way of modelling 'zero-or-one to many' relationships, using a nullable foreign key or an association table?

    Example: a project can have one manager assigned to it, or be unassigned.

    [Project Mgr] 0..1 <------ 0..n [Project]

    An association table is a bit of an overkill, but a nullable FK 'smells' a bit wrong, so I'd like to hear your opinions.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nullable FK
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Nullable FK.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Nullable FK.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, all right... nullable FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Anna-J View Post
    What do you think it the neatest way of modelling 'zero-or-one to many' relationships, using a nullable foreign key or an association table?

    Example: a project can have one manager assigned to it, or be unassigned.

    [Project Mgr] 0..1 <------ 0..n [Project]

    An association table is a bit of an overkill, but a nullable FK 'smells' a bit wrong, so I'd like to hear your opinions.
    I would do it like this:

    Code:
    CREATE TABLE ProjectProjectMgr
    (ProjectId INT NOT NULL PRIMARY KEY REFERENCES Project (ProjectId),
     ProjectMgr INT NOT NULL REFERENCES ProjectMgr (ProjectMgrId));
    (which may be what you mean by an "association table").

    If a project doesn't have a project manager then it doesn't need a project manager Id - much less a null Id. Only add nulls into a schema where there is some good reason for doing so. And it pays to be doubly cautious when considering nullable foreign keys because they have significant disadvantages.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas View Post
    Only add nulls into a schema where there is some good reason for doing so.
    okay, provided that i get to decide what good means

    Quote Originally Posted by dportas View Post
    And it pays to be doubly cautious when considering nullable foreign keys because they have significant disadvantages.
    o rly?

    significant how, if i may ask?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2010
    Posts
    19
    Nice to see that there's a consensus that nullable FKs are ok to use. A nullable FK it is, then!

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I could have told you before people started responding that everyone bar one would have recommended that.

    Also, I was tempted to say so initially but left it, but smell is not ordinarily a recognised stage in database design. Make whatever decisions you believe you can justify but justify them with reason. I am sure it is a small olfactory step to thinking that shoving an autonumber on to a table smells like normalisation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Anna-J View Post
    Nice to see that there's a consensus that nullable FKs are ok to use. A nullable FK it is, then!
    Certainly if you want to make design decisions based on acclaimation on DBFORUMS. Even though I disagreed I wouldn't have wanted you to follow my example just because you read it here. Even less so if it happened to be the majority view. A million people are always wrong.

    What I would suggest is that you study some decent books on database design and make sure you understand what are the potential advantages and disadvantages of nulls. Then you'll have more information with which to make up your own mind.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Association table ONLY, very rarely you'd use a "nullable" FK. I usually use them after the fourth Heineken.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dportas View Post
    it pays to be doubly cautious when considering nullable foreign keys because they have significant disadvantages.
    Quote Originally Posted by dportas View Post
    make sure you understand what are the potential advantages and disadvantages of nulls.
    Dave - I know most of the disadvantages of NULLs. With the exception of RDBMSs that stick to the ISO SQL standard where it comes to NULLable composite foreign keys (courtesy of you) I don't know of any NULL issues that are specific to foreign keys.
    Care to enlighten?
    In case it is relevant, the longer I work in the field the more and more dissatisfied I am becoming with NULLs. I'm not yet at the point that I would eliminate them from my every schema but I am moving in that direction.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by rdjabarov View Post
    Association table ONLY, very rarely you'd use a "nullable" FK. I usually use them after the fourth Heineken.
    Quote Originally Posted by dportas View Post
    Only add nulls into a schema where there is some good reason for doing so.
    Still waiting for a good reason for NOT using a NFK (outside of Happy Hour).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Still waiting for a good reason for NOT using a NFK (outside of Happy Hour).
    so am i, and i asked before you did
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    One reason for not using NULL-able Foreign Keys is that they force you to use outer joins in order to get correct answers, and to cope with the results of unmatched keys (many more NULL values) that those joins imply. This is a complete "straw man" in my mind, it is a factually correct but practically irrelevant point to me.

    A better reason in my mind is that Foreign Keys imply a relationship. I usually track many details about a relationship like when it began, when it ended, etc. When I model things that have relationships, I normally treat the relationship itself as a "thing" to be modeled because that fits well with my view of my environment.

    I see my environment as a rich and varied place. I'm rarely willing to compromise and represent things very simply in order to make the code very simple because that has never worked to my benefit. I always end up coming back and implementing most if not not more than I originally imagined, and then I have to deal with the "lost data" and the complexity of a system/schema upgrade on what is often a critically important system... This just is bad business to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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