With Microsoft joining the club, we now have Oracle, IBM (DB2), Teradata and Microsoft supporting some portion or all of the bitemporal design.
Good to see it become more mainstream - I've been adding the field to support this in data warehouses for years, but to use it properly is clunky so more support for this has got to be a good thing.
Showing posts with label DATABASE DESIGN. Show all posts
Showing posts with label DATABASE DESIGN. Show all posts
Wednesday, 17 August 2016
Sunday, 22 November 2015
Mind your Database Ps and Qs via @drsql
I heartfelt plea to all those that create or share code/databases/anything else - documentation, structure and comments are vital if you want anyone else to be able to understand what you have done.
Great blog by Louis Davidson. I would go a bit further - if you have an ego and want everyone to think the best of you and that what you have done is great/brilliant/amazing then structure it well, leave comments in the code, provide clear documentation and then it will be easy and obvious to see just how clever you really are.
Great blog by Louis Davidson. I would go a bit further - if you have an ego and want everyone to think the best of you and that what you have done is great/brilliant/amazing then structure it well, leave comments in the code, provide clear documentation and then it will be easy and obvious to see just how clever you really are.
Wednesday, 17 June 2015
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.
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.
Subscribe to:
Posts (Atom)