10

Long ago, I worked (as a client) with a software which use a centralized table for it's codified element. Here, as far as I remember, how the table look like :

Table_Name (PK) Field_Name (PK) Code (PK) Sort_Order Description

So, instead of creating a table every time they need a codified field, they where just adding row in this table with the new Table_Name and Field_Name.

I'm sometime tempted to use this pattern in some database I design, but I have resisted to this as from now, I think there's something wrong with this, but I cannot put the finger on it.

It is just because you land with some of the structure logic within the Data or something else?

Improve this question
0

6 Answers 6

Reset to default
21

Let me help you put your finger on it. Look up the "Inner Platform Effect (anti-pattern)" for the pros (and mostly) cons.

[...] In the database world, developers are sometimes tempted to bypass the RDBMS, for example by storing everything in one big table with two columns labeled key and value. While this allows the developer to break out from the rigid structure imposed by a relational database, it loses out on all the benefits, since all of the work that could be done efficiently by the RDBMS is forced onto the application instead.

Here's some further reading.

Improve this answer
8
  • how Alex Papadimoulis's thedailywtf is related to Jeff Atwood? Commented Mar 16, 2011 at 15:14
  • Oops, sorry. My bad. Fixed the citation. All you programmers look the same to me. =) Commented Mar 16, 2011 at 15:16
  • OK, I got it: While you will save time on the database development by centralizing all description into one table (only one table to create and maintain), you lose on the performance and structure because you have to do database work on another layer on the database (within the query, or elsewhere). This database work would be better done by the RDBMS if a proper structure is done. So it's a lazy programming pattern. Commented Mar 21, 2011 at 13:37
  • I don't know about lazy, but it just isn't a good idea. Commented Mar 21, 2011 at 14:19
  • @johmFX "The road to hell is paved with good intentions" laziness of programmer make them think it's a good idea because they will have less work to do. Commented Mar 21, 2011 at 14:33
5

Databases already do that for you, you're nesting the same logic and losing all the benifits the database would normaly give you.

Improve this answer
4

One problem is that, sooner or later, one of those "mini-tables" inside the big table will need an additional column. Then what do you do? Split it off into a separate table? Now you have most of your lookups in one table, but one or two oddballs in their own tables. Or you add the column in the main table, which leaves you a whole bunch of nulls.

Improve this answer
2

I think one description table is problematic, but I absolutely lump non-homogeneous data in a more-or-less normalized fashion into lookup tables. Like if I have to store various date values with limited key information that is associated with different master tables, I'll put those in a single table with an identifier column to let me split it into handy views.

It's a fine line. One massive table with extra columns and other weirdness is hard to maintain...But so is 500 little hyper-specialized lookup tables. You need to strike a balance. Keep your data pretty normalized, and you can't go too far wrong.

Improve this answer
1
  • +1 For most applications, perfectly normalized data shouldn't be your goal. Like you said, "strike a balance." Commented Mar 15, 2011 at 23:08
2

I have worked with this model. Foreign key validation was a nightmare implemented in triggers. In the end we would have needed triggers anyway as we didn't allow codes to be updated to disabled values. But that was an extra layer of integrity. Basic integrity should not have required a trigger.

Drop the table name column, and you have a nice start on a pattern for a standard code table. Build an interface or a factory and you have some nicely reusable functionality. Allow code tables to extend the pattern as needed.

Let the database ensure the integrity of your stored data. This is what it is designed to do. The single code table model does not allow the database to help you.

If you are dealing with internationalization, you may want to move the descriptive table. This would allow different descriptions in different languages. However, other methods are also available for applications.

Improve this answer
2

I have encountered this model in a several different systems at my current company. Since all of the answers so far take a negative stance, I will give some reasons why this pattern is good. The cons raised by the other posts are certainly worth considering. But there are some pros:

  • You can write a single persistence / business log tier against that centralized "reference data" table. Let's say you use some sort of ORM. Under this case, you only need to write one persistence class - let's call it "ReferenceValue". Under the completely normalized pattern, you'd have to write one persistence class for each reference type you have - such as "PaymentStatus", "RequestUrgency", "OrderType", or whatever.

  • If you have the requirement that application users need to be able to change these values on the fly, then you only have to write a single screen and users can change all the values. Under the normalized model, you have to have different logic for editing every different list of values.

  • You can centralize certain business rules around "list of value" data. For example, looking at the editing requirement from the previous bullet, what if you wanted an audit trail when people made edits. Or what if you wanted values to be able to be "expired" but still remain in the system. Or what if you wanted to have certain lists be sorted by a specific order, as opposed to just alphabetical. Centralizing "lists of values" into a single model let's you centralize all of these business needs.

Every case is different, so you have to weigh the pros and the cons. But this pattern is certainly used in the industry, and it is not without benefit.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.