Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
You're trying to model MS project?? –  FreshPhilOfSO Nov 22 '12 at 22:02
    
no, just a status report...but I work on several projects. Today I do this in Excel and this gets annoying –  Julien Bidault Nov 22 '12 at 22:14
    
How many different project attributes do you expect? I'd expect that most of them (say TaskID, name, next phase and so on) will be common. On the other hand, formatting the same data (say, next phase) differently between projects will add flexibility, but will confuse you for sure. –  dezso Nov 23 '12 at 9:05
2  
What you're thinking about is either "row modelling" or "entity-attribute-value" (EAV) - See en.wikipedia.org/wiki/… - either of which is a common subject for religious wars. I won't get into the pros vs. cons. If you read up on EAV and row modeling you'll see lots of discussion on that. –  Joel Brown Nov 23 '12 at 15:40
    
too bad you didn't post this as an answer, I would have up voted it and mark it as solved. Thanks for the link, this is exactly what I was looking for –  Julien Bidault Nov 23 '12 at 23:15
show 2 more comments

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
can't upvote, don't have enough karma yet. But I flagged it as accepted answer ;) –  Julien Bidault Nov 25 '12 at 17:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.