10 Rules for a Better SQL Schema

A list of what the author perceives as the 10 rules that should be followed for a better SQL database schema.

I have some observations -

1.  They suggest using lowercase - you could just as easily use uppercase - just decide which one o use and stick to it.

2. and 3. I would add to it that you need to be clear and standardise on whether you are going to use UK or US English.

4. Be careful with the indexes it is suggesting instead of multi-field PKs - if you have too many of the wrong type you will create a burden on loading data into the tables as you will have to drop indexes, load the data, and then recreate the indexes.

6.and 7. I agree - they should be stored as datatimes and in a single timezone - there is usually functionality to handle conversion to local datetime when running reports.  You can also always add calculated fields to denormalised data used for quicker reporting to pre-calculate local values.

8.  Should be the rule for any database - if you don't have one source of the truth you are never going to be able to use any of your data with confidence.

I would like to add a new rule which I will call 11.

11. Try to avoid using NULL values in any field - always use a default as it will improve the quality of any reporting on that data.