I've got a question about database architecture.

We're creating a Content management systems. You will find lots of fields which will have pre-populated choices. A good example is Customer's Credit Status could be "Good","Bad","Unknown", or "Have a Deposit". A spec from the project is the fact that these pre-populated choices be dynamic, the admin can also add new values through the after sales. So I have to store these values within the database.

I'm battling to determine between two approaches

1) Possess a table for every type of list. Example could be tables like list_CrediStatus, list_Branches, list_Marketplaces, etc.

Advantages are the tables aren't huge and they're outside of each other. Therefore data and query strain on one table may not effect others? Disadvantages are that you will see most of them. Maybe 30? Which there will have to be a question per table.

2) Have two tables. Possess a description table where you're able to define all of the different list names (list_CreditStatus, list_Branches, etc.) Have another table consists of all of the values of lists along with a foreign key that links each row to the identifier within the description table.

Advantages are less tables, 1 query and uniformed format. The disadvantages may be in performance. This table will have to be queried a great deal. It'll have many rows and lots of data.

Does anybody have advice? I'm leaning towards Option 2. Also tell me if this doesn't seem sensible. It had been a tough question to create clearly.

Thanks, Jed

Keep like things in a single table and in contrast to things in separate tables. which means you opt for option 1. Remember: A superficial similarity according to area names does not necessarily mean they're like things.

The only-table option would be seductively appealing since it looks simpler but it's not. The code you will have to keep these separate becomes quite complicated.

Plus, you can't take advantage of the proper foreign key. How can you state that a purchase includes a column CREDIT_STATUS that references their email list table, without enables somebody to decrease inside a Bloodstream Type (as well as other) value?

I'd possess a table per type. How Come ? Among some other reasons, you might locate fairly easily these data types progressively accrue information particularly for your type. If things are consolidated into one table, that will be tough to look after.

(disclaimer: I have done this type of system having a table that contains several weeks, days, kinds of commodity, true/false (yes!), holiday dates etc. It had been essentially an enormous miscellaneous grab bag and was similar to the Celestial Emporium of Benevolent Knowledge)

Don't be concerned about queries per table. That is what databases are great at, and that i wouldn't optimise too soon. Not until you have problems/issues.

what you're speaking about is struggling it really includes a title. It's what's generically referred to as an anti-pattern - a pattern of software development to become prevented.

the title may be the One True Lookup Table.

I incorporated one link, use that term you will find others.

tables are great, referential integrity is nice.

Use both if needed.

It may sound in my experience like you are speaking about tables of domain constraints, however i might be wrong. If you're, indeed, speaking about domain constraints, you would like each domain constraint in the own table. These two tables are defensible,

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

and both of them are better (IMO) than the usual table that implements a website constraint while on an integer since it's primary key. (Because each such table requires one more join to obtain information humans may use.)