Ticket #1332930 (Feature Requests)

Chuck, Charlie and Charles charles at charlesmcnulty.com
Fri Aug 11 22:08:33 CEST 2006


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:
1) some flexibility in creating and removing fields
2) 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





More information about the Dev mailing list