What DB design to use for collecting configurable demographic data?

Question

I'm working on an application for a charitable student organization. The application will track participation and fund-raising for (primarily) student participants in an event. One of the things we'd like to do is collect some demographic information about students who register for the event from our enterprise directory to use in evaluating marketing and to construct reports for the administration on student participation by year, major, college, etc. One of the goals of the project is to market the application to similar organizations at other universities. I'm designing the app with a plugin architecture for authentication and directory access to make it more general.

So far I've come up with a couple of different ways to store demographic information in the database. I can assume that most universities will like to collect (and have sources of information for) similar demographic information. In this case I can keep appropriate parts of the demographic information (those things that change yearly) in columns in the registrations table and the rest in the participants table (those things that are constant for an individual). Alternatively, I've considered keeping a demographics table that holds a registration id, attribute, value triple for each registration and desired attribute. I think that the first option is much easier to query against for reports, whereas the second option is much more flexible.

I'm wondering if there are other alternatives that I haven't considered. Or, if you've faced a similar problem how did you handle it (and how satisfied were you with the result)?

Answer 1

If you write your code in such a way that adding columns won't break it (or will be easy to update), for example being careful with SELECT * and specifying columns in INSERT, then from your description it doesn't sound like you need the ultra-flexibility of attribute-value, so I'd stick with option 1.

Answer 2

Here are my thoughts:

• You shouldn't tie in demographics with registration. It doesn't make sense logically and this can always lead to practical issues. You can instead create a demographics history table, e.g. StudentID, StartDate, EndDate, xxxDemographics, yyyDemographics...)
• Different academic insituttions have may have quite different demographics needs. To reduce customization, you may want to softcode all the definitions in a configuration table e.g. Demographics (DemographicsID, DemographicsDesc) and keep a relationship table between Student and Demographics e.g. StudentDemographics (StudentID, DemographicsID...). The only trick with the above solution is that you may want to use PIVOTing on some of the reports.