This is as much a question as a post, so please, leave me some comments if you have the time.
In the current issue of SQL Sever Magazine, Michelle Poolet has an article about "Overlapping Subtypes." I'm wondering what you all think about this.
The example she uses is very similar to my own case, so I'll describe it in the best way that I can:
I have a people table which of course contains basic information about people that are used in my application.
Each person might perform different "roles" within the application. Such as "broker" or "client" or "sales rep."
Each person might perform 0, 1, or more roles at the same time.
The problem then, is how to store that data. My solution has been to have a roles table and a peopleroles table. The peopleroles table simply relates people to roles. Any given "person" may have 0 or more entries in the peopleroles table. I think this is pretty much the standard way to store this type of data.
However, Michelle mentions some drawbacks (such as performance, and query complexity) that lead her to suggest something very different: Adding columns to the people table, one for each role that a person might perform.
She also mentions another way, to add one column which would contain multi-values, so that you could parse that field to find people who are in certain roles. I dislike this one a LOT, so lets talk about the other suggestion :)
Now, I'm not guru enough to state exactly WHY I don't feel comfortable with the "add a few columns to the people table" concept. But, I thought that I had learned somewhere along the way that "it's generally better to build a solution that allows you to "add a row" when something changes (like a new role to track) than to "add a column" to my table. Did I word that correctly?
Anyway, if I were to do it the way she suggests, certain parts of my app would be SO much better. I'm seriously considering it. I'd like to hear other people's comments.
What do YOU think?
| posted on Wednesday, October 29, 2003 8:32 AM