A few Guidelines for data modeling

Never lie, trust in reality!

Benefit: There will never be a need to redesign your database if the data model grows.

Why? Your data model describes a part of reality. If you add some more reality to your data model, everything will fit just fine. Always, I promise.

You never get stuck - just implement, what you see in reality.

Don't think too simple, don't think too complex (easier to tell than do, I know).

 

Always use third normal form

Don't "optimize" a data model without need, you won't need.

If you know nothing about  third normal form and relational database theory - learn! It's essential.

 

Indexes

Build a unique index for each primary key (PostgreSQL does that for you).

Build an index for each foreign key and performance should (almost) never be an issue.

 

Naming is essential

Tables (i.e. relations) are always plural, because they almost always contain more than row (i.e. entity).

Field names in foreign keys have to have the same names in the primary-key-table and the foreign-key-table, because essentially it's the same field (i.e. attribute).

 

Graphical SQL development

SQL is easy to learn.

Don't use SQL generators. Resulting code is often ugly and you can't use some features your database provides, you don't even know they exist.

Above a certain complexity, graphical tools won't work any more.

Maintain a SQL create script and a script for stored procedures.

 

Object relational mapping

Don't hide the internals of your database, use them for your benefit.

You can't be sure, what this thing is doing.

So don't use ORM tools.

 

Never ever trust a foreign system

Never trust another IT-System, you have interfaces to/fro.

Example: Often the promised unique id's are no more unique after rollout (afterwards they tell you: But they are unique - most of the time ...).