I appreciate the idea of normalizing, but those tables wouldn't meet the spec. There would also have to be a column value table at the very least. Also, why would you have user_id and cont_id in both the user_table and the contract table.
Also if you read my post you would see that I am talking about a minimum of 200 users each with an average of 20,000 contacts (with no overlap). This means that the contact table would have a minimum of 2,000,000 rows just to get started. The alternative would be to have 200 tables with 20,000 rows each. I understand that having this many tables is crazy, but I don't understand why it is not better. -Jackson On Wednesday 02 July 2003 11:49 am, Jake Johnson wrote: > You don't want to have a separate table for each user. That would cause a > maintenance nightmare. > > Try normalizing your data.... > > user table > ---------- > user_id > cont_id > user_name > > > Contract lookup > ---------------- > cont_id > Cont_Name > > Contract Column Lookup > ---------------------- > col_id > col_name > > Contract table > ---------------- > user_id > Cont_id > col_id > qty > > This should be a good start... > > Regards, > Jake Johnson > [EMAIL PROTECTED] > > ______________________________________________________________________ > Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on > Rims, Car Audio, and Performance Parts. > > 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. > > I am considering giving each user it's own table for storing contacts. In > > this scenerio I would provide a means for editing the columns in the > > table. > > > > The other scenerio is to have a table to store field names, their type, > > and their default value and their account relationship. Then another > > table would store the contacts for all accounts with an account > > relationship. A final table would store relationships and values of > > contacts and the fields. > > > > I am mostly concerned with speed. My guess is that the first scenerio > > will be faster as long as all the queries only search the contacts for > > one account (i.e. one table). However I am a little concerned about > > having hundreds (and eventually thousands) of tables. > > > > Does anyone have experience with this kind of situation? > > > > Thanks, > > -Jackson > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
