I'm wrestling with a schema design issue.
I'm attempting to determine when I should add another column instead of making it an "attribute".
Rather than adding a numerous columns to a table, I could use an attribute table, with supporting tables if needed, to store this information.
When users decide they need to track another field, it just gets added to the attribute table.
I'm doing the same thing for people on the request. Rather than putting
ManagerID columns in the table, I add them to a
contacts table or
approvers table so that a record can have as many related items as needed. This also prevents the user interface from having to add new fields.
This definitely makes understanding the schema more difficult, but it also makes it more flexible. Of course, those attributes will be limited on how detailed they can be in terms of a sub-hierarchy (unless I want to get really complicated).
I'm just getting this off the ground for some business users who will maintain it, so I can't make the schema too complicated.
Do you think pursuing this strategy is a good idea, or should I pursue a design the just adds columns to tables?