I'd like to do a status list. I have 1..n projects. Each project contains 0..n tasks, for which I'd like to report a status, next phase, priority, etc…
For each project, I'd like to setup which kind of column I want to use, for instance :
- Project A : task ID, name, long description, next phase (date), ontime (boolean)
- Project B : task ID, name, next phase (date), priority (limited set of values)
I also want to be able to configure the use of a field for a given project. In my example, I want to display next phase (a date) using a different format for instance. Some fields may be computed values (eg : late = true if today() > next phase()
I though of the following model :
- one table for projects (with general metadata about the project)
- one table with available column types
- one link table between the project and the column types, with attributes on the configuration of the column (in my example, the date pattern)…(I'm no so happy about this part, as I would need to extend the table every time I add a new time with new configuration possibilities)
- one task table, with a foreign key on projects, a foreign key on column types, and one column for the value
eg : the task on project B (12, "Write specifications", 12.12.12, "High") would be represented with 4 lines :
B, 1 (assume it's PK for ID column type), 12
B, 2 (assume it's PK for description column type), "Write specifications"
B, 3 (assume it's PK for date column type), 12.12.12
B, 4 (assume it's PK for priority column type), High
I'm not so convinced…do you have any suggestion ?
Maybe I should not model the column types in the DB ?
my biggest issue is to model the fact I have an undetermined number of attribute for a task in the project, and that 2 projects may have a different table size
Best Answer
What you're thinking about is either "row modelling" or "entity-attribute-value" (EAV) - See Wikipedia - either of which is a common subject for religious wars. I won't get too far into the pros vs. cons. If you read up on EAV and row modeling you'll see lots of discussion on that.
What I will say is that you should model all of the common predicates using properly normalized tables and rely on EAV (or row modelling) only for the customizable columns (if at all). This is how many highly customizable packaged solutions deal with allowing users to add their own attributes to existing objects. It isn't clean, easy or efficient, but it is probably the best compromise for allowing this particular type of customization when you have no way of reasonably predicting what the customized columns might be at design time.