Representation of Lookup table

Question

How can I represent my lookup tables in technical reports?

In other words, the ER model is used to represent a database, but what about lookup tables?

Answer 1

To recover a conceptual model (entity sets, attributes and relationships) from a physical model (tables and columns), we first have to understand the logical model. This means understanding the domains and functional dependencies which are represented by the lookup table.

Lookup table is a common term which can mean different things. I generally understand it as a table which represents a domain with a surrogate key, and associates it with a name and/or a few other attributes. In the ER model, these would be simple entity relations, and leaves / terminal nodes in the graph of entity sets.

If a lookup table records facts about only one type of thing (represented by the key of the lookup table), then you can represent that type as an entity set (rectangle) with an attribute (oval) for each dependent column, and draw relationships (diamonds) to connect it to other entity sets as required. Look for foreign key columns / constraints in other tables to find these relationships.

For example, consider the following physical model:

CarMake and CarModel are examples of lookup tables. This isn't a very good model, since in the real world CarModelId determines CarMakeId, while the model treats them as independent elements in CarSales. However, since the point of the example is to focus on lookup tables, I'll use it as is.

In this case, CarMake and CarModel describe a single entity set each. Their functional dependencies are CarMakeId -> CarMakeName and CarModelId -> CarModelName. In CarSales, we've got CarSaleId -> RegNumber, Price, SoldOn (attributes) and CarSaleId -> CarMakeId, CarModelId (relationships).

In this case our ER model is similar to the physical model:

However, in some cases, you may find multiple types of things combined into one lookup table due to the similar physical structure. This doesn't affect the logical or conceptual models, but makes it more complicated to recover since we have to understand how the table is used to unpack it.