Re: Help with Table structure
Not sure how someone can intelligently comment on your table structure when you haven't given any details of the data you are storing. In my experience, the fact that you have 75 fields in your table is a strong indicator that your data is not normalized. If that is the case you tables are likely much larger than they need to be and queries may be slower. In general the larger your tables are the slower queries are going to be, if it is too slow for you depends on your hardware and how fast you need it to be. Also not knowing how large the 75 fields are, makes it hard to make any guess on this either. If they are all INTs or char(1)s, then that really isn't that much data and half a million records won't be all that much to handle. You also don't mention which database engine you are using. MyISAM will be much faster than some of the others, if you don't need to do transactions that would be what I would use. Velen wrote: Hi, I have a table containing 75 fields with a primary index and index set on 5 other fields. Everything is working fine so far as the table contains only about 80,000 records. I expect these records to reach 500,000 by end of september. I would like to know: - if the number of records will slow down my queries when I search on the indexed fields? - if manipulating the records within the table will be slow, (i.e. insert into table..., delete from table..., subqueries) Can anyone advise? Also, if you have a similar table please let me know your pros and cons for this kind of table structure. Thanks. Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Table structure
Thanks Chris for your comments. The table is actually storing CVs of jobseekers. With most fields being Varchar (50), varchar(100), varchar(150). The database is using MyISAM. I agree with you that the table could be optimized. The problem is that when we setup that table and that project the table was supposed to contain a maximum of 50,000 records, but now that the website is getting more and more vistors, the number of records is expected to be 10 times more than expected. I just wanted to know if it is worth going for a complete re structuring of the db or if this table would still be able to handle the records. I think from your explanation, I better go for a complete restructuring. Thanks. Velen - Original Message - From: Chris W [EMAIL PROTECTED] To: Velen [EMAIL PROTECTED]; MYSQL General List mysql@lists.mysql.com Sent: Monday, August 11, 2008 19:14 Subject: Re: Help with Table structure Not sure how someone can intelligently comment on your table structure when you haven't given any details of the data you are storing. In my experience, the fact that you have 75 fields in your table is a strong indicator that your data is not normalized. If that is the case you tables are likely much larger than they need to be and queries may be slower. In general the larger your tables are the slower queries are going to be, if it is too slow for you depends on your hardware and how fast you need it to be. Also not knowing how large the 75 fields are, makes it hard to make any guess on this either. If they are all INTs or char(1)s, then that really isn't that much data and half a million records won't be all that much to handle. You also don't mention which database engine you are using. MyISAM will be much faster than some of the others, if you don't need to do transactions that would be what I would use. Velen wrote: Hi, I have a table containing 75 fields with a primary index and index set on 5 other fields. Everything is working fine so far as the table contains only about 80,000 records. I expect these records to reach 500,000 by end of september. I would like to know: - if the number of records will slow down my queries when I search on the indexed fields? - if manipulating the records within the table will be slow, (i.e. insert into table..., delete from table..., subqueries) Can anyone advise? Also, if you have a similar table please let me know your pros and cons for this kind of table structure. Thanks. Velen -- 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]
Help with Table structure
Hi, I have a table containing 75 fields with a primary index and index set on 5 other fields. Everything is working fine so far as the table contains only about 80,000 records. I expect these records to reach 500,000 by end of september. I would like to know: - if the number of records will slow down my queries when I search on the indexed fields? - if manipulating the records within the table will be slow, (i.e. insert into table..., delete from table..., subqueries) Can anyone advise? Also, if you have a similar table please let me know your pros and cons for this kind of table structure. Thanks. Velen
Re: help with table structure
What you have so far looks good, but what I learned from doing my ecomm project was that it is beneficial to make a separate table for anything and everything that you might have more than one of... Addresses, phone numbers, and email addresses are all great candidates for breaking out into another table. That way, if you want to have a billing address and a shipping address for each customer, for example, all you have to do is have an address table with all of the address information, and a relation table that describes the relationship between an address and a user: address_id int not null, (foreign key corresponding to an address ID in your address table) user_id int not null, (foreign key corresponding to a user ID in your user table) address_type int (1=billing address, 2=shipping address, 3=secondary shipping, etc.) I hope this helps. Chris W. Parker wrote: hello, i'm just looking for some examples of a customer table that some of you are using for your ecomm sites (or any site that would need a customer table). here is mine so far: (horrible wrapping to follow...) mysql describe customers; ++-+--+-+--- --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+--- --++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | fname | varchar(20) | | PRI | || | lname | varchar(20) | | PRI | || | address1 | varchar(40) | | | || | address2 | varchar(40) | YES | | || | city | varchar(20) | | | || | state | char(2) | | | || | zip| varchar(10) | | | || | phone | varchar(20) | YES | | || | fax| varchar(20) | YES | | || | email | varchar(64) | | PRI | || | newsletter | tinyint(1) | | | 0 || | password | varchar(32) | | | || | signupdate | datetime| | | -00-00 00:00:00 || | lastvisit | datetime| | | -00-00 00:00:00 || | type | tinyint(3) unsigned | | | 0 || | company| varchar(64) | YES | | || | is_active | tinyint(4) | | | 0 || | activationdate | datetime| | | -00-00 00:00:00 || | activationtype | tinyint(3) unsigned | | | 0 || ++-+--+-+--- --++ i would appreciate not only table descriptions (like mine above)(if you're willing) but comments on what i have so far as well. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help with table structure
Justin Smith mailto:[EMAIL PROTECTED] on Tuesday, October 12, 2004 8:48 AM said: What you have so far looks good, but what I learned from doing my ecomm project was that it is beneficial to make a separate table for anything and everything that you might have more than one of... Addresses, phone numbers, and email addresses are all great candidates for breaking out into another table. interesting you say that because i was going to do this same thing except not as completely as i probably should (which i think is what you are suggesting). what i mean is, my extra table of addresses was going to be merely shipping addresses for the customer and nothing else. but i guess i should change it from being just shipping addresses to include any kind of address relating to the customer? but what about phone numbers? i'm a bit unsure on that one. in my table as it is now, i have three: business, home, and fax. in what case would having a phone (number) table be beneficial? I hope this helps. yes thank you. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with table structure
Chris W. Parker wrote: interesting you say that because i was going to do this same thing except not as completely as i probably should (which i think is what you are suggesting). what i mean is, my extra table of addresses was going to be merely shipping addresses for the customer and nothing else. but i guess i should change it from being just shipping addresses to include any kind of address relating to the customer? but what about phone numbers? i'm a bit unsure on that one. in my table as it is now, i have three: business, home, and fax. in what case would having a phone (number) table be beneficial? The phone number table is probably a little less important since there isn't much data to be stored, but if you for some reason wanted to add another phone number (pager, cell phone, etc.) it's a lot easier to just add another row to your phone number table than it is to add another column to your User table. Also keep in mind that not every user is going to have every type of phone number (I don't have a fax number, for instance). This is only what I've learned from my experience, though, which is pretty limited. Do any of the uber data modelling DBA guru types out there have any input as to what is easiest/most efficient? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help with table structure
You were wondering about a separate phone number table? Most phone numbers (esp. cell phones and home phones) belong to only one person. If that were your only data, then creating a new table may not make sense. However, dozens of people can share a common number (like in a large office. Each person doesn't get their own number rather they get their own extension). Phone numbers can also be international (you can't rely on (ac) ###- being able to hold the whole number). Other phone numbers come with access codes (Dial a number, wait for a tone, type in your access code, get connected). What happens if someone has 3 cell phone numbers? ( I know someone like that, they are a salesperson and they have to use a different phone (for the service and for the better rates) depending on where they are in the world). You wouldn't want 3 fields on your table with 1 of them being empty for all but one record, would you? A mapping table like was described would equate a user_id to a phone_id and could also contain a PIN# or an Extension # for those users that need them. If you wanted to get really fancy, you could put a startdate and a stopdate on the mapping table. That way if you ever wanted to see a history of who had that number, there it would be. (If anyone ever belonged to the same number more than once, they would have multiple records in this table) Basically, you end up with better, more accessible data doing the phone numbers in their own table. You could add more fields like phone_type (cell, fax, pager, blackberry, etc), and notes for each phone (This is an EU #) and maybe even a do not call flag (or would that go on the mapping table? hmmm...) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris W. Parker [EMAIL PROTECTED] wrote on 10/12/2004 12:55:54 PM: Justin Smith mailto:[EMAIL PROTECTED] on Tuesday, October 12, 2004 8:48 AM said: What you have so far looks good, but what I learned from doing my ecomm project was that it is beneficial to make a separate table for anything and everything that you might have more than one of... Addresses, phone numbers, and email addresses are all great candidates for breaking out into another table. interesting you say that because i was going to do this same thing except not as completely as i probably should (which i think is what you are suggesting). what i mean is, my extra table of addresses was going to be merely shipping addresses for the customer and nothing else. but i guess i should change it from being just shipping addresses to include any kind of address relating to the customer? but what about phone numbers? i'm a bit unsure on that one. in my table as it is now, i have three: business, home, and fax. in what case would having a phone (number) table be beneficial? I hope this helps. yes thank you. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with table structure
Also International Numbers Have you seen any address books accomodating 3 digit Country Code?? Martin- - Original Message - From: [EMAIL PROTECTED] To: Chris W. Parker [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 12, 2004 1:20 PM Subject: RE: help with table structure You were wondering about a separate phone number table? Most phone numbers (esp. cell phones and home phones) belong to only one person. If that were your only data, then creating a new table may not make sense. However, dozens of people can share a common number (like in a large office. Each person doesn't get their own number rather they get their own extension). Phone numbers can also be international (you can't rely on (ac) ###- being able to hold the whole number). Other phone numbers come with access codes (Dial a number, wait for a tone, type in your access code, get connected). What happens if someone has 3 cell phone numbers? ( I know someone like that, they are a salesperson and they have to use a different phone (for the service and for the better rates) depending on where they are in the world). You wouldn't want 3 fields on your table with 1 of them being empty for all but one record, would you? A mapping table like was described would equate a user_id to a phone_id and could also contain a PIN# or an Extension # for those users that need them. If you wanted to get really fancy, you could put a startdate and a stopdate on the mapping table. That way if you ever wanted to see a history of who had that number, there it would be. (If anyone ever belonged to the same number more than once, they would have multiple records in this table) Basically, you end up with better, more accessible data doing the phone numbers in their own table. You could add more fields like phone_type (cell, fax, pager, blackberry, etc), and notes for each phone (This is an EU #) and maybe even a do not call flag (or would that go on the mapping table? hmmm...) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris W. Parker [EMAIL PROTECTED] wrote on 10/12/2004 12:55:54 PM: Justin Smith mailto:[EMAIL PROTECTED] on Tuesday, October 12, 2004 8:48 AM said: What you have so far looks good, but what I learned from doing my ecomm project was that it is beneficial to make a separate table for anything and everything that you might have more than one of... Addresses, phone numbers, and email addresses are all great candidates for breaking out into another table. interesting you say that because i was going to do this same thing except not as completely as i probably should (which i think is what you are suggesting). what i mean is, my extra table of addresses was going to be merely shipping addresses for the customer and nothing else. but i guess i should change it from being just shipping addresses to include any kind of address relating to the customer? but what about phone numbers? i'm a bit unsure on that one. in my table as it is now, i have three: business, home, and fax. in what case would having a phone (number) table be beneficial? I hope this helps. yes thank you. chris. -- 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]
help with table structure
hello, i'm just looking for some examples of a customer table that some of you are using for your ecomm sites (or any site that would need a customer table). here is mine so far: (horrible wrapping to follow...) mysql describe customers; ++-+--+-+--- --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+--- --++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | fname | varchar(20) | | PRI | || | lname | varchar(20) | | PRI | || | address1 | varchar(40) | | | || | address2 | varchar(40) | YES | | || | city | varchar(20) | | | || | state | char(2) | | | || | zip| varchar(10) | | | || | phone | varchar(20) | YES | | || | fax| varchar(20) | YES | | || | email | varchar(64) | | PRI | || | newsletter | tinyint(1) | | | 0 || | password | varchar(32) | | | || | signupdate | datetime| | | -00-00 00:00:00 || | lastvisit | datetime| | | -00-00 00:00:00 || | type | tinyint(3) unsigned | | | 0 || | company| varchar(64) | YES | | || | is_active | tinyint(4) | | | 0 || | activationdate | datetime| | | -00-00 00:00:00 || | activationtype | tinyint(3) unsigned | | | 0 || ++-+--+-+--- --++ i would appreciate not only table descriptions (like mine above)(if you're willing) but comments on what i have so far as well. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
