Jackson Miller wrote:
>I understand that having this many tables is crazy, but I don't understand why
>it is not better.
>
Several reasons. One is complexity, another is administration.
Using one table per user is nasty because it's too complicated. You
have 200 tables to keep track of, each with its own structure.
Administration becomes a problem because everything is multiplied by
200. You decide next year that you need to track another field? No
problem... just modify 200 tables! A new key? 200 times!
You also end up with difficulties when you want to generate cross-user
reports, for instance to show all contacts that multiple users are
dealing with.
And then you need to build a new table each time you get a new user, and
presumably drop tables when users go away. It won't be pretty, even if
you automate as much as you can.
>>On Wed, 2 Jul 2003, Jackson Miller wrote:
>>
>>
>>>I am working on a program that is essentially a contact management tool
>>>for multiple users. There are currently about 200 users and will be over
>>>1000 eventually. Each user may have between 10 and 500,000 contacts.
>>>
>>>Where it gets interesting is that each user needs to have the ability to
>>>control the fields that it is storing for it's contacts.
>>>
If you get more specific about this requirement, we may find a better
solution. For instance, if each user is interested in a different
subset of fields from some universal common set, you can have a common
table with everything, and store (in another table) the fields that each
user is interested in, to build a customized display for each user at
run-time. Or, you could have a 'custom field definition' table keyed by
userid and fieldname, and a 'custom field value' table keyed by userid,
fieldname, and contactid, and create customization that way. This is
probably similar to what you were describing in your second scenario;
I'm not sure, because I don't know what an 'account relationship' is.
Bruce Feist
