What is the purpose of Reference entities in physical models?

Hi,

I am learning databases and I run into some example physical models that features Reference entities.
Below is an example of a physical model for online Pizza delivery service ̣(Figure 1 ↓↓↓).

As You can see, there are 5 reference entities in figure 1:

  • Ref_Payment_Methods
  • Ref_Delivery_Status
  • Ref_Base_Types
  • Ref_Toppings
  • Ref_Vehicle_Types

I would like to know why they are needed for specifically for this model. For example there is Ref_Delivery_Status entity, however we can instead put delivery_status_description inside Orders entity, thus no Ref_Delivery_Status will be needed (Figure 2).
I don’t understand why author of this model created Ref_Delivery_Status as it is in the first figure.

Depending who you’re talking to, these may also be known as lookup tables. But some may call it reference tables.

To answer your question, it’s got to do with database normalization.

Your question about Figure 2… I guess for simple apps and in this case pizza ordering system, you can do it that way but that will be going against data normalization. The problem with that approach is data duplication. What if you need to change the description? You’ll need to replace multiple records, and there is danger you’ll have inconsistent description for the same exact thing. In a normalized data form, you’ll only need to change description in one table, one time. But then again, this is a pizza delivery application so after the pizza is delivered, who cares? But for other types of application, normalization and data consistency will be very important.