Lookup Tables Best Practices: DB Tables… or Enumer

问题:

If we have to store the available positions at a company (i.e. Manager, Team Lead, ... etc). What are the best practices for storing it? I have two opinions with comments... "sure, welcoming yours"

1. Storing it as DB table with columns ID and Name, and deal with it using queries and joins.
2. Storing it as Enum and forget about the DB table.

In my opinion, I will choose the first solution if I have changing items. So that I won't hard code these options as Enum.
I may choose the Enum solution, if I have no doubt that data won't change (for example, Gender: Male, Female).

NOTE: I code in English, and the UI Culture may be Arabic. If I will work with the Enum Solution, I will hard code the culture-based strings in the presentation layer, is it okay from the best practices perspective!!!!

I would like to know your opinions and if my thoughts correspond to what is most recommended "Best Practices"??

回答1:

Generally you should only use enumeration where there is a clear set of items that will not change, e.g. primary colours, or continent names. Otherwise lookup tables with appropriately implemented foreign keys are pretty much always the best option.

There is a possible variation on the lookup table option where you potentially have a large number of lookup tables for simple id/value relationships. A domain/lookup table pair can dramatically reduce this the number of tables required, albeit with some additional coding complexity. In this case you'd have a domain table

DomainID int identity
Domain   varchar(255)

and a key/value table

DomainID int
ID       int identity
Value    varchar(255)

Hence a row is added to the Domain table corresponding to each lookup table that you would otherwise use, and all (key-domain)/value pairs added to the value table. Apart from simplifying the database structure this approach also has the advantage that 'lookup tables' can be created in the application code dynamically, which in some applications can be extremely useful.

回答2:

Option 1: Storing it as DB table with columns ID and Name, and deal with it using queries and joins is the minimum that you should do.

From "Five Simple Database Design Errors You Should Avoid":

While application-enforced integrity is sometimes favored by developers, it remains true that the DBMS must still be the centralized enforcer of all integrity.

The recommended best practice is to implement your database as if it knows nothing about the user interface. That is, the database should enforce all rules pertaining to the data; in this case, the database should be enforcing what values are appropriate. To enumerate what values are appropriate, a lookup table for each type of lookup value is usually the way to go. Many times, applications come and go, yet the database remains and is reused.

If you want to enforce enumerations in the application, you can certainly do so, but whatever you do, make sure the database does its job as a database and maintains the integrity of the data. If it's troublesome, go through the trouble; you are laying the groundwork. In "Database Design and the Leaning Tower of Pisa," the writer emphasizes why laying the groundwork properly in a database is so very important.

I hope this helps.

回答3:

I tend to go for the database option as this enables easy querying with meaningful data (i.e. names rather than ID's).

If I am fairly confident that the values will not change then I will enumerate them in the application as wellas it makes development much easier when you do not have to remember the ID of an item and also makes the code much more readable.

This approach allows me to choose whether to include the lookup table in queries or not. For example I would include it in a report query where I want to display the lookup value but may not include it when loading data in my application if I can infer it from the enumeration instead.

Obviously if the values are subject to change or modification enumeration may not be possible.

Only you can judge the impact of UI culture, I am 100% certain of the culture of my users so do not have to worry about it too much :).

回答4:

I always go for the database option as that has several advantages, one of the main ones being you can change the names/descriptions of items in the look-up lists without having to change any code. Also agree with having a single table as opposed to lots of little tables, that way you code one single routine to retrieve the values from the database which cuts down on maintenance costs. Having a single routine means you can invest more effort in making it perform well.

Further to Cruachan's reply above, you can get away with one table if you have a parent-child relationship where the rows with no parent describe the domain. All the records belonging to a domain have the domain row as their parent.

ID           int autonumber -- integer primary key for performance
DomainID     int            -- null for domains
Name         varchar(100)   -- Name of the item
Description  varchar(255)

So for example a list of currencies could contain:

 ID    DomainID   Name              Description

 100   NULL       ISO Currencies    List of ISO Currency codes
 101   100        BHD               Bahrain Dinar
 102   100        GBP               British Pound
 103   100        EUR               Euro  
 104   100        USD               US Dollar

回答5:

Lookups, Lookups, Lookups (insert monkey-dancing video here)

My personal "best practice" is to have everything in the database. Positions in a company is definitely a lookup table.

The same goes for translations, which may be a bit trickier, but generally a view definition joining the table with a translation table is a good start.

Besides, if the Position is only an int value, and your app is single-language, you can add the position titles directly to the database.

回答6:

I tend to almost always put this kind of thing in a table. If it's needed a lot I'll cache it. If I need to programmatically refer to some of them I'll then create an enum which has its values set to the key value of the lookup item.

For example, a surrogate primary key (usually so my data access layer can seemlessly add/update it), a field for the value of the item, plus a "candidate" (candidate being in quotes since it's not required, so it's either unique or null). That way I can use the enum in quotes to refer to specific/important items in the table, but still have the flexibility of letting end users add new items. The specifics of the data model used really depend on your preference (some people may be screaming at me right now for not just using the "candidate" field as the true primary key).

回答7:

Is the only thing you are planning on putting into a db this list of employers?

If yes, drop it in a file, and be done with it.

DBs are great, but if you need a simple key-value, they are overkill. They give you a lot of stuff. But they also do a lot of stuff behind the scenes, they are one more thing you need to support... if you can get away with a single file with 20 tab-delimited lines, go with simplicity.

If you have a lot of things that require these kinds of lookups, or if you think you might need to update them while a client is trying to read them, or if you might want to cross-reference things later on -- then go for the DB.

回答8:

I generally prefer both. I use table in the dal layer as they can be used for tools like tableau for reporting, SQL queries and other purpose.

I use enums in the front end and the business layer because its easy to develop with them and you dont have to get the list from the dal neither you have to remember the values from the table.

C# automatically convert the enums to corresponding ID for you if the list is same in the table and the enum.

In MVC you can use the dropdown with the enums as @Html.EnumDropdownListFor() in the View so you dont have to the hit the database itself for dropdowns.

回答9:

If you can keep them synchronized I don't think there is much of a reason not to have both. When I was developing some state-machine functionality with a DB store it made things a lot easier to have an enumeration of the available object states within my IDE.

I wrote myself a little EnumGenerator Visual Studio Addin a while back that creates an Enumeration code snippet in C# from a the ID and DESC columns of a DB Tablet you select from right within the IDE. I haven't released it because it was the first time I'd worked with VS Addins and it was pretty junky but I bet somebody with addin/code generation experience could take the idea and run with it.