Jason Dixon wrote:
No offense, but given the design criteria Thomas mentioned earlier (iCal-like), doesn't it make more sense to use a "free field" method? That is, to create a single table of "contact" key/value pairs associated with the user id? Then you can easily pull all pairs associated with the user and arrange them according to a known set of keys (first_name, last_name, email, phone, im_contact, etc).
This approach has worked well in the past for systems like OTRS. Just my $0.02.
I don't like it because it makes things like sorting multiple contacts, sorting by numeric/non-numeric fields, and having fields like check boxes, drop-down lists, etc. needlessly complicated. In essence I don't like it because the more features you want to implement, the more you've moved towards creating a database engine on top of a database engine. In other words there's already a system for creating paired fields and values, and it's called a table.
With the system you propose you gain:
- some flexibility in creating and removing fields
- some storage space
but you lose all of the built in functions of a database engine, such as default data, data types (including date manipulation), etc. What about grouping bits of that data together? Let's say you want a list of all phone numbers for a contact. In a normal contact table you just hang a table off of it called phone_numbers {phone_id; contact_id; description; phone_number} and voila, you have lists of phone numbers. Now imagine trying to do that in the systems being proposed. Sure you can create a single phone number label, but what if you want to allow them to label the phone numbers with "home" "work" "cell" etc. You either have to put that data in the "data" portion, mixing it with the phone numbers (forget about ever sorting by area code), or you have to create unique labels for each type, but then they can't be grouped without having another table grouping all of them by their label.
And this is just one example off of the top of my head. Another one: How do you handle multi-lingual labels? I'm not saying that each of these challenges couldn't be solved, I'm saying that it's a crap load of work and not worth it for the benefit's described so far.
I'm saying this from experience implementing exactly what you're describing for a registration database. At first it seemed totally reasonable to allow the conference department to create their own fields for each event, but in practice it just flat out didn't work. When we finally had to scrap the system and purchase an enterprise system, it was, in essence, a big honkin' table with everything you could possibly want about an event, with the ability to select which database columns you need for any specific form or worksheet. Does it need to be updated occasionally? Yes (for the new credit card verification number, for instance), but very rarely, and the updates are not that painful.
For your own sanity, let the database engine be the database engine, don't try to build a new layer of fields and data on top of a system designed for exactly that.
-Charles