Rules for Database design and implementation
Here are my rules for database design and implementation. Again, these are not absolute rules but are guidelines that should be followed in most circumstances.
- Can't have multiple values in a column in one record. For example, if Fred has three cars, then the record for Fred should not have a Car column with three values in it.
- Want to have as few nulls in columns as necessary. Many times you have a choice of which table to embed a foreign key in. You should choose the table to embed the key in that will result in the fewer number of nulls.
- Want as few tables as necessary. You can represent any relationship by making a third table for that relationship; however, this makes the database more complicated than is necessary. For example, if there is a 1-M relationship between Hospital Rooms and Patient, you could make a separate table for the Assigned-To relationship that has the key of each table embedded in it. The simpler, and preferred, method is to embed the key of the Hospital Room (the 1) in the table of the Patient (the many).
- Key should be no longer than necessary. If you are trying to decide between defining a key on two columns (e.g., Invoice Number and Date) and a key on one column (e.g., Invoice Number), both of which can uniquely identify a particular row in a table, choose the shorter of the two keys. When declaring a key, you are asserting that this certain set of information is necessary and sufficient for uniquely identifying a row; no more information is necessary and no less information is necessary.
- Only create a new relation between entities if that relation cannot be derived from another relation. In a relational database you can represent a relationship either by creating a table for that relationship or by embedding the key of one table in the key of the other. As stated in rule #3, the second alternative is preferable to the first.
Return to the ER to SQL page.
page revision: 1, last edited: 20 Aug 2008 16:01