This might be more appropriate for the desgin forum but maybe someone here can help.

I'm designing a db that will store our customers's dbs. Most of our customers are in the same business so we have a good idea about what the fields will be. But there are fields that are going to be unique to certain customers. So the question is, how do I store these custom fields?

Here are the two solutions I can think of:
1) create table custom (
user_id
,field
,value)
pros: unlimited custom fields
cons: bad for querying

2) create table custom (
user_id
,field_1
,field_2
...
,field_100)
pros: good for queying
cons: limited # of fields, data might exceed max row width (I'm using 11.9.2 and I think the max is 1980 bytes)

I'm leaning towards #2 since I'm most concerned about querying. Can anyone offer up a better way?

Provided you have an index on userid, field the first method should not be too bad (unless you have many, many, many rows).

What you could do is create a customer table which stores the common customer values.

Then as customers need unique attributes stored create an attribute table for them, with the columns storing exactly what you need.

Maybe I'm not thinking about this right but the first method seems like it won't work for querying. For example, if I was querying for everyone that had a gender of male and a marital status of divorced, the following query wouln't work:
select count(1)
from custom
where field = "gender" and value = "male"
and field = "marital_status" and value = "divorced"

I'd have to create temp tables or do a bunch of exists statements. Is there another way? The customer attribute approach might work.

I'm actually working on the same sort of issue where I need to allow for custom fields. Definitely multiple solutions...each with their own issues!! You might find this link interesting:

http://discuss.fogcreek.com/joelonso...w&ixPost=28256

By the way, if you go with the 1st approach...say we have a user_contact table with three fields, user_id, field_name, and value., and we want to retrieve all users with a last name beginning with b, ordered by last_name then first_name. You could "flatten" the data like this:

select distinct tuc1.user_id,

(select field_value from tb_user_contact tuc2 where
tuc1.user_id = tuc2.user_id and field_name='first_name') as first_name,

(select field_value from tb_user_contact tuc2 where
tuc1.user_id = tuc2.user_id and field_name='last_name') as last_name

from tb_user_contact tuc1

where

(select field_value from tb_user_contact tuc2 where
tuc1.user_id = tuc2.user_id and field_name='last_name') like 'b%'

order by last_name,first_name

Not pretty, I know, but it does work. I haven't tried it on large datasets, however. Might just be faster to build a temp table and munge the data into it.


Another approach I've considered is to "serialize" the custom data into a blob field containing XML (to at least give the blob field some structure). Doesn't make querying so easy though...or sorting.

I'm leaning towards the approach with a "base" TB_USER_CONTACT table and a TB_USER_CONTACT_CUSTOM table with the custom fields in it, and a Web-based interface allowing an end-user adminstrator to actually add/delete fields in the _CUSTOM table as needed. I'll then have a view VW_USER_CONTACT which simply joins the two tables on the USER_ID to abstract things a bit.

Good luck...

-Dave