Database Schema to Support Customizable/Extensible Application
We want to redesign the ticketing system of DrProject so that different sites can customize it to meet their needs. Students in undergrad courses just need an ordered to-do list; companies need all the fields we currently have (with a few more values for some of the enumerations), and one or two more as well.
Coincidentally, Jeremy Miller had a post earlier this week asking the same question I’ve been mulling over: what should the database schema look like to support extensibility? His options are:
- Allow sites to add customs fields to the database --- madness lies in this direction.
- Use "wildcard" fields (which for my money is just option #1 with poor column names).
- Use name/value extension tables.
- Structure the fields (e.g., store XML). I think this is #1 with angle brackets, but I'm not sure...
Have you been there? Done that? If so, what would you recommend? Keep in mind that testability is as important to us as extensibility…