Lookup Values - Domain Tables
Every Database Project I have ever worked on has needed a lookup Table. There will always be lists of values you need to select from.
In our Test Project table QiSQL_Children we decide we need to add a column 'Gender'. So, for every record we would need to enter 'Male' or 'Female' and possibly in the future 'Transgender' or there may be an option for 'Non Specified'. We don't want to have to type these values in every time we add a new record - it's a waste of time, a waste of disk space and we have the possibility of incorrect data being added to our table.
So let's consider adding a table for Gender. A Table just for Gender would be daft! There would only normally be two values in it. Maybe it would be better to create a 'Domains' Table where we could group together different sets of Lookup Data. So how about a Table that stored Lookup Data like this:
NO!! Loads of Repeating Data that breaks all our Normalisation Rules. We will have to break this design down into separate entities (you remember what they are don't you - chunks of data). Let's create a simple Entity Relationship Model for Domains and Values.
So we would have a Foreign Key in the Child Table -' Domain Values' linking it to the Primary Key in its Parent 'Domains'
The solution above would work reasonably well until we decided that we need a more complex Domain Hierarchy with more than one detail level.
Hierarchies are used a lot in Databases. Say for example we wanted a lookup field to store Towns. We may want to know which county or state the town was in , we may also need to know which country the town was in, which continent etc. So we would need a Data Model a bit like this:
Can you spot the problem with this kind of design? How would we know how many levels to build in - our Domain table is supposed to handle ALL our lookup data. It would be also be a real pain maintaining these multiple tables.
I am now going to introduce another new concept which will have its very own page - Self Referencing Tables: