Database Schema to Support Customizable/Extensible Application
February 20, 2007 – 2:46 pmWe 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…
4 Responses to “Database Schema to Support Customizable/Extensible Application”
Scott Bellware posted another suggestion [1]: ship the model code. The problems with this approach are (a) end users have to code in order to add fields, (b) who’s going to test it, and (c) who’s going to maintain it in the face of future releases?
[1] http://codebetter.com/blogs/scott.bellware/archive/2007/02/21/158933.aspx
By Greg Wilson on Feb 21, 2007
I’m currently using #3 in a project - I have a few tables that look like (foreign key, key, value), where key is a string and value is repr(python_value) - and am quite happy with it. One idea that I had was to use a simple scheme to pack/unpack values to/from a single string field, but there are numerous issues with that including breaking the first normal form and too much processing in the client to get a value for a key. I hadn’t thought of using XML, but it seem a bit heavy.
By liam on Feb 21, 2007
WordPress uses #3 in the form of the wp_options table, which can hold strings or serialized PHP data structures. This seems to have worked really well for them if the enormous WordPress plugin community is anything to go by. It doesn’t feel very elegant but it works just fine. They also allow arbritrary key/value pairs to be associated with both users and posts through the wp_postmeta and wp_usermeta tables.
By Simon Willison on Feb 22, 2007
Option #3 works well until you get a *lot* of data, and then the relational model starts to break down and your database starts to resemble a great big dictionary.
AFAIK SAP uses option 4, except in German
Oracle E-Business Suite uses a variation on option 2. Each table comes with a number (usually 12) of ‘generic’ columns that the application can be configured to use how and when the end users like. These are known as flexfields if you want to go looking for a better definition on Google. They enable different users to define their own specific values for things that are stored in tables and preserves them through upgrades. The drawback is that at the database level its not immediately obvious what is stored in these columns - did we store originating department in ATTRIBUTE1 or ATTRIBUTE2 of the tickets table for instance?
By Andy Todd on Feb 27, 2007