I have a table Subject It has many fields, two of them are code and flag. Earlier those two fields was an idempotention key for rows in this table. But, now I need one more option system.

There are tens of rows in Subject And 4-7 systems.

What is a better way?

1. Create table System for systems and create cross-table of mapping sysytems on subjects (code and flag are still in table Subject)
2. Create one table of mapping without creating table System
3. Just add another column in table Subject
4. Create table System and add to the table Subject foreign key for table System?

So, It's all about database normalization. And the third option is pretty bad. As for me the better way is fourth option. But, I can`t explain to yourself why this option is better than 1 and 2. So, I read rules of database normalization. And as for me, the first option satisfies all rules too.

This is the reason why I am asking this question.

CodePudding user response：

It is not typically a great idea to design a SQL schema to 1st or 2nd normal form; many databases use something at or near 3rd normal form however there many still be some relationships in 3rd normal form where dependencies exist where redundancy still exists. This can be addressed by Boyce–Codd Normal Form (Codd, 1974).

It is also not typical to see 4th normal form and less so 5th normal form and beyond due to challenges with data maintenance in a "living" database.

Let's put this another way.

If you find yourself creating NULLable values constraints on many columns consider a table to contain those in an organized fashion - for example an Address table for addresses with a linking table from say for example a Person to a PersonAddress linking table to that Address table where the PersonAddress linking table might even have an AdddressTypeId column which links to an AddressType table with rows for Address Type Postal and Address Type Street or Address Type Business. For another example consider email addresses where people have personal, family, business and other email address types; even multiple of the same type for different uses; a doctor with a business practice email and 2-3 hospital email addresses where the doctor practices.

Linking tables for those type scenarios are likely better than 3-4 or more email or postal address columns in one table where many after the first are nullable or perhaps redundant.

Review your data; consider if your Subject for example may link to multiple System or placing a SubjectId column may lead to duplicates of that ID for differing system rows. If it is always and forever a 1-1 relationship it may be OK but for a 1-n or n-n it may not be ok to have the id in the other table and a linking table may provide a good mechanism to link them.