Source: Simple-Talk
- Common Lookup Tables
Always use separate tables for each logical entity, identifying the appropriate columns with correct types, constraints and references. It is better to write simple routines and procedures to access and manipulate the data in the tables without aiming for “dynamic code”.Common lookup tables have no place in sensible database design, whether used as a short-term makeshift fix or as a long-term viable solution.
- Check Constraint Conundrum
Three specific criteria to choose between a check constraint or a separate table with foreign key constraints.- If the list of values changes over a period of time, you must use a separate table with a foreign key constraint rather than a check constraint.
- If the list of values is larger than 15 or 20, you should consider a separate table.
- If the list of values is shared or reusable, at least used three or more times in the same database, then you have a very strong case to use a separate table.
- Entity-Attribute-Value Table(Entity-Attribute-Value)
A nickname for a table that has three columns, one for the type of entity it is supposed to represent, another for a parameter or attribute or property of that entity and a third one for the actual value of that property. - Application Encroachments Into Database Design
Enforcing Integrity via applications: Databases are more than mere data repositories; they are the source of rules associated with that data. Declare integrity constraints in the database where possible, for every rule that should be enforced. Use stored procedures and triggers only where declarative integrity enforcement via keys and constraints isn’t possible. Only application-specific rules need to be implemented via the application.
Application Tail wagging the Database Dog: Applications come and go, but databases usually stand for a long time. - Misusing Data Values As Data Elements
No two tables in a database should have overlapped meanings