These are not meant to be comprehensive (and don’t get into advanced normalization or intentional denormalization) but will result in a decent table design without too much thought in most situations. A case can be made for natural primary keys but going the surrogate route is easier and works well too.
- Always have a primary key. Typically a surrogate key using an identity column and not necessarily clustered.
- Almost always have at least one unique constraint if you used a surrogate primary key
Always include create date and created by columns. If rows can be updated, include updated date, and updated by columns as well.
Consider effective and expiration date columns in place of “active” flags.
Consider a timestamp (not datetime, but timestamp) field if multiple simultaneous edits are possible
Avoid nullable columns – they result in ISNULL/COALESCES — and use defaults instead – EG ‘’ (empty string) for varchar fields, 0 or -1 for ints, etc. A large number of necessarily nullable columns suggest the table may need to be split.
Avoid nullable foreign keys — they result in outer joins
Consider indexes on any foreign keys
Do not store compound values in one column.
If you know in advance how your table will be queried and none of the indexes created from rules 1, 2, or 7 meet requirements, consider creating indexes on those columns if they are selective (have a wide distribution of values).
You can break any rule except number 1 with good reason after very careful consideration
- consider making this/one of these the clustered index as they may be the most common way rows are selected
- these will frequently save you from bugs/errors that would result in duplicate data
- name and abbreviation in a state table are a good example — only one state should have the abbreviation TX and only one should have the name Texas