Lookup Tables are used to store contents that is used to categorize groups of records in other Master/Parent and Child Tables.
In the example below, we see the data model of a Ninox database application used by a car dealership to keep track of sales inventory on their lot, the salespeople who sell that inventory and the customers who purchase that inventory. These entities are represented in the New Car Inventory, Customer and Sales Team stacks. We also see that each of these stacks – or Families – have at least one child. And in each case, the child of each parent is a transactional tables where the parent is the Master Data table.
On the outer periphery however, we see yet a third set of tables which are neither Master/Parent tables or Transactional/Child tables. These tables – Interior Upgrades, Wheel Upgrades, Stereo Upgrades and Vehicle Colors – store content that the New Car Inventory Master/Parent table looks up. It is these Lookup Tables that enable the users of this database application to routinely add and delete vehicle option packs and color choices as they are offered and then discontinued by the vehicle manufacturer.
Lookup Tables play a critical role within the scope of ERP (Enterprise Resource Planning) and CRM (Customer Relationship Management) solutions that are used by entities large and small across the globe.
It is worth noting that Ninox does provide both Choice and Multiple Choice fields that can be included in a tables data model, the options contained in these elements are usually more “fixed” in nature. Consider a landscape service company that tracks customer jobs by season. That entity would use a Ninox database with a choice field offering selection options of “Summer”, “Fall”, “Winter” and “Spring”. These are not options that are likely to be discontinued nor are additional seasons likely to be released in future years. A choice field is perfect for this type of scenario. Lookup Tables, on the other hand are very effective when the choices/options offered by the table are likely to change over time.