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!

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?

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.

pootle flump ur codings are working excelent.

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

but Shirley that should be a SkillLevel colu....
aw....you're no fun!

pootle flump ur codings are working excelent.

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.

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

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 )

pootle flump ur codings are working excelent.

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.

OP - we are probably about to get into an argument that is very much a question of dogma. Hang on

pootle flump ur codings are working excelent.

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

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.

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?

not even close, but thanks for trying

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

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

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