Those who read my blogs regularly could complain that I am repeating myself this time. Of course, I have written comments about design couple of times. Nevertheless, I think there is never enough emphasizing how important a good database design is.
Relational design, suitable for OLTP applications, is based on science, namely on set theory. This is one of the rare spots where application development can rely on mathematics. If mathematical laws do not support something, then the only way you can check whether it is going to work is by testing. BTW, how do you check whether your application works these days? OOP, for example, is not a science; it is a set of best practices. RDBMS’ are written in non-scientific programming languages by themselves. Therefore, a good relational design does not guarantee everything is going to work. However, I can bet that a bed design leads to problems, specifically data integrity problems. Good relational design is about data integrity. The design by itself enforces constraints, and constraints enforce data integrity.
Of course, relational design is not the only design possible. For data warehouses, dimensional design is preferred. For always changing problems where you do not care about data integrity too much, XML design might be more appropriate. For special problems, like controlling production lines, a specialized system might be a solution. Still, you should never forget: if you have to deal with data integrity problems, you should go for the relational design. Do not accept solutions that advertise themselves as “the future” and then introduce some kind of object or XML “database”. Alternatively, if you accept such a solution, remember my foretelling about your data integrity.
- Python for SQL Server Specialists Part 4: Python and SQL Server - April 24, 2018
- Python for SQL Server Specialists Part 3: Graphs and Machine Learning - April 11, 2018
- Python for SQL Server Specialists Part 2: Working with Data - March 22, 2018