January 12, 2009 at 3:32 pm
I have a design question that I've been mulling over... I think it's a case where there is no good answer and I'm going to have different problems depending on which direction I take. That said, here's what I'm doing:
Imagine a task table applied to onboarding a new employee. A task can be assigned to different types of entities. It might be assigned to the incoming employee (effectively someone outside the company), it might be assigned to that employee's manager, it might be applied to a team of people, it might be applied to an onboarding mentor, etc. etc. In this case the relationship itself is just as important as who the entity ends up being.
There are different ways to accomplish this... I can create a "TaskAssignee" table in which I store the relationship and the entity ID (which might be the ID of a team, the ID of an employee, the ID of the external person, etc). Doing so would basically be an EAV type design which scares me because joins become very difficult. I'm effectively overloading the ID column to represent the ID of different entities, thus a foreign key becomes impossible. That said, it offers the maximum flexibility. Another option is to define columns for each relationship type (either on the task table or on an associated table) and leave them nullable so that if the task gets assigned to a team, for example, I fill out its ID in that column and leave all others null. That option seems to stink too because the schema of the table has to change every time a new relationship is identified and it leaves it up to application logic to ensure that multiple assignment columns don't get populated. On top of that, the assignee's relationship is lost outside of understanding the columns. So, both have benefits and drawbacks... am I missing some third (and better) solution?
A related example is the concept of filters on a task. So, a task only applies to certain geographical regions, departments, job types, etc. The example is a bit more complex than the assignee one because I might have multipe filters (geographic - United States, geographic - United Kingdom, department - Finance). The EAV type solution lends itself well in that I might create a table called TaskFilter which would include an ID, the filter type and the filter ID (i.e. 1 | DEPARTMENT | 10; 1 | REGION | US; 1 | REGION | UK). Option two would be to create a TaskRegionFilter table and a TaskDepartmentFilter table and so on. Again, the drawback there is having to create a new table every time a filter is identified. Honestly, as I write this one, the second option is seeming better and better.
So, thoughts?
January 13, 2009 at 5:18 am
Hi!
If you consider the normalization rules you will end up with one "task assignment" table for each entity table you want to assign task to. In this scenario will not alter the table each time there is a new entity you want to assign, you just will add another table. This solution has the best performance since the assignment table does not contain assignments for all entities.
Lets say you have en employee table, an employee assignment table and your task table. The employee assignment table only contains employee assignments and nothing else instead of every assignment . Another thing you have to think of is the rules for tasks, is it possible to assign a task to multiple entities? If its not, you have to add a assignment type and include it in the primary key of the task table.
/Senior development DBA
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
January 15, 2009 at 8:39 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply