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.
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.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.
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
