Tell me more ×
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.
up vote 1 down vote favorite

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 ApproverID, ExecutiveApproverID, 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?

share|improve this question

1 Answer

up vote 1 down vote

There are two schools of thought with this type of design:

  1. The developer / DBA.
  2. The users.

The Developer/DBA will want to create a system that self-maintains. In other words precisely what you are pursuing above.

The Users will want a very simple system that they can understand without really understanding anything other than their day-to-day job.

You will be successfully fulfilling your role if you create a system that is easy to manage and not too difficult to learn.

My experience tells me normally the user wants a flat-file based system where they can easily see all the fields "they would ever need" on a single form, without having to design anything themselves. As a developer/DBA I have created both flat-file based designs and highly normalized designs. The flat-file designs were me deciding I could no longer tolerate trying to convince the user that they really did need to be able to extend their design without getting me to do the work in the future. Needless to say, around 10 milliseconds after I put their "everything I'll ever need" system into production, they wanted to add some fields. Me bitter? Never.

If you can find a user that understands how to manage the normalized design you want to utilize, get them on your side and have them vocalize their concerns about being able to extend the design without having to involve I.T. for every minute field change. Then build a really slick user interface that allows them to build the fields (attributes) they want. Then build the back-end the way YOU want. Don't make it so complex that if you put it down for 6 months you'll have a hard time understanding what you did.

share|improve this answer

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.