Simple Database Design Recipe

When you need to design a relational database, do not try to do lot of things at once!

You might have a cleaner result if you follow these steps:

  1. Collect all papers, reports, receipts, screens that are currently being used in your organization
  2. Make a list of all atomic values being used in these papers, reports, receipts, screens, etc…. : database dictionary
  3. Design a set of tables that will represent exactly what it is represented in these papers, reports, receipts, screens, etc…. Try to not improve it — just guarantee that the semantics are properly maintained
  4. Now, you should have a very complex design with lots of redundancy. Don't try to be creative for now: just try to be a good book-keeper
  5. Simplify the design produced in (3-): remove columns and tables that are not important. Now, you should have a “napkin” design, basically. Your creativity here is measured in how much you eliminate and simplify, but don't add new attributes
  6. Change the design produced in (4-) to add more features that you need now but that can not be represented in that design: extend the model. You will be adding new relations (tables) but no new attributes (columns). Now you are adding attributes and tables, but only if there is a palpable reason to do that. You should be able to write down that reason, before you add any new attribute
  7. Normalize the design in (5-). Just follow the procedure: you are cooking with a recipe
  8. Add the magic columns and other tricks that are required by your implementation and tools

There are two mental processes that you must continuously apply:

  • analysis (divide and separate) and
  • synthesis (consolidation and simplification)

Don't try to do both at the same time

When you try to capture knowledge you are basically analysing (dividing the whole in parts), but later you have to create something new (synthesise it)

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License