A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
What is the best approach for dynamically extending relational database schema to accommodate client-specific data elements (custom fields)?
Saturday, December 31, 2005
I've seen it done two ways:
1. Object oriented-ish. Create a meta table of fields, then have a table of properties which contains:
Source Table Name
Source Record ID
When you load up record #13 from the widget table, collect all records with a Source Table Name of "widget" and a Source Record ID of "13."
This get's a little confusing since the data in the properties table is "vertical," instead of "horizontal," like a traditional table. But you don't have to mess with any ALTER TABLE statements.
2. One-to-one extension. Your "widget" table is your system table that stays sacrosanct. But you also have a "widget_meta" table with a 1-to-1 relationship back to "widgets."
Users can add and remove (actual) fields from this meta table. Join the two, and there you have it. Remember, however, that you'll have to have another table of field data -- title, description, validation, and such -- that can't be represented in the meta table definition.
This forces you to mess with ALTER TABLE statements, and build methods for your users to invoke them, but the table join is simple and the data ends up in one big table (build a view for it, and make it even easier).
Saturday, December 31, 2005
At a previous company we did #2. "Core" fields were in one table, and custom fields were in another. Adding/Removing were not ad-hoc -- they had to go through an implementation consultant to get changes made, who used an in-house tool to propagate changes to QA & production systems.
The sucky part is that when customers found out they could add custom columns, they would really go overboard, basically designing the system they were replacing all over again. But then budget constraints would hit home and they'd drop half those columns. It caused a lot of thrash in the implementation group as they were whiplashed back and forth (customers can't plan).
Agreed -- don't let them do it ad-hoc, implement some signed-off process. Customer's who treat the computer (and the database) as some magical do-everything-for-me resource they can change on a whim create lots of thrashing for the DBA.
And somebody needs to maintain some configuration control over the whole thing.
Sunday, January 01, 2006
Sometimes it is a lot better to just add another table each time.
A situation we had once involved many similar columns and lots of different ones based on each client. Historically we'd had a total of 12 clients with only 6 currently active and any future clients would be on a similar order of magnitude.
Anyway, we went with the metatables and it required all sorts of hoops to jump through to do searches and comparisons. We should have just made a table for each client and our necessary lookups would have been a single simple select * from client_table. Oh how I wish we'd done it that way.
>> And somebody needs to maintain some configuration control over the whole thing. <<
That's the part they didn't do that well. :-(
When upgrade time came around, the customers would squeal like stuck pigs when they found out the migration was going to break all their gee-whiz custom stuff, because there wasn't an overall direction/plan for the product.
It really is a limitation of databases that they don't make ALTER TABLE trivial.
I recall back on the very old SQL Server (running on OS2) having to create a new table, move the data across, delete the old table and then rename the new one etc. Have things really not got better??
new nick, new rep
Tuesday, January 03, 2006
For one of our applications, we have a single table with both the default fields and the custom, user added fields. The table starts out only having columns for the default fields.
When a user adds a custom field (using a GUI configuration interface) a column with an autogenerated name and the selected datatype is added to the table. A second 'metadata' table links the customers custom field name to the data table's auto-generated name.
When a user deletes a custom field, the corresponding column and meta-data entry are removed (all data for the custom field is lost)
When a user edits a custom field, only the name of the field and its display position in the GUI (both stored in the metadata table) can be edited. The datatype is fixed when the field is created.
This method adds some complexity when the user configures the custom field (infrequently), but it simplifies the database structure for inserts, queries, and third party backend reporting.
"Source Table Name
Source Record ID
We did something like that. In addition, we also added type information and constraints to the metadata table so the GUI could pick up on it and show, for instance, a checkbox for a Boolean field or a drop-down seletion list.
It was a real pain in the ass. YMMV.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz