6 Oct 2017

What is a Good Data Model

This is an excerpt from something I am working on about physical data modelling.

A good data modelled table should

  1. Be able to retrieve data quickly 
  2. Be able to store data quickly
  3. Be clear and easy to work with

A good data modelled table should not

  1. Store unneeded data
  2. Need to change its rows very often **
  3. Need too many JOINs to get you the data that you need

Purposes of a Data Model

A good data model should serve a specific and narrow set of purposes.
The more purposes the table serves the:
  1. More indexes it would need. 
  2. More cumbersome it will be to store and keep in memory.
  3. More overhead it would be to write to.
  4. More likely it be a single-point-of-failure
  5. More likely it would have locks and deadlocks
  6. More likely it would be to add unneeded data
  7. More difficult it would be to make changes to your application if you needed to make changes to the table.
If you notice a pattern here, you may notice that reusability to a high degree, may hinder the performance of a database. There needs to be a balance between reusability and single-responsibility of the data models to be effective. 

** A table that has data that needs to change often and is transient, may be better suited in a cache. If it needs to be saved and transactional, then a smaller table that records the state of certain keys or values with a combination of a log to store how it got that way if it is needed.

No comments:

Post a Comment