2

I am trying to design the DB schema of my new project and I need some suggestions. I decided to create a lookup table in order to save the enumeration values. So I have a table Status with these columns idstatus, statusName, statusCode. My question is, do you think that is a good practice to follow this way? Imagine a scenario that you want to update a status of an order. Then your front-end app should send the statusCode and then need to get the idstatus of this code and finally update the Order table.

What do you think? Is there any better approach?

  • 1
    Do you really need the separate statusCode field? I've just used the unique identifier (in this case, the idstatus field) as the enumeration value when I've needed a lookup table. However, I'm interested to see what others do. – Matt May 23 '18 at 13:28
  • @Matt the reason that you need the statusCode is to have a standard way of communication. For example in for visualization purposes, you can have smth like "Completed Order" which is translated to a specific statusCode. In the future you may have "Order is Done", so in this case there is no need to change smth coz the communication based on the statusCode – pik4 May 23 '18 at 13:57
  • I was thinking that you were using statusCode as a separate field to store the enumeration value (integer). Thanks for the clarification. If I understand correctly now, you are instead using statusName to store the text that would be displayed to the user in a GUI and statusCode to represent a human-readable canonical name for the status. – Matt May 23 '18 at 14:51
  • @Matt exactly! :) – pik4 May 23 '18 at 14:55
  • 2
    statusCode and idstatus seem redundant to me (unique keys of the status table). I assume the order table has a FK reference to the status table? Can you use statusCode for the FK? – Egret Jun 7 '18 at 5:40
2

Lookup tables are very useful, and a good practice, because the database remains the source of data, and you don't spread it between database and application.

The database should be the canonical source of data. Saving enumeration values in lookup tables gives you referential integrity due to the foreign key relationships between the lookup tables and the other tables that use these enumerations. The database becomes the single source of truth as to which items are in those enumerations.

Programming languages like C# allow you to save a string to a column in the database, and then map it easily to an enum, but then the C# code becomes the single source of truth for that data, but not the rest. The programming code should be the single source of truth for behavior - algorithms. The database should be about data. Using lookup tables allows this separation fully.

Imagine a case where you have two statuses for orders:

  1. Pending
  2. Fullfilled

So you create an enum in a programming language to represent this:

public enum OrderStatus
{
    Pending = 0,
    Fullfilled = 1
}

Then you add another status: Cancelled = 2 but you haven't saved a "cancelled" order to the database yet. While cruising around the database tables, you only see "Pending" and "Fullfilled" statuses for orders. Without the programming code available you cannot tell what all the possible values are.

Related to this, I recommend using a "code" column for the primary key - a string, essentially. Something short. When looking at tables in the database you don't have to keep track of numbers and their corresponding representations. You don't need to remember that a status Id of 1 is Pending and 2 is Fullfilled, and 3 is Cancelled. If your status table has these three statuses, and their primary keys are literally "Pending", "Fullfilled" and "Cancelled" then the other tables that utilize those statuses will have foreign keys that make sense to a human as well, without the need to remember the number to status name translation.

1

Your scenario is very typical and recurrent. Adopting an idstatus has some beneficial implications at DB design and some complexities to manage in designing the Domain Layer.

TL;DR: if your enumerations depends on domain logic (it's not non-functional requirement) go safe for idstatus and being prepared to manage a little extra effort on DomainLayer design.

DB Design

  • Performance: An FK on a more compact type (typically INT) is better (really naïve observation) and could speed up filtering on master table in case your statusCodes tend to be too wider or the master table has many of such enumerations
  • Evolvability: Can you be really sure that the statusCodes itself (either than statusName) will not change in the future?… Codes should convey domain model insights and the vocabulary of your model could evolve: having an easy way to adapt to it wouldn't be so bad

Domain Layer Design

  • Status Codes: are semantically Object Values and not Entities: don't expose their idstatus to Application Layer never therefore…
    • Entity Models: your entities should avoid to expose any idstatus in favor of statusCodes.
    • Repositories: Model the interface of the Repositories exposing the statusCodes. Managing a query with an extra lookup is not so complex. Take in consideration that could also avoid an extra JOIN if you cache the lookup table at this level… just in case you're exasperated with performance gain 🙂 (I don't suggest this practice because lookup tables are very likely candidates to be cached in memory by all serious RDBMS engines)
0

The design of database should be based on how you going to use it. Of course if you really need to start from database design (as you may as well start from business cases). The same rule applies to lookup tables. If you:

  • have group of properties which are hardly every changed for entity
  • have property common for all rows in your table (e.g status in most cases is common for orders)
  • want to retrieve common property without extracting it from all rows (e.g. list all possible statuses by name without checking them in all rows)
  • ...
  • ...

you might think about additional table, lookup table. But you don't have to. Identify major cases - check which solution is better. Don't guess!

If you decide to do it in most cases you will use some unique id/s in one entity and same ids as index/es in common part (your lookup table). So you will ask on the frontend for list of all statuses and you will receive objects with id, name and code (if it is not unique and you need to display it). User chooses by name on UI and you pass corresponding status id to the backend (+ of course order id) to update specific order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.