Data Modelling Best Practise

Data model design considerations

When it comes to designing data models, there are four considerations that you should keep in mind while you're developing in order to help you maximize the effectiveness of your data warehouse:

• Grain

• Naming

• Permissioning and governance

Grain

Grain is one of the most important factor of consideration for Data Modelling. In simple terms, if you pick a single row from fact table then what does it represent would be the grain of the fact table.

So for fact table:

1. Determination the grain .

2. Ensure that all of the columns in the relation apply to the appropriate grain

Naming

Naming object in datawarehouse could be the most easy yet complicated affair.

Relation naming

• Use schema name to be umbrella of all tables. Eg: Department, Team etc.

• Always use snake_case.

• Use singular name as much as possible and pluralized name as exception when encountering entities like users, order_items etc.

Column naming

• IDs should get an _id suffix

• Contraints like primary key get an _pk suffix, Unique key - _uk likewise

• Dates or Timestamps should get an _date suffix and _tm suffix

• Booleans should prefix with is_ or has_

Permissioning and governance

• Permission should be centrally controlled by DBAs

• Governance needs to be enforced to make it compliant with GDPR, HIPAA or any regional or vertical based compliance