Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2012
    Posts
    8

    Unanswered: One to Many Relationship

    Are there any potential pitfalls of using NULL to represent "ALL" records?

    For example, if you have a table that has a Planet_Key and a Moon_Key record for every Moon a planet has (say in dbo.Planet), but instead you just want to put NULL in the Moon_Key slot and you now know that the Planet_Key should contain every moon in the dbo.Moon table that has that Planet_Key.

    Would one be better off having a bit field in the Planet table stating that it should have all moons or a different solution than using NULL?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand enough your issue.

    Anyway, a Planet have zero to many Moons. And a Moon has(or "belong to") a Planet.
    (There are binary star(or twin star). But, I didnt heard binary Planet.)
    So,
    Planet table(PK: Planet_key) has no Moon_key.
    Moon table(PK: Moon_key) has a Planet_key.
    That's all.
    Last edited by tonkuma; 02-15-12 at 19:39.

  3. #3
    Join Date
    Jan 2012
    Posts
    8
    I'm asking if theres an issue where instead of having 1 record per relationship,
    you use NULL in the Moon_Key in the Planet table to represent that the Planet has all of those moons.


    So you have a planet_moon table that has.. [Planet_Key, Moon_Key] 1,1 1,2 1,3 so Planet 1 has Moons 1,2,3
    vs.

    1,NULL

    Where null just means "all moons in the moon table", and if there are there any pitfalls with that system. Using NULL to represent "ALL".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Planet table(PK: Planet_key) has no Moon_key.
    Moon table(PK: Moon_key) has a Planet_key.
    I mean
    CREATE TABLE Planet(Planet_key Number NOT NULL PRIMARY KEY);
    CREATE TABLE Moon(Moon_key Number NOT NULL PRIMARY KEY , Planet_key Number NOT NULL REFERENCES Planet);


    If there are more than one Planet, how did you do?
    For example:
    There are Venus, Earth, Mars, so on... in Solar system.
    Venus has no Moon.
    Eath has a Moon(Luna).
    Mars has two Moons(Deimos and Phobos).

    Who have all Moons?
    Sun has transitively all Moons.
    But, Sun is not a Planet.
    Last edited by tonkuma; 02-15-12 at 20:03. Reason: Add Venus. Correct some spelling.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the important aspect of this relationship is its one-to-many cardinality, yes?

    so each moon should reference its planet, as tonkuma suggested

    if a planet has no moon, then there is no moon row that references that planet

    how can a planet have all the moons in the moon table? that doesn't make any sense at all!!!

    so the problem here is -- (a) i don't understand your scenario, because each moon can belong to only one planet, or (b) this whole scenario isn't really about planets and moons

    what's going on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2012
    Posts
    8
    I just chose arbitrary objects as to make it easier to describe, though it doesn't seem to be rubbing off as so. So, I'll dive a bit deeper.

    You have a Planet table, this table has a Planet_Key.
    You have a Moon table, this table has a Moon_Key
    You have a Planet_Moon table, this table has a Planet_Key and a Moon_Key to relate the two.

    There is a One-to-Many relation for a Planet and a Moon. A Planet can have multiple Moons, which would be represented by a record in the Planet_Moon table.

    However, what if there were 1,000 Moons that a Planet could have. This would mean 1,000 records in the Planet_Moon table. One for each relation.

    My question is, could we simply not add ONE record into Planet_Moon where the Moon_Key was NULL, and have that signify that the Planet had every Moon. I know it's possible, I'm more curious of reasons not to, and the downfalls of doing it this way.

    You could then do a NULL check on that column, and if it was, you'd know (the developer) that every Moon was related to that Planet. It'd be the same as adding a BIT flag of "All_Moons" and a single record. A way to signify that all Moons are related to that Planet, without having one record per moon.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the planet_moon table is designed according to database principles, both the planet_id and moon_id would together form the composite primary key

    that way, you wouldn't be able to relate the same moon to a given planet more than once

    but a main feature of primary keys is that none of the columns that make it up may be null

    i'm sorry, that whole "NULL means all" idea just doesn't fly with me

    of course, since we aren't really talking about planets and moons, you're free to do whatever you feel like
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2012
    Posts
    8
    That was kind of what I was searching for, as I don't agree either, I am just unable to say "I don't agree with this because ________." Intuitively it doesn't make much sense, but from what I can tell, it would work just fine.

    I'm just curious if there's potential for any data to be wrongly derived, pitfalls, or any eye glaring reason to not pursue that path and use a bit field or other representation.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query to find all the "planets" that a "moon" belongs to would be inefficient, requiring a table scan, thus completely destroying any potential saving of not needing to store all the "moons" that a "planet" has


    i am not going to talk about planets and moons any more

    next time, choose a better analogy

    a moon belongs to only one planet, and a planet cannot possibly have all moons
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Erwina View Post
    Are there any potential pitfalls of using NULL to represent "ALL" records?
    Yes, this is an extremely bad idea.
    Quote Originally Posted by Erwina View Post
    Would one be better off having a bit field in the Planet table stating that it should have all moons or a different solution than using NULL?
    I can only think of a few solutions that would be worse than using NULL.

    The optimum solution would be to create a linking row for every moon from the desired planet to each target moon.

    Keep in mind that while Science Fiction does postulate binary planets with moons parked in one or more Lagrangian points, there is no evidence to support the existance of such a configuration of bodies anywhere in our universe.

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

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Query - in a one-to-many relationship, why would you have an intersection table? The table on the 'many' side of the relationship would simply have a column to hold the key from the 'one' side.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by weejas View Post
    Query - in a one-to-many relationship, why would you have an intersection table?
    Answer - it wasn't a one-to-many relationship, because it wasn't about planets and moons
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I didn't mention moons or planets, However:
    There is a One-to-Many relation for a Planet and a Moon. A Planet can have multiple Moons, which would be represented by a record in the Planet_Moon table.
    The OP did!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    granted

    but it turns out he meant many-to-many

    it if was truly one-to-many, then a moon could only belong to one planet

    he was specifically asking about how to represent the situation(s) when a planet has all moons

    if a planet has all moons
    then no other planet can have any
    so it just gots to be
    many-to-many

    q.e.d.

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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