Unanswered: Data Modeling / configuration data question
Hi, I'm looking for some opinions on developing a data model for a new system I'm building.
I've worked with an Oracle package for years and it makes extensive use of 2 codelist tables (codelists/codelist details) which allows you to store alot of metadata convienently in the database. I really like this technique and plan on using it extensively in my own application.
To give you an idea how this works, a user might be able to configure an employee data entry screen through a form. Instead of storing this metadata in a table, you can create, 1st, an entry in the codelist table:
Then store these configuration options and their values in a codelist details table (joined by id):
show id? Yes
max number of recs to display 10
and so on...
This really helps keep a clean data model. What I'm looking for is opinions on when it might be a better idea to store 'configuration' data in its own table. For example, an emp table traditionally might have an employee_type table with id/name entries of 1/manager, 2/temporary, etc. Then these tables would be joined. However, what's the reason that even these types of configuration tables couldn't be stored in a codelist too?
Anyone have any advice on what factors contribute to making the codelist vs. type table decision?
I use the same method very often, but I keep it to simple data, which only has 2 or 3 fields (e.g., short code, description, comment, in addition to the unique sequences to join everything), like employee type. It does cause a problem with foreign keys (a foreign key will reference anything in the code table, not just the code for that codelist). Also, it can quite confusing to junior developers when they need to join codelist/codelist detail multiple times in the same query (views can aid in this, but then you need to keep creating views for new codes). The main problem I've had is when I add a new codelist, start using it, and then find out that I need more columns to support this codelist, or it requires extra validation rules that would make a check constraint impossible, and triggers just downright confusing (keep the junior level people and your successors in mind).