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
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.
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 )
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.
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.
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?