Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    2 n00b questions

    Please excuse the elementary nature of these ?s.

    1. I'm establishing a subset of a table, because this subset has some extra attributes that the main table lacks. Is there a problem with having two tables with the same primary key name?

    2. I'm linking two many-to-many tables with a linking table with a compound primary key. Will adding some attribute fields to that linking table create any problems?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rbfree
    1. I'm establishing a subset of a table, because this subset has some extra attributes that the main table lacks. Is there a problem with having two tables with the same primary key name?
    No - this is a one-to-one relationship. The question however might be "why bother?". Why not just have one table and leave the values for these columns NULL where appropriate?

    Quote Originally Posted by rbfree
    2. I'm linking two many-to-many tables with a linking table with a compound primary key. Will adding some attribute fields to that linking table create any problems?
    No problem at all. A subtle change occurs though. Without non-key attributes, the table describes a relationship and nothing else. With non-key attributes, it becomes an entity in its own right.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    With non-key attributes, it becomes an entity in its own right.
    i wouldn't go that far, i would just say that the relationship has attributes

    example: gymnast performs manoeuvre is the relationship (and it's obviously many-to-many)

    add a column for isHighlySkilled and it does not automatically become a "gymnast-performs-manoeuvre" entity, it's still just a many-to-many relationship between gymnast and manoeuvre, except now you know something more about it

    don't confuse the guy with subtle nuances between entities and relationships if he's just trying to learn PKs and FKs

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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    add a column for isHighlySkilled
    but Shirley that should be a SkillLevel colu....
    Quote Originally Posted by r937
    don't confuse the guy with subtle nuances between entities and relationships if he's just trying to learn PKs and FKs

    aw....you're no fun!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2009
    Posts
    104
    Quote Originally Posted by pootle flump
    the question however might be "why bother?". Why not just have one table and leave the values for these columns NULL where appropriate?
    My thinking is that the fewer unpopulated records, the better -- in general. For this case, I could see a couple hundred records, with only 1/3 of them going into the subset table. The subset table will have 5 or 6 fields -- so that's at least 300 null values. Seems like a waste of space to me, but the reason I'm asking is because I'm still getting a feel for the logic. So, I'm not particularly attached to this argument.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that logic is fine

    it's also in line with relational theory, which disdains the use of NULL

    database practitioners, as a whole, tend more towards pragmatism than theory

    NULLs don't take up a whole lotta space, and query simplicity (not having to do joins) is sometimes the deciding factor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    104

    sounds like a quasi-optimization

    Interesting. Sounds like it's a quasi-optimization problem -- between simplicity in queries and economy in memory storage.

    Is there an advantage to either solution from the perspective of adding tables or fields later in the game? (I don't have any amendments in mind, but could imagine the need in the uncertain future.)

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rbfree
    Interesting. Sounds like it's a quasi-optimization problem -- between simplicity in queries and economy in memory storage.

    Is there an advantage to either solution from the perspective of adding tables or fields later in the game? (I don't have any amendments in mind, but could imagine the need in the uncertain future.)
    Actually, it is a (potential) economy in disk storage. Joins however take place in memory.

    Disk space is cheap. Also remember that knocking a few columns storing nulls out of one table has to be offset against the space taken up by the second table (which may be more than the sum of the space taken up by the nulls).

    I would stick with one table. The only reason I would use a one-to-one relationship is to make enforcing constraints easier (and that's just a whole other concept for Rudy to tell me off about )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rbfree
    Is there an advantage to either solution from the perspective of adding tables or fields later in the game?
    I think there is. I find that being in Fifth Normal Form (without nulls) minimises the cost of future database evolutions. "Bundling" entities with different sets of attributes into the same table is really just a form of technical debt in the database.

    Always take it as read that a database design will evolve. A business that is not changing is dying. Your database design should evolve in line with changes in the business being modelled.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    eh? Domain key normal form (6 NF) is the nullphobic form. 5NF can be stuffed full of nulls.

    OP - we are probably about to get into an argument that is very much a question of dogma. Hang on
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your dogma ate my karma, and i won't be taking part in the loverly discussion of theory

    i have yet to see anyone explain a normal form beyond 3NF in plain english

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

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by pootle flump
    eh? Domain key normal form (6 NF) is the nullphobic form. 5NF can be stuffed full of nulls.
    DKNF (Fagin, 1981) and 6NF (Date, Darwen, Lorentzos, 2003) are different and unrelated to each other.

    Normal forms were defined before nulls were even invented. All the normal forms I know of deal only with relations consisting of values within tuples - nulls don't get a look in anywhere.

    In the SQL world we have to work with SQL tables rather than relations. It is pretty arbitrary and suspect to try and retro-fit normalization onto anything that doesn't accurately represent a true relation. If you want to claim that a table with nulls is normalized then what is your definition of a join dependency going to be? Even in SQL "nulls don't join" so it's difficult to see how a nullable column can be subject to anything like the conventional definition of a JD. It's also hard to make sense of Armstrong's axioms where the attributes include nulls (a few people have tried developing a dependency theory for nulls but I don't think there's any widely known or understood interpretation).

    So whatever you think of nulls, they have no place in any normalized table. Common approaches to normalization with nullable tables are either to pretend that nulls will never occur or just to ignore nullable attributes altogether. Neither is very satisfactory in my opnion.

    Quote Originally Posted by r937
    i have yet to see anyone explain a normal form beyond 3NF in plain english
    With good reason surely. English isn't the right language for describing mathematical concepts. If you wrote a short English description of 3NF it would probably be wrong or ambiguous. "Make things as simple as possible, but not simpler"

    5NF isn't hard to describe if you know what a join dependency is: A relation is in 5NF if every join dependency is implied by its superkeys. Got it?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    A relation is in 5NF if every join dependency is implied by its superkeys. Got it?
    not even close, but thanks for trying

    i guess i must be missing a few of the prerequisite concepts, eh

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

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Well don't give up. Fifth is by far the most important Normal Form (by contrast 3NF seems like not much more than a historical accident).

    Hugh Darwen has some examples of Join Dependency and 5NF:
    http://www.dcs.warwick.ac.uk/~hugh/C...8-design-I.pdf
    http://www.dcs.warwick.ac.uk/~hugh/C...-design-II.pdf

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    give up learning 5NF?

    like, the way i had to give up my dreams of ever playing in the nfl?

    neither is likely to happen, and i don't pursue either at all

    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
  •