Feel free to add to it - rules from your own experience so others can benefit.
- Every table has to follow 1st, 2nd, and 3rd Normal Form --- First Normal Form (1NF): A table is 1NF if every cell contains a single value, not a list of values. This properties is known as atomic. 1NF also prohibits repeating group of columns such as item1, item2,.., itemN. Instead, you should create another table using one-to-many relationship.
- Every table has to have a numeric Primary Key
- Number of fields in a table should not exceed a ‘reasonable’ number (up to 20? 25?)
- Fields in a table have to have NOT NULL setting with default values (unless there is a very good reason not to. Example: comments field)
- A table has to relate to any other table only by one field (Foreign Key to Primary Key)
- Any piece of data has to be in one place only. No piece of data can be copied to two or more places / tables.
- There should be a standard established for naming any object: table, field (specific for PK, FK), index, trigger, sequence, check, etc.
- Names of objects should be as short as possible, but long enough to be readable.
- No calculated, concatenated (etc.) fields in a table.
- If a data element contains two or more components, they should be divided as much as possible. The data element can be rebuilt by concatenating the individual components.
- Identify the columns having an index – those having foreign keys, those used frequently in search conditions or joins, those with a large number of distinct values and those updated infrequently.
- Each table represents one object (entity) in the real-world systems it represents.
- Anything else?
--- Second Normal Form (2NF): A table is 2NF, if it is 1NF and every non-key column is fully dependent on the primary key.
--- Third Normal Form (3NF): A table is 3NF, if it is 2NF and the non-key columns are independent of each others. In other words, the non-key columns are dependent on primary key, only on the primary key and nothing else.
There is a great need for a sarcasm font.