Multiple databases tables or combined into one_问答_开发者

I have a question about database architecture.

We are building a CMS. There are a lot of fields that will have pre-populated selections. An example is Customer's Credit Status can be "Good","Bad","Unknown", or "Take a Deposit". A spec of the project is that these pre-populated selections be dynamic, that the admin can add new values via the backend. So I need to store these values in the database.

I am struggling to decide between two approaches

1) Have a table for each kind of list. Example would be tables like list开发者_StackOverflow中文版_CrediStatus, list_Branches, list_Markets, etc.

Advantages are that the tables are not huge and they are separate from one another. Therefore data and query load on one table might not effect the others? Disadvantages are that there will be many of them. Maybe 30? And that there will need to be a query per table.

2) Have two tables. Have a description table where you get to define all the different list names (list_CreditStatus, list_Branches, etc.) Have another table contains all the values of all lists plus a foreign key that links each row to its identifier in the description table.

Advantages are less tables, 1 query and uniformed format. The disadvantages might be in performance. This table will need to be queried a lot. It will have many rows and a lot of data.

Does anyone have any advice? I am leaning towards Option 2. Also let me know if this does not make sense. It was a hard question to write clearly.

Thanks, Jed

Always keep like things in one table and unlike things in separate tables. this means you go with option 1. Remember: A superficial similarity based on field names does not mean they are like things.

The single-table solution is seductively appealing because it looks simpler but it is not. The code you will need to keep these separate becomes quite complicated.

Plus, you cannot make use of a proper foreign key. How do you say that an ORDER has a column CREDIT_STATUS that references the list table, without allows somebody to drop in a Blood Type (or some other) value?

I would have a table per type. Why ? Amongst other reasons, you may easily find that these data types gradually accrue additional info specifically for that type. If everything is consolidated into one table, that's going to be very difficult to cater for.

(disclaimer: I've worked on such a system with a table containing months, days, types of commodity, true/false (yes!), holiday dates etc. It was basically a huge miscellaneous grab bag and resembled the Celestial Emporium of Benevolent Knowledge)

Don't worry about queries per table. That's what databases are good at, and I wouldn't optimise too early. Not until you've got problems/issues.

what you are talking about is so bad that it actually has a name. It's what is generically known as an anti-pattern - a pattern of software development to be avoided.

the name is the One True Lookup Table.

I included one link, use that term you'll find others.

tables are good, referential integrity is good.

Use both when needed.

It sounds to me like you're talking about tables of domain constraints, but I could be wrong. If you are, indeed, talking about domain constraints, you want each domain constraint in its own table. Both these tables are defensible,

cr_status        cr_status_abbr  cr_status
--               -------------------------
Good                       G     Good
Bad                        B     Bad
Unknown (or Unkn)          U     Unknown

and both are better (IMO) than a table that implements a domain constraint by using an integer as its primary key. (Because each such table requires an additional join to get information humans can use.)