Re: basic style shema question
Hi, Split frequently used columns from other non frequently used. Splitting them will improve the disk access. You don't need to separate as too many tables. You need to index all the tables if you split into many. Saravanan --- On Fri, 1/18/08, Alex K [EMAIL PROTECTED] wrote: From: Alex K [EMAIL PROTECTED] Subject: basic style shema question To: mysql@lists.mysql.com Date: Friday, January 18, 2008, 11:14 PM Hi Guys, Let's suppose I have the following table: create table companies ( id int(11) not null auto_increment primary key, # user's login and password email varchar(100), passwordvarchar(100), # basic information company_namevarchar(100), contact_namevarchar(100), street varchar(100), cityvarchar(100), state varchar(7), zip varchar(13), phone varchar(25), # user's company description description text, category_other text, # localization desired_zip varchar(7), latitudedec(10,7), longitude dec(10,7), # user's personalized options url varchar(200) not null unique, logo_md5varchar(32), linked_url varchar(200), color_bgrd varchar(16), # content moderation (null if nothing, 1 for new, 2 for updates) updates smallint, banned boolean ); Would you keep this as one table or would you split it into multiple tables such as users, company localizations, personalized options and moderation which would hold each the fields under each comment together with a company_id? The first way of doing it is easier to update because I would not have to update all the other linked tables. But the other way of doing seems cleaner. To summarize one table vs. many tables with one to one relations? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic style shema question
At 11:44a -0500 on 18 Jan 2008, Alex K wrote: To summarize one table vs. many tables with one to one relations? As per usual, it depends on your needs. For most flexibility, and to give the DB the best chance to give the best plan for the possible requests I might make in the future, I generally tend towards normalization, not denormalization. The question is: what do you want to do with the schema? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic style shema question
Hi, Split frequently used columns from other non frequently used. Splitting them will improve the disk access. You don't need to separate as too many tables. You need to index all the tables if you split into many. Saravanan --- On Fri, 1/18/08, Alex K [EMAIL PROTECTED] wrote: From: Alex K [EMAIL PROTECTED] Subject: basic style shema question To: mysql@lists.mysql.com Date: Friday, January 18, 2008, 11:14 PM Hi Guys, Let's suppose I have the following table: create table companies ( id int(11) not null auto_increment primary key, # user's login and password email varchar(100), passwordvarchar(100), # basic information company_namevarchar(100), contact_namevarchar(100), street varchar(100), cityvarchar(100), state varchar(7), zip varchar(13), phone varchar(25), # user's company description description text, category_other text, # localization desired_zip varchar(7), latitudedec(10,7), longitude dec(10,7), # user's personalized options url varchar(200) not null unique, logo_md5varchar(32), linked_url varchar(200), color_bgrd varchar(16), # content moderation (null if nothing, 1 for new, 2 for updates) updates smallint, banned boolean ); Would you keep this as one table or would you split it into multiple tables such as users, company localizations, personalized options and moderation which would hold each the fields under each comment together with a company_id? The first way of doing it is easier to update because I would not have to update all the other linked tables. But the other way of doing seems cleaner. To summarize one table vs. many tables with one to one relations? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic style shema question
Hmm. If we're talking pure DB theory, then the whole point is to apply the DRY principle as much as possible. At the point you have multiple copies of the same data, unless your programmers are perfect (and they aren't, I promise), you *will* have stale data. Better to have only one place to update things. Some other questions/thoughts that come to mind: - Will it ever be possible for a company to have more than one contact email address? - Do you /really/ want to store the user's password in your DB? Look towards salts and SHA/MD5 sums. - If you have more than a few people with the same company, are you at all worried about disk space? - Disk contention is often the bottleneck, if your DB can't fit entirely in main memory. Having less places to update means better disk access. - Usage patterns, as you described, are also a concern. Logging in is an issue as is accessing other data. What's the ratio of currently-logging-in-users to other data requests? No need to pull the entire data row if you're not going to use most of the disk pages. - Will you have mainly INSERT queries, mainly SELECT, UPDATE, DELETE, a combination? DB's and engines are better suited to different types of workloads. - Are you worried about integrity of your data? How many foreign key constraints will you want? These all tie in together, and generally beg the question of *your* usage patterns. If this is a project of any import, I can almost guarantee that what you think will happen will not align with what actually happens. With that in mind, having the agility of multiple tables with correct data (read: use foreign key constraints) will likely behoove you. You might want to take a look at some articles on normalization and schema design. Wikipedia is a good starting pace. Kevin At 12:30p -0500 on 18 Jan 2008, Alex K wrote: Well the basic information, company description and personalized options will be selected many times (whenever a user submits a query). It will basically be show on the result page of the search engine. The user's login / password well is used to login, then the user may update the company basic information, description and personalized options. These updates may happen sporadically though. Once every 3 minutes these fields are selected again in order to update the search engine index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic style shema question
Hi Kevin, Well the basic information, company description and personalized options will be selected many times (whenever a user submits a query). It will basically be show on the result page of the search engine. The user's login / password well is used to login, then the user may update the company basic information, description and personalized options. These updates may happen sporadically though. Once every 3 minutes these fields are selected again in order to update the search engine index. Thank you, Alex On 18/01/2008, Kevin Hunter [EMAIL PROTECTED] wrote: At 11:44a -0500 on 18 Jan 2008, Alex K wrote: To summarize one table vs. many tables with one to one relations? As per usual, it depends on your needs. For most flexibility, and to give the DB the best chance to give the best plan for the possible requests I might make in the future, I generally tend towards normalization, not denormalization. The question is: what do you want to do with the schema? Kevin -- 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]
