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]
