Coding For a Living

rss

A place to share and comment about coding for a living.


DB Schema Design–Rethinking ‘Type’ Tables Pt 1 Mike McIntyre

If you are a typical business application developer you probably work with a database which is cluttered with ‘type’ tables e.g. InvoiceType, CustomerType, PaymentType, CreditCartType, OrderType, ItemType, ProductType – which all have a schemas similar to these:

     image

There are the same number of columns in each type table above. 

The data type of the columns are the same in all the tables. 

Some column names are the same in all the tables. 

Columns names that are not the same in all tables do follow the same naming rule in each table e.g. ItemTypeId, OrderTypeId, ListTypeId which all end with ‘Id’.

Given how similar these tables are, why not use 2-3 tables for all standard ‘type’ data, maybe something like this?

 

     image

More to follow in part 2 of ‘DB Schema Design–Rethinking ‘Type’ Tables’