Re: More tables or more joins
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]
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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More tables or more joins
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]
RE: More tables or more joins
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]
Re: More tables or more joins
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
