If you want to add another column name, just insert a new record into Contract Column Lookup ---------------------- col_id col_name
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, Mike Hillyer wrote: > Well, lets say that you suddenly remember that you need column X in the > user table. In the normalized model you have to do one ALTER TABLE > statement. In the design you have in place you need n ALTER TABLE > statements where n = the number of users. It can also be easier to > program against and manage normalized data. > > That being said, if your users have security concerns you need to > maintain separate tables, as there are no views in MySQL (yet) and > therefore you cannot prevent users from seeing each other's data in a > normalized model. > > On another note, 2 million rows should not pose any performance issues, > I can search tables with millions of rows and get back results quickly > as long as I practice proper indexing (having fixed length rows also > helps and is not hard to achieve). > > I would say that as long as contact privacy is not a concern, use the > normalized approach for management ease. > > Regards, > Mike Hillyer > www.vbmysql.com > > > > -----Original Message----- > > From: Jackson Miller [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, July 02, 2003 10:47 AM > > To: Jake Johnson > > Cc: [EMAIL PROTECTED] > > Subject: Re: More tables or more joins > > > > > > 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/mysql?> [EMAIL PROTECTED] > > > > > > -- > > > > MySQL General Mailing List > > For > > list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
