1. Consider the nature of the application.
If the application is more into CRUD operations it higly likely needs a transcational database. Then normalization approach is better. But if the application is for analysis, reporting and forecasting where less number of inserts and updates, it needs an analytical database which needs to fetch and analyse data as fast as possible. So de-normalization approach is better for an analytical database for fast data access.
2. Break data into logical pieces.
3. Don’t get overdose to break data
Don’t break data blindly, just beacuse of the theories of normalization. Always ask the question “Is it logical to decompose?”
4. Avoid duplicate non-uniform data.
Solution to avoid non-uniform data is to create a master table for them.
5. Avoid ‘repeating groups’or data separated by the separators
Solution to avoid repeating groups is many-to-many relationship. This depends on the context.
6. Avoid partial dependancies
As mentioned in the second normal form, all columns should fully depend on primary key. If the primary key is a composite key, then all other columns should depend on the columns used for composite key.
7. Choose derived columns preciously.
In transactional databases, better to avoid derived columns, as it needs multiple columns to update for a single operation and in analytical databases derived columns might be needed to gain performance.
8. If performance is really important then don’t be hard on avoiding redundancy. De-normalization can be helpful to gain performance as mentioned above.
9. Handle multi-dimensional data carefully specially in analytical databases.
Solution is to use a dimension and fact design which has a central fact table and dimension table has a foriegn key to central fact table.
10. Centralize key, value table design with type column.
11. For unlimited hierarchical data, use self-referencing with Primary Key and Foriegn Key.
References:
http://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo
Randoms Came Across 