The model outlives the interface
Interfaces are redesigned frequently. The underlying data model tends to persist for years. Getting it wrong creates problems that surface long after launch.
Normalize to avoid contradictions
Storing the same fact in several places invites inconsistency. A normalized model keeps each fact in one place so it cannot disagree with itself.
Choose keys deliberately
- Every record should be uniquely identifiable
- Avoid using changeable values as identifiers
- Understand the relationships between entities before you build
Protect integrity at the source
Constraints and validation in the database prevent bad data getting in, which is far easier than cleaning it up later.
Design for the questions you will ask
Reporting and searching need to be considered while designing, because they determine what should be indexed and how data is structured.