Re: table design question
Hi Peter, Thanks for the reply. So are you saying like this? Repair RID INT, Date DATE, Tech_ID INT, R_TYPE_ID INT 1 2015-07-28 3243 3 2 2015-06-15 1253 1 Repair_details ID, APL_TYPE VARCHAR(35), REPAIR_CODE CHAR(4), DESC 1 Refridgerator C compressor 2 Wash Mach MC Motor Coupler 3 Dish Washer SA Spray Arm 4 Refridgerator DP Drain Pan Not sure what you mean by repeating details.What would the look up table look like? Thanks 2015-07-29 9:38 GMT-05:00 peter.braw...@earthlink.net: one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. The first would stick you with extending the enum column forever, the second would stick you with unmanageable table glut, so you need the traditional relational solution---a parent repairs table to track common repair attributes, a child table to track the details, and lookup tables to track repeating details. PB Original Message From: Richard Reina gatorre...@gmail.com Reply-To: Richard Reina gatorre...@gmail.com Date: 07/29/15 10:19 AM To: mysql@lists.mysql.com mysql@lists.mysql.com Cc: Sub: table design question If I were to create a database table(s) to tract most common repairs to different appliances I can't decide if it would be better to create one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. All the tables would describe the same thing -- a repair -- however the the things being repaired are different in nature which means a great deal of types of repairs that do not relate. Here is an example. repair_wash_mach ID INT, Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator') repair_dish_washer ID INT, Date DATE, Tech_ID INT, Type ENUM( 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve') repair_refridgerator ID INT, Date DATE, Tech_ID INT, Type ENUM( 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') Or since they are all repairs should they be in one table with a REALLY long ENUM table -- that will need to me altered as the number of appliances will most likely increase? ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') End Original Message
Re: table design question
Hi Richard, On 7/29/2015 10:19 AM, Richard Reina wrote: If I were to create a database table(s) to tract most common repairs to different appliances I can't decide if it would be better to create one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. All the tables would describe the same thing -- a repair -- however the the things being repaired are different in nature which means a great deal of types of repairs that do not relate. Here is an example. repair_wash_mach ID INT, Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator') repair_dish_washer ID INT, Date DATE, Tech_ID INT, Type ENUM( 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve') repair_refridgerator ID INT, Date DATE, Tech_ID INT, Type ENUM( 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') Or since they are all repairs should they be in one table with a REALLY long ENUM table -- that will need to me altered as the number of appliances will most likely increase? ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') I would suggest a table of appliances, a table of components, and a table of repairs something like this... repair_tasks( task_id int auto_increment , task_description varchar(25) , appliance_id int not null , component_id int not null ) That way you can have two tasks for the same device. For example, A task of attach door seal would associate the fields (refrigerator, door seal). So would replace door seal. So would order door seal from warehouse. I would not use ENUMS, you would run out of options too quickly. My examples are extremely simplified but hopefully you can see the storage pattern I am suggesting. Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
table design question
If I were to create a database table(s) to tract most common repairs to different appliances I can't decide if it would be better to create one table with a long ENUM column that contains repairs that could be attributed to any appliance or different repair tables for each appliance. All the tables would describe the same thing -- a repair -- however the the things being repaired are different in nature which means a great deal of types of repairs that do not relate. Here is an example. repair_wash_mach ID INT, Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator') repair_dish_washer ID INT, Date DATE, Tech_ID INT, Type ENUM( 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve') repair_refridgerator ID INT, Date DATE, Tech_ID INT, Type ENUM( 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm') Or since they are all repairs should they be in one table with a REALLY long ENUM table -- that will need to me altered as the number of appliances will most likely increase? ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT, Type ENUM( 'leak', 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt', 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor', 'disps_line', 'drain_pan', 'feeler_arm')
suggestion needed for table design and relationship
I have to keep this data in MySql, and i am not sure (as SQL/databse is not my field) how to organise this into one or many tables? right now I would represent my info as follows: device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service |associated_device dev_x | 1234 |1234 |1.2.3.4 |9.8.7.6 |data_specific_to_x |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m... dev_y | 2348 |7734 |10.2.3.4 |99.8.7.6 |data_specific_to_y.|SVC_B,SVC_X... |dev_x,dev_m... dev_z | 3934 |5634 |11.2.3.4 |79.8.7.6 |data_specific_to_z.|SVC_M |dev_n,dev_m... ... pl advice. what would be the best design? data_specific_to_device could be more than one column, as i get to explore the data a bit more. and do i really need a device_id field? which any SQL table normally has. ty.
Re: suggestion needed for table design and relationship
On 2012-08-15 1:54 PM, Rajeev Prasad wrote: I have to keep this data in MySql, and i am not sure (as SQL/databse is not my field) how to organise this into one or many tables? right now I would represent my info as follows: device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service |associated_device dev_x | 1234 |1234 |1.2.3.4|9.8.7.6|data_specific_to_x |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m... dev_y | 2348 |7734 |10.2.3.4 |99.8.7.6 |data_specific_to_y.|SVC_B,SVC_X... |dev_x,dev_m... dev_z | 3934 |5634 |11.2.3.4 |79.8.7.6 |data_specific_to_z.|SVC_M|dev_n,dev_m... ... pl advice. what would be the best design? data_specific_to_device could be more than one column, as i get to explore the data a bit more. and do i really need a device_id field? whi. ch any SQL table normally has. Without a primary key, a table isn't really a table. A surrogate (auto_increment) PK might be simplest. If the associated_service column is a list of values, it needs to be projected to a child table (parentkey, data item ...). From the info posted, I can't tell much about the other fields PB - ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: suggestion needed for table design and relationship
hello Peter, data_specific_to_device = contains diff values, and is different for different devices associated_service = will also be a list of values which will be different for different devices and same for associated_device column. this data rarely(almost never) changes and add happens only say twice a month. So can I not have one big table like below? what disadvantage does it has? sorry I am not into RDBMS, so i want to know prob specific to my data and proposed table layout. I am also not clear about relating more than one tables, if i break this up in more than one table thx in advance. Rajeev From: Peter Brawley peter.braw...@earthlink.net To: Rajeev Prasad rp.ne...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Wednesday, August 15, 2012 4:01 PM Subject: Re: suggestion needed for table design and relationship On 2012-08-15 1:54 PM, Rajeev Prasad wrote: I have to keep this data in MySql, and i am not sure (as SQL/databse is not my field) how to organise this into one or many tables? right now I would represent my info as follows: device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service |associated_device dev_x | 1234 |1234 |1.2.3.4 |9.8.7.6 |data_specific_to_x |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m... dev_y | 2348 |7734 |10.2.3.4 |99.8.7.6 |data_specific_to_y.|SVC_B,SVC_X... |dev_x,dev_m... dev_z | 3934 |5634 |11.2.3.4 |79.8.7.6 |data_specific_to_z.|SVC_M |dev_n,dev_m... ... pl advice. what would be the best design? data_specific_to_device could be more than one column, as i get to explore the data a bit more. and do i really need a device_id field? whi. ch any SQL table normally has. Without a primary key, a table isn't really a table. A surrogate (auto_increment) PK might be simplest. If the associated_service column is a list of values, it needs to be projected to a child table (parentkey, data item ...). From the info posted, I can't tell much about the other fields PB - ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: suggestion needed for table design and relationship
On 2012-08-15 5:27 PM, Rajeev Prasad wrote: hello Peter, data_specific_to_device = contains diff values, and is different for different devices associated_service = will also be a list of values which will be different for different devices and same for associated_device column. this data rarely(almost never) changes and add happens only say twice a month. So can I not have one big table like below? Size is not the issue. A basic table design rule is atomicity--one value per cell. Violating that rule screws up queries. what disadvantage does it has? sorry I am not into RDBMS, Well you are now :-). Rilly you have three choices--read about normalisation enough to do it right, hire someone to do it right, or botch the system. so i want to know prob specific to my data and proposed table layout. I am also not clear about relating more than one tables, if i break this up in more than one table create table parent( deviceID int unsigned primary key auto_increment, devx, ... ) engine=innodb; create table child child( childID int unsigned primary key auto_increment, deviceID int unsigned, foreign key(deviceID) references parent(deviceID) on update cascade on delete cascade, ... ) engine=innodb; PB thx in advance. Rajeev From: Peter Brawley peter.braw...@earthlink.net To: Rajeev Prasad rp.ne...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Wednesday, August 15, 2012 4:01 PM Subject: Re: suggestion needed for table design and relationship On 2012-08-15 1:54 PM, Rajeev Prasad wrote: I have to keep this data in MySql, and i am not sure (as SQL/databse is not my field) how to organise this into one or many tables? right now I would represent my info as follows: device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service |associated_device dev_x | 1234 |1234 |1.2.3.4|9.8.7.6|data_specific_to_x |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m... dev_y | 2348 |7734 |10.2.3.4 |99.8.7.6 |data_specific_to_y.|SVC_B,SVC_X... |dev_x,dev_m... dev_z | 3934 |5634 |11.2.3.4 |79.8.7.6 |data_specific_to_z.|SVC_M|dev_n,dev_m... ... pl advice. what would be the best design? data_specific_to_device could be more than one column, as i get to explore the data a bit more. and do i really need a device_id field? whi. ch any SQL table normally has. Without a primary key, a table isn't really a table. A surrogate (auto_increment) PK might be simplest. If the associated_service column is a list of values, it needs to be projected to a child table (parentkey, data item ...). From the info posted, I can't tell much about the other fields PB - ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table design question
From: Richard Reina gatorre...@gmail.com I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables... To me, the key question is cardinality. You gave a big clue with some states have more than one. This cardinality rule clearly indicates you need a separate table for nick names. I'd look carefully at cardinality, and any field in which you can say, some states may have more than one, put it in a separate table. (One exception to cardinality-driven table design would be if a field is a clearly defined, relatively unchanging set of constants. The classic example is when different states in a process need to be recorded -- membership might include the set applied, paid, accepted, withdrawn. You could have multiple states in a SET field, which would be much less cumbersome than having a fifth-normal-form join table.) A low-energy policy allows for a wide choice of lifestyles and cultures. If, on the other hand, a society opts for high energy consumption, its social relations must be dictated by technocracy and will be equally degrading whether labeled capitalist or socialist. -- Ivan Illich Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
table design question
I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? Thanks, Richard
RE: table design question
-Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Monday, September 19, 2011 9:55 AM To: mysql@lists.mysql.com Subject: table design question I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? [JS] Use separate tables. Unless you have a //very// good reason, you should always try to normalize your data. In other words, use separate tables unless you are positive that you will //always// have 1:1 relationships between the various fields. For example, even such a simple thing as the data of incorporation might have more than one value in the case of the original colonies, the independent republics (Texas, California), and (I'm not sure about these) the Dakotas and West Virginia. Did you know that Maine was once part of Massachusetts? You could put that kind of thing into a trivia record, but that might make it harder to use in the future. My personal philosophy is that it is easier to scramble an egg than to unscramble it. You might someday need to keep track of which states were originally part of other states. And remember, those things that will never happen will happen the day before your vacation. The last thing you want to hear is Richard, before you leave I need you to... (I have 45 years of experience with that.) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: table design question
I would design three tables: Table1 (states): ID, name, abbreviation Table2 (state_item): ID, state_id (from states), item_id (from item_type), item_value (varchar) Table3 (item_type): ID, item_name Into the item_type table you can insert: Nick Name Motto Name origin Facts SomeOtherDataPoint SomeOtherDataPoint2 etc Now, you can have as many nick names per state as needed, some states may have 1, some 50, etc. Same for every other data point you want to keep track of for each state as well. On Mon, Sep 19, 2011 at 8:55 AM, Richard Reina gatorre...@gmail.com wrote: I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? Thanks, Richard -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: table design question
Thank you very much for all the insightful advice. I will keep the separated. 2011/9/19 Jerry Schwartz je...@gii.co.jp -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Monday, September 19, 2011 9:55 AM To: mysql@lists.mysql.com Subject: table design question I want to create a US geography database. So far I have categories such as state nick names (some states have more than one), state mottos (text 25 to 150 characters), state name origins (100-300 characters), state trivial facts, entry into union. My question is; would it be better to keep at least some of this information in separate tables like: state_basic ID | name | Incorporation | Entry in Union| Name_origin | Motto state_nicknames ID | name | nick_name| state_trivia ID | name | fact or would it be batter for queries to try to put all this information in one table? [JS] Use separate tables. Unless you have a //very// good reason, you should always try to normalize your data. In other words, use separate tables unless you are positive that you will //always// have 1:1 relationships between the various fields. For example, even such a simple thing as the data of incorporation might have more than one value in the case of the original colonies, the independent republics (Texas, California), and (I'm not sure about these) the Dakotas and West Virginia. Did you know that Maine was once part of Massachusetts? You could put that kind of thing into a trivia record, but that might make it harder to use in the future. My personal philosophy is that it is easier to scramble an egg than to unscramble it. You might someday need to keep track of which states were originally part of other states. And remember, those things that will never happen will happen the day before your vacation. The last thing you want to hear is Richard, before you leave I need you to... (I have 45 years of experience with that.) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com
Re: Table design help
At 04:23 PM 9/9/2010, Tompkins Neil wrote: Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the financial table for each team to hold the teams current balance,historic financial information in terms of player wages and how best I should link it to the players_bids table, if I should at all ? Other information storage in the financial table would be things like prize money, gate receipts etc Thanks for any help. Cheers Neil Neil, If you are just starting out with database design, you may get some ideas from http://www.databaseanswers.org/data_models/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table design help
Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the financial table for each team to hold the teams current balance,historic financial information in terms of player wages and how best I should link it to the players_bids table, if I should at all ? Other information storage in the financial table would be things like prize money, gate receipts etc Thanks for any help. Cheers Neil
Table design question
Hi, I'm creating a application which hosts football matches and I want to record the player appearances, goals etc. I was thinking of having a record for each player as follows : appearance_id season_id player_id team_id competition_id appearance goals yellow_card red_card date_played Is this the sort of standard layout, recommended for such application. From here, I can then count number of appearances, goals scored etc within a season or for a team_id ? Cheers Neil
roles table design
HI, I'm developing a cms, I need some suggessions regarding database design. I'm creating role table in which role name will be unique, so my question is that should I create roleid(int, autoincreament, primary key )? Same question for users table. Note: I'll have user role mapping table. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) http://safknw.blogspot.com/ Peace is the Ultimate thing we want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Design
Hi, I've the following table design (attached txt file, for some reason the content was being blocked) and I'd like any advice if this is the correct method/design. Thanks for any comments. Neil _ Play and win great prizes with Live Search and Kung Fu Panda http://clk.atdmt.com/UKM/go/101719966/direct/01/ TableName:ProductMaster ProductMasterID FriendlyProductName TableName:ProductContent ProductContentID ProductName Site Language TableName:ProductLookup ProductLookupID ProductContentID TableName:Products ProductsID ProductSupplier Cost Below is my query extracting the data : SELECT ProductName, ProductSupplier FROM Products INNER JOIN ProductLookup ON ProductMaster.ProductMasterID = ProductLookup.ProductLookup INNER JOIN Products ON ProductLookup.ProductID = Products.ProductsID INNER JOIN ProductContent ON Products.ProductsID = ProductContent.ProductContentID WHERE ProductMaster.ProductMasterID = 1 AND ProductContent.Site = mysite.com AND ProductContent.Language = eng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Design
Neil Tompkins wrote: Hi, I've the following table design (attached txt file, for some reason the content was being blocked) and I'd like any advice if this is the correct method/design. TableName:ProductMaster ProductMasterID FriendlyProductName TableName:ProductContent ProductContentID ProductName Site Language TableName:ProductLookup ProductLookupID ProductContentID TableName:Products ProductsID ProductSupplier Cost Below is my query extracting the data : SELECT ProductName, ProductSupplier FROM Products INNER JOIN ProductLookup ON ProductMaster.ProductMasterID = ProductLookup.ProductLookup INNER JOIN Products ON ProductLookup.ProductID = Products.ProductsID INNER JOIN ProductContent ON Products.ProductsID = ProductContent.ProductContentID WHERE ProductMaster.ProductMasterID = 1 AND ProductContent.Site = mysite.com AND ProductContent.Language = eng Neil, when making a query like this to the list, it would be helpful if you explained the entities the various tables are dealing with and the purpose for the query. This saves everyone a lot of time and guesswork. I can spot two or three outright errors in your SQL statement, so clearly you have not actually run it. - You are referencing fields in one table that is not referenced. - You reference another table twice. - There is no field named ProductLookup.ProductLookup Good luck with your homework! - John
Re: Table Design
Hi wultsch, Thanks a lot. Every thing is going fine. I am only concerned with duplicate index, as it is using disk space. Is there any solution so that i can ignore duplicate index by altering the table design. OR i have to end up with duplicate index. Thanks, Krishna Chandra Prajapati On Thu, Apr 24, 2008 at 9:27 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ Please suggest me the correct table design. Thanks in advance. Thanks, -- Krishna Chandra Prajapati Hi Krishna, I have run into similar issues in the past and have ended up having duplicative indexes. The multi column indexes have higher cardinality and although it should not be an issue, lookup on the first portion of the index alone is not as efficient. I would love to know why this is/what I am dong wrong. Are you having issues with INSERT speed, or the size of the your indexes? Posting your explain (extended) and show index may be helpful. For whatever it is worth, I always suggest explicit joins and using AS: SELECT sql_no_cache ac.plan FROMcoupon_per_course AS cpc INNER JOIN affiliate_coupon AS ac USING(coupon_id) WHERE cpc.course_id = 213336 AND ac.coupon_code='TST0G0' I think it makes queries much easier to read and understand. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
Re: Table Design
Krishna Chandra Prajapati schrieb: Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ as you can see above, is the PRIMARY KEY(`coupon_id`,`course_id`) not only larger caused by having two fields indexed, also by having more index entries so it seems not unusual to me that it takes more time to search this index ... -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Design
Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ Please suggest me the correct table design. Thanks in advance. Thanks, -- Krishna Chandra Prajapati
Re: Table Design
On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ Please suggest me the correct table design. Thanks in advance. Thanks, -- Krishna Chandra Prajapati Hi Krishna, I have run into similar issues in the past and have ended up having duplicative indexes. The multi column indexes have higher cardinality and although it should not be an issue, lookup on the first portion of the index alone is not as efficient. I would love to know why this is/what I am dong wrong. Are you having issues with INSERT speed, or the size of the your indexes? Posting your explain (extended) and show index may be helpful. For whatever it is worth, I always suggest explicit joins and using AS: SELECT sql_no_cache ac.plan FROMcoupon_per_course AS cpc INNER JOIN affiliate_coupon AS ac USING(coupon_id) WHERE cpc.course_id = 213336 AND ac.coupon_code='TST0G0' I think it makes queries much easier to read and understand. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design; 2-column index
Hello List, If I have a table: CREATE TABLE t ( id int(11) NOT NULL auto_increment, fk1 mediumint(9) NOT NULL default '0', fk2 smallint(6) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY idxfk1 (fk1,fk2), UNIQUE KEY idxfk2 (fk2,fk1) ) TYPE=MyISAM; I will about half the time have a query WHERE fk1 IN () and about the other half the time have WHERE fk2 IN () Does it make sense to define the UNIQUE KEYS the way I have? Thanks! -- Wellington -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design; 2-column index
In the last episode (Apr 24), Fan, Wellington said: If I have a table: CREATE TABLE t ( id int(11) NOT NULL auto_increment, fk1 mediumint(9) NOT NULL default '0', fk2 smallint(6) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY idxfk1 (fk1,fk2), UNIQUE KEY idxfk2 (fk2,fk1) ) TYPE=MyISAM; I will about half the time have a query WHERE fk1 IN () and about the other half the time have WHERE fk2 IN () Does it make sense to define the UNIQUE KEYS the way I have? You only need one unique index to enforce uniqueness, so you can safely convert your idxfk2 to a single-column regular index and save a little bit of space. ALTER TABLE t drop key idxfk2, add key idxfk2 (fk2); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table design; 2-column index
Hey Dan, Thanks; I was really trying to ask about the potential performance gain, however. I don't care so much about the UNIQUEness, but the INDEXness. See, I am wondering if I create an 2-column index wiht fk1 as the first component, will that index help me if I am refering fk2 in my query? -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, April 24, 2006 1:40 PM To: Fan, Wellington Cc: mysql@lists.mysql.com Subject: Re: Table design; 2-column index In the last episode (Apr 24), Fan, Wellington said: If I have a table: CREATE TABLE t ( id int(11) NOT NULL auto_increment, fk1 mediumint(9) NOT NULL default '0', fk2 smallint(6) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY idxfk1 (fk1,fk2), UNIQUE KEY idxfk2 (fk2,fk1) ) TYPE=MyISAM; I will about half the time have a query WHERE fk1 IN () and about the other half the time have WHERE fk2 IN () Does it make sense to define the UNIQUE KEYS the way I have? You only need one unique index to enforce uniqueness, so you can safely convert your idxfk2 to a single-column regular index and save a little bit of space. ALTER TABLE t drop key idxfk2, add key idxfk2 (fk2); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design; 2-column index
In the last episode (Apr 24), Fan, Wellington said: Thanks; I was really trying to ask about the potential performance gain, however. I don't care so much about the UNIQUEness, but the INDEXness. See, I am wondering if I create an 2-column index wiht fk1 as the first component, will that index help me if I am refering fk2 in my query? You mean like SELECT fk2 FROM t WHERE fk1 IN (1,2,3,4)? Yes. You can verify this by looking at the EXPLAIN plan for the query. If it says Using index in the Extra column, it means all the fields mysql needs is in the index and it won't have to fetch row data. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design question
Hi, A very simple question. I have two products at our website and i would like to keep track of how many of each softwares were downloaded daily. I am planning to create the following table: id - auto_incr date_of_download - data product_name - enum value containing the two products Then I plan to use: select date_of_download, product_name, count(*) from table table group by 1,2 Any other faster method or design? Karam __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Product Table Design Question
Hi All, I'm not sure the best design approach for a product table for a number of different hardware devices. Some devices have IP, Port, CPU, Memory specs, whilst some don't. Current desing is below. Products: product_id product_name maker_id controller_id product_type_id product_model_number product_serial_number product_age condition_id product_price product_sold_price product_sold product_auctioned product_qty product_last_updated product_data_output product_desc ProductTypes: product_type_id product_type_detail Makers: maker_id maker_detail Some products we'll have are, switches/routers/dedicated servers/firewalls etc. Should I just make a Specs table, or, specific table for each type of product.? Appreciate any thoughts on this. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Product Table Design Question
Mark Sargent [EMAIL PROTECTED] wrote on 06/03/2005 03:04:23 AM: Hi All, I'm not sure the best design approach for a product table for a number of different hardware devices. Some devices have IP, Port, CPU, Memory specs, whilst some don't. Current desing is below. Products: product_id product_name maker_id controller_id product_type_id product_model_number product_serial_number product_age condition_id product_price product_sold_price product_sold product_auctioned product_qty product_last_updated product_data_output product_desc ProductTypes: product_type_id product_type_detail Makers: maker_id maker_detail Some products we'll have are, switches/routers/dedicated servers/firewalls etc. Should I just make a Specs table, or, specific table for each type of product.? Appreciate any thoughts on this. Cheers. Mark Sargent. In my opinion, you have identified the core descriptors for any product already (your Products table). Now what you need to be able to provide is a list of descriptors and their values: (# of ports, 16), (# of cpus,2), (# expansion bays,6), (expansion option 1, TELCO-card), etc That kind of flexible design (where you are not limited to the number of extra items you can tack onto a Product) fits best into it's own, vertically organized table (ProductFeatures, below) CREATE TABLE Features ( feature_id int auto_increment primary key , feature varchar(75) not null , UNIQUE (feature) ) ENGINE=InnoDB; CREATE TABLE ProductFeatures ( prodfeat_id int auto_increment primary key , product_id int not null , feature_id int not null , value varchar(255) , UNIQUE(product_id, feature_id) , KEY(feature_id) , FOREIGN KEY (product_ID) References Products(product_id) , FOREIGN KEY (feature_ID) References Features(feature_id) ) ENGINE=InnoDB; Just to keep your data (and your GUI design) more manageable, I normalized the list of possible features into their own table. That way you don't have someone typing in # of ports for one product and port count for another. The UNIQUE index on the ProductFeatures should help to prevent having more than 1 of the same feature described for the same product. The FK constraints prevents you from adding non-existent features to a product that may or may not exist. Just my 2 cents... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query performance...two table design options
James Tu wrote: Hi: Let's say I want to store the following information. Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) In general 'age' is a bad column, because you need to know what year the data was entered to calculate the current age. It is often better to store year of birth or date of birth. This may not be relevant to your application, I just wanted to mention it. Date - DATETIME Activity - VARCHAR(100) Data - TEXT I would be basing my queries on all columns _except_ the Data column. I.e. I would be using WHERE's with all except the Data column. You are not telling us how much data you are planning to maintain. How big will the Data column be, on average, and how many rows/persons are we talking about? Hundreds, thousands or millions? My question is...which design would perform better? (Design A) Put all in one table...index all the columns that I will use WHERE with. -TABLE_ALL- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT Indices - Unique ID, First Name, Last Name, Age, Date, Activity You will probably not need to index all columns. If you have few rows, you don't need indexes at all, except for the primary key on the unique ID. A primary key automatically works as an index. I would probably start with only the primary key, and add indexes only when I find that some queries are too slow. SELECT First_Name, Last_Name, Data FROM TABLE_ALL WHERE Activity = 'draw' AND Age 24; (Design B) Put the Data in its own separate table. -TABLE_A- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data_ID - INT(10) Indices - Unique ID, First Name, Last Name, Age, Date, Activity -TABLE_B- Data_ID - INT(10) Data - TEXT Index - Data_ID This will be faster if your Data column is relatively big (several K on average, I don't know. depends on your HW, of course). I would suggest using the unique ID from TABLE_A as a primary key in TABLE_B, and drop Data_ID from TABLE_A. If there are millions of rows I would normalize these tables to the extreme, something like this: Person: P_Id,Born FName: FN_Id,FirstName LName: LN_Id,LastName FN_P: FN_Id,P_id LN_P: LN_Id,P_id Activity: A_Id,Activity Act_P: A_id,P_Id Data:P_Id,Data FN_P and LN_P are so-called link tables, linking names to persons in a many-to-many relation. Even further normalization would have been achieved with an additional counter column. It would be used in these tables to maintain the order of the names when a person have multiple first names or last names, so that you would have one FName row for each unique name, Mary Jane would be split in Mary and Jane. You could query this schema like this: SELECT FirstName,LastName,Data FROM Person,FName,LName,Data,Activity,FN_P,LN_P,Act_P WHERE Person.P_Id = Data.P_Id AND Person.P_Id = FN_P.P_Id AND Person.P_Id = LN_P.P_Id AND Person.P_Id = Act_P.P_Id AND FName.FN_Id = FN_P.FN_Id AND LName.LN_Id = LN_P.LN_Id AND Activity.A_Id = Act_P.A_Id AND Activity = 'draw' and Born year(now()) - 24 ...or with more explicit formulated joins, like this: SELECT FirstName,LastName,Data FROM Person NATURAL JOIN Act_P NATURAL JOIN Activity INNER JOIN FN_P ON FN_P.P_Id=Person.P_Id NATURAL JOIN FName INNER JOIN LN_P ON LN_P.P_Id=Person.P_Id NATURAL JOIN LName, LEFT JOIN Data ON Data.P_Id = Person.P_Id WHERE Activity = 'draw' and Born year(now()) - 24 The NATURAL JOINS are joins based on columns with the same name in the two joined tables, see the manual. The LEFT JOIN is used in this case because some Persons may not have a corresponding row in the Data table, in this case the Data column of the result table will contain NULL. If you used an INNER join in place of the LEFT join in this case, Persons without a Data record would be omitted from the result. SELECT TABLE_A.First_Name, TABLE_A.Last_Name, TABLE_B.Data FROM TABLE_A, TABLE_B WHERE Activity = 'draw' AND Age 24 AND TABLE_A.Data_ID = TABLE_B.Data_ID; (Aside: Would this query give me the same results as the above query?) Yes, I think so, if all rows in TABLE_A have a corresponding row in TABLE_B. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query performance...two table design options
Hi: Let's say I want to store the following information. Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT I would be basing my queries on all columns _except_ the Data column. I.e. I would be using WHERE's with all except the Data column. My question is...which design would perform better? (Design A) Put all in one table...index all the columns that I will use WHERE with. -TABLE_ALL- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT Indices - Unique ID, First Name, Last Name, Age, Date, Activity SELECT First_Name, Last_Name, Data FROM TABLE_ALL WHERE Activity = 'draw' AND Age 24; (Design B) Put the Data in its own separate table. -TABLE_A- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data_ID - INT(10) Indices - Unique ID, First Name, Last Name, Age, Date, Activity -TABLE_B- Data_ID - INT(10) Data - TEXT Index - Data_ID SELECT TABLE_A.First_Name, TABLE_A.Last_Name, TABLE_B.Data FROM TABLE_A, TABLE_B WHERE Activity = 'draw' AND Age 24 AND TABLE_A.Data_ID = TABLE_B.Data_ID; (Aside: Would this query give me the same results as the above query?) -James
Database load and table design ?
Hello, Currently I am using syslog-sql to store syslog data in a mysql database. The table format is something like: ID hostnamefacilityprioritydatemessage ID is auto incrementing Now I am writting a perl app to calculate stats based on the data in the table which will get run everyday. It is currently doing a loop, here is an example: Select count(facility) from syslog WHERE machine = '$srv' AND facility = '$fac' AND date1 (NOW() - INTERVAL 24 hour) Now $srv is the name of the host and $fac is the facility name. This select statement is in a loop that loops through each server and each facility. When this is running it puts a load on the DB, since there could be about 20 host, each with 6 facilities, which equals about: 600 - The number of times that the select statement would be run I suppose that running the query: select host,facility from WHERE date1 (NOW() - INTERVAL 24 hour) and letting perl do the math on the selected results would be less of a system load ? Is there a better way ? Also I am going to display the results using the following format: hostnamehost A host B ... Facility name facility A X XX facility B X xX facility C X XX Is there an easy way to put that into a mysql table ?? Thanks. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database load and table design ?
In the last episode (Apr 16), Michael Gale said: Currently I am using syslog-sql to store syslog data in a mysql database. The table format is something like: IDhostnamefacilityprioritydatemessage Now I am writting a perl app to calculate stats based on the data in the table which will get run everyday. It is currently doing a loop, here is an example: Select count(facility) from syslog WHERE machine = '$srv' AND facility = '$fac' AND date1 (NOW() - INTERVAL 24 hour) Now $srv is the name of the host and $fac is the facility name. This select statement is in a loop that loops through each server and each facility. When this is running it puts a load on the DB, since there could be about 20 host, each with 6 facilities, which equals about: 600 - The number of times that the select statement would be run I suppose that running the query: select host,facility from WHERE date1 (NOW() - INTERVAL 24 hour) and letting perl do the math on the selected results would be less of a system load ? Why not SELECT host, facility, COUNT(*) FROM syslog WHERE date1 (NOW() - INTERVAL 24 HOUR) GROUP BY host, facility -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with table design
Hello everybody I'm designing a database for our new Application and have some problems with the following: We have a table Jobs in which we store all kind of Jobs. Looks like this: tbl_jobs - job_id, integer, name, varchar, description, varchar easy so far :) The problem is we also want to keep track which of the Jobs are related or almost the same. Example: If we have the following jobs: 1 painter 2 auxiliary worker painter 3 plasterer 4 auxiliary worker plasterer 5 electrician 6 auxiliary worker electrician There will be 2 logical groups: first: 1, 2, 3, 4 second: 5, 6 If I query for plasterer I should get the following result: - plasterer - painter - auxiliary worker painter - auxiliary worker plasterer If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician What is the easiest way to design this? I thought about this the whole morning but couldn't get a solution. I hope somebody on this list can point me in the right direction. Regards, Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with table design
Hi, I'm designing a database for our new Application and have some problems with the following: We have a table Jobs in which we store all kind of Jobs. Looks like this: tbl_jobs - job_id, integer, name, varchar, description, varchar easy so far :) The problem is we also want to keep track which of the Jobs are related or almost the same. Example: If we have the following jobs: 1 painter 2 auxiliary worker painter 3 plasterer 4 auxiliary worker plasterer 5 electrician 6 auxiliary worker electrician There will be 2 logical groups: first: 1, 2, 3, 4 second: 5, 6 If I query for plasterer I should get the following result: - plasterer - painter - auxiliary worker painter - auxiliary worker plasterer If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician What is the easiest way to design this? I thought about this the whole morning but couldn't get a solution. I hope somebody on this list can point me in the right direction. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: problem with table design
On Apr 5, 2005 3:15 PM, Martijn Tonies [EMAIL PROTECTED] wrote: Hi, I'm designing a database for our new Application and have some problems with the following: We have a table Jobs in which we store all kind of Jobs. Looks like this: tbl_jobs - job_id, integer, name, varchar, description, varchar easy so far :) The problem is we also want to keep track which of the Jobs are related or almost the same. Example: If we have the following jobs: 1 painter 2 auxiliary worker painter 3 plasterer 4 auxiliary worker plasterer 5 electrician 6 auxiliary worker electrician There will be 2 logical groups: first: 1, 2, 3, 4 second: 5, 6 If I query for plasterer I should get the following result: - plasterer - painter - auxiliary worker painter - auxiliary worker plasterer If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician What is the easiest way to design this? I thought about this the whole morning but couldn't get a solution. I hope somebody on this list can point me in the right direction. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Thanks for your reply. I see this could be a possible solution. But isn't it possible to solve this problem somehow without having to create an additional job_group table? Regards, Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with table design
I'm designing a database for our new Application and have some problems with the following: We have a table Jobs in which we store all kind of Jobs. Looks like this: tbl_jobs - job_id, integer, name, varchar, description, varchar easy so far :) The problem is we also want to keep track which of the Jobs are related or almost the same. Example: If we have the following jobs: 1 painter 2 auxiliary worker painter 3 plasterer 4 auxiliary worker plasterer 5 electrician 6 auxiliary worker electrician There will be 2 logical groups: first: 1, 2, 3, 4 second: 5, 6 If I query for plasterer I should get the following result: - plasterer - painter - auxiliary worker painter - auxiliary worker plasterer If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician What is the easiest way to design this? I thought about this the whole morning but couldn't get a solution. I hope somebody on this list can point me in the right direction. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. Thanks for your reply. I see this could be a possible solution. But isn't it possible to solve this problem somehow without having to create an additional job_group table? Well, if you do this: If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician How do you expect the database engine to return electrician when you're searching for auxiliary worker electrician. How would it know that the two are related? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with table design
- Original Message -
From: Olivier Salzgeber [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, April 05, 2005 9:01 AM
Subject: problem with table design
Hello everybody
I'm designing a database for our new Application and have some
problems with the following:
We have a table Jobs in which we store all kind of Jobs.
Looks like this:
tbl_jobs
-
job_id, integer,
name, varchar,
description, varchar
easy so far :)
The problem is we also want to keep track which of the Jobs are
related or almost the same.
Example:
If we have the following jobs:
1 painter
2 auxiliary worker painter
3 plasterer
4 auxiliary worker plasterer
5 electrician
6 auxiliary worker electrician
I'm not clear if you intend this to be a second table or if you are putting
the desciption in tbl_jobs; the latter would be a poor choice since there
could be many people whose job is painter and you don't want to store the
fact that job code 1 means 'painter' more than once. Therefore, I'm going to
assume that you have a second table called job_codes that is defined like
this:
drop table if exists job_codes;
create table if not exists job_codes
(job_id integer not null,
job_name varchar(50) not null,
primary key(job_id));
insert into job_codes values
(1, 'painter'),
(2, 'auxiliary worker painter'),
(3, 'plasterer'),
(4, 'auxiliary worker plasterer'),
(5, 'electrician'),
(6, 'auxiliary worker electrician');
I'm also going to assume that the description column in tbl_jobs is no
longer of any importance for this discussion.
There will be 2 logical groups:
first:
1, 2, 3, 4
second:
5, 6
If I query for plasterer I should get the following result:
- plasterer
- painter
- auxiliary worker painter
- auxiliary worker plasterer
If I query for auxiliary worker electrician I should get this:
- electrician
- auxiliary worker electrician
What is the easiest way to design this?
I thought about this the whole morning but couldn't get a solution.
I hope somebody on this list can point me in the right direction.
I don't claim that this is the absolute best solution - someone else may
think of something better - but I would create an additional table something
like this:
create table related_jobs
job_category char(30) not null,
job_id integer not null,
primary key (job_category, job);
and populate it like this:
insert into related_jobs values
('plastering', 1),
('plastering', 2),
('plastering', 3),
('plastering', 4),
('electrical', 5),
('electrical', 6);
If you want to list everyone who has a specific job, like painter, this
query will do it:
select job_name, employee_name
from job_codes c inner join tbl_jobs j on c.job_id = j.job_id
where job_name = 'painter'
order by employee_name;
[Please note that I changed the column name in tbl_jobs from 'name' to
'employee_name' because it is much more descriptive.]
If you want to list the jobs which are related to the plastering group,
this query will do it:
select job_category, job_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
where job_category = 'plastering'
order by job_name;
If you want to list the names of everyone whose job is one of the jobs in
the plastering group, this query will do it:
select job_category, job_name, employee_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
inner join tbl_jobs j on j.job_id = c.job_id
where job_category = 'plastering'
order by job_name, employee_name;
These last two queries are not precisely what you wanted; I am going after
the data already knowing the name of the group to which the job belongs. You
wanted to start with the specific job, such as painter, and then have the
query determine the group and then determine the jobs and/or people
belonging to the group. To accomplish that, you normally use a subquery.
Unfortunately, subqueries are only supported in V4.1 and later of MySQL and
I am only running V4.0. That means I cannot test this query to be sure it
will work. Also, I don't know if you are on V4.1 or later of MySQL so it may
not be very useful to you if I showed you that query. Therefore, I will tell
you a technique that will work for any version of MySQL since it doesn't
involve a subquery: simply break the job up into two queries. The first
query needs to determine the name of the job_category that includes
'plasterer':
select job_category
from related_jobs r inner join job_codes c on r.job_id = c.job_id
where job_name = 'plasterer';
Then, simply plug the result, which is plastering, into this query to get
all of the jobs that belong to the group that includes the specific job
'plasterer':
select job_category, job_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
where job_category = 'plastering'
order by job_name;
Or, to get the people whose job is in the same group as 'plasterer', use
this query:
select job_category, job_name, employee_name
from related_jobs r inner join job_codes c on r.job_id
MySQL table design
Hello, I'll try to explain correctly my idea. I need to develop a on-line sale's website. For that, I have some dough's on how to create the table basket. Client - IDclie (PK) - ... Produts - IDprod (PK) - ... Basket - IDbask (PK) - IDprod (FK) - IDClie (FK) - qt - date Sale - IDsale (PK) - idbask (FK) - there should only be one ID per basket...in this case. - shipping_debit - ... I dont know how to make a propper relation when it comes to (Client - Basket), because I wanted to know how many sales a client made. I was thinking on making an extra table by removing IDclie from Basket and making this: Clie/Basket - IDclie (PK) - IDbask (PK) But I can't really add any values due to foreign key constraints. Any help on how to resolve my problem? How should I solve this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL table design
If you want to know how many sales were made to a give client (e.g. where IDclie = 99), this should see you right: SELECT count(*) FROM Sale, Basket WHERE Sale.idbask = Basket.IDbask AND Basket.IDClie = 99 Paul Vincent DBA University of Central England -Original Message- From: RuiSMonteiro [mailto:[EMAIL PROTECTED] Sent: 27 July 2004 09:44 To: [EMAIL PROTECTED] Subject: MySQL table design Hello, I'll try to explain correctly my idea. I need to develop a on-line sale's website. For that, I have some dough's on how to create the table basket. Client - IDclie (PK) - ... Produts - IDprod (PK) - ... Basket - IDbask (PK) - IDprod (FK) - IDClie (FK) - qt - date Sale - IDsale (PK) - idbask (FK) - there should only be one ID per basket...in this case. - shipping_debit - ... I dont know how to make a propper relation when it comes to (Client - Basket), because I wanted to know how many sales a client made. I was thinking on making an extra table by removing IDclie from Basket and making this: Clie/Basket - IDclie (PK) - IDbask (PK) But I can't really add any values due to foreign key constraints. Any help on how to resolve my problem? How should I solve this? Thanks -- 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]
MyISAM and InnoDB table design
Hi, I read it somewhere that InnoDB is faster for table with high read/write concurrency. I have a table look like this: CREATE TABLE diary ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, member_id INT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, body TEXT NOT NULL, date DATE NOT NULL, time TIME NOT NULL, last_accessed TIMESTAMP PRIMARY KEY (id), INDEX member_id (member_id) ) TYPE=InnoDB; INSERT only occurs when someone writes a new diary, which is not very often. But UPDATE occurs everytime diary is accessed, so it happens often. I am thinking to divide the table into MySQL and InnoDB like this: CREATE TABLE diary ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, member_id INT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, body TEXT NOT NULL, date DATE NOT NULL, time TIME NOT NULL, PRIMARY KEY (id), INDEX member_id (member_id) ) TYPE=MyISAM; CREATE TABLE diary_info ( diary_id INT UNSIGNED NOT NULL, last_accessed TIMESTAMP, PRIMARY KEY(diary_id) ) TYPE=InnoDB; So it is only diary_info that has high read/write concurrency now. But, everytime I have to do a SELECT, I also have to JOIN diary_info. Which one do you think is better? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Carsten, Thanks for the answer (and other thanks go to the other guys that answered me). I think normalization is the way to go. I think it is the right thing to do (in theory). The problem is that theory doesn't fit all. Basically I have some tables with only 2 fields (ID and name), and a central table, joined by a one-to-many relation. The key point here are the 2-field tables. If I keep them separate, I can extend them (add new fields) without problem when need arise. But if there is no need for an extension (my case), all I get is a greater number of tables that I have to take care of. Wouldn't be better (maybe more efficient ?) to put all the 2-field tables in only table, with a separate ENUM field to separate the records on categories ? -- Cip CRD Hi Ciprian, CRD OK, I'm by no means a DB guru, so a) take this with a grain of salt CRD and b) feel free to tear it apart if I'm completely wrong! ;] CRD If in fact your people and city tables aren't going to change very CRD often, then why don't you just go all the way and keep that CRD information somewhere else in your application and write it straight CRD to your travel_expenditures table, e.g.: [..] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Ciprian Trofin writes: Basically I have some tables with only 2 fields (ID and name), and a central table, joined by a one-to-many relation. The key point here are the 2-field tables. If I keep them separate, I can extend them (add new fields) without problem when need arise. But if there is no need for an extension (my case), all I get is a greater number of tables that I have to take care of. Wouldn't be better (maybe more efficient ?) to put all the 2-field tables in only table, with a separate ENUM field to separate the records on categories ? Ciprian, There are two main purposes for normalization in this case. The first is to provide consistency of data. Going back to your example, placing the city name in each record allows the possibility of multiple spellings for the city name, since each record has its own copy of the data. The second is space savings, since storing an int is usually 4 bytes at worst while a city name is definitely more than 4 bytes. Yes it does generate a second table that only has the mappings from cityID to cityName, but you will likely find it well worth the effort to use the mapping. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, Per_Diem) Travel_Exp_Cities (CityID, ExpID) Based on the descriptions I'd tend to go with a normalized table set of this nature: Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, CityID, Exp) This provides consistent use of person and city. Along with gathering related data into the same record. It is doubtful that an expense would reference more than one person or city. Normalizing to this table set provides a simple means of querying related data, without undue duplication of data elements with the possibility of errors creeping in during the data input. But these observations are based upon my own common sense view of the kinds of business rules/processes that are likely to be used. If your business processes would not follow the described mechanisms, say you do indeed share travel_expenses between individuals, or the expense can be across cities, the normalization I've described would not fit. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Hi Ciprian, OK, I'm by no means a DB guru, so a) take this with a grain of salt and b) feel free to tear it apart if I'm completely wrong! ;] If in fact your people and city tables aren't going to change very often, then why don't you just go all the way and keep that information somewhere else in your application and write it straight to your travel_expenditures table, e.g.: travel_expenditures --- | id | date | people| city| per_diem | --- | 1 | 05.08 | John | Glasgow |1.600 | | 2 | 05.09 | Mary | Madrid |2.000 | | 3 | 06.12 | John | Madrid |1.000 | --- This way you completely avoid any JOINs. Of course, this only makes sense if your people and cities information is not likely to change much at all... Thursday, April 8, 2004, 3:29:22 AM, you wrote: CT Hello, CT I have the following structure: CT people CT - CT | id | name | CT - CT | 1 | John | CT | 2 | Mary | CT - CT cities CT CT | id | city| CT CT | 1 | Glasgow | CT | 2 | Madrid | CT | 3 | Berlin | CT CT travel_expenditures CT --- CT | id | date | id_people | id_city | per_diem | CT --- CT | 1 | 05.08 | 1 | 1 |1.600 | CT | 2 | 05.09 | 2 | 3 |2.000 | CT | 3 | 06.12 | 1 | 2 |1.000 | CT --- CT The `people` and `cities` tables aren't going to be very populated, so a CT thought to merge them into something like this: CT central_data CT --- CT | id | name| type | CT --- CT | 1 | John| P| CT | 2 | Glasgow | C| CT | 3 | Mary| P| CT | 4 | Madrid | C| CT | 5 | Berlin | C| CT --- CT where central_data.type is P for people and C for cities. CT Do you think it is a good ideea ? CT -- CT Best regards, CT Ciprian Trofin -- Best regards, Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar, Inc. 8000 Westpark Dr Suite 450 McLean, VA 22102 USA Tel: (703) 556-8436 Fax: (703) 556-8430 Cel: (571) 213-7904 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practice on table design
Hello, I have the following structure: people - | id | name | - | 1 | John | | 2 | Mary | - cities | id | city| | 1 | Glasgow | | 2 | Madrid | | 3 | Berlin | travel_expenditures --- | id | date | id_people | id_city | per_diem | --- | 1 | 05.08 | 1 | 1 |1.600 | | 2 | 05.09 | 2 | 3 |2.000 | | 3 | 06.12 | 1 | 2 |1.000 | --- The `people` and `cities` tables aren't going to be very populated, so a thought to merge them into something like this: central_data --- | id | name| type | --- | 1 | John| P| | 2 | Glasgow | C| | 3 | Mary| P| | 4 | Madrid | C| | 5 | Berlin | C| --- where central_data.type is P for people and C for cities. Do you think it is a good ideea ? -- Best regards, Ciprian Trofin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table design question
I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? Should I use int for ports, which will have an index. Not sure how to store ip_address. This table has the possibility of having 800 millon records. Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
rmck said: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? PostgreSQL ;-) It has a native datatype for storing IP addresses. That means that things like sorting and subnet inclusion tests come included with the database. But switching databases is not something to be undertaken lightly. Should I use int for ports, which will have an index. Not sure how to store ip_address. Is IPv6 an issue? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jochem van Dieten wrote: rmck said: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? PostgreSQL ;-) It has a native datatype for storing IP addresses. That means that things like sorting and subnet inclusion tests come included with the database. But switching databases is not something to be undertaken lightly. Or take a look at the INET_ATON() and INET_NTOA() functions in the MySQL manual at http://www.mysql.com/doc/en/Miscellaneous_functions.html that allow you to convert the IP address to an integer Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAGS2MtvXNTca6JD8RAgkdAKC5rcoyIMJFOaklzT8TaLONQLPsqACfUImB p//aTmYu/i84jWOJ0PqX8Mk= =OODk -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
Mark Matthews wrote: Jochem van Dieten wrote: rmck said: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? PostgreSQL ;-) It has a native datatype for storing IP addresses. That means that things like sorting and subnet inclusion tests come included with the database. But switching databases is not something to be undertaken lightly. Or take a look at the INET_ATON() and INET_NTOA() functions in the MySQL manual at http://www.mysql.com/doc/en/Miscellaneous_functions.html that allow you to convert the IP address to an integer Which is exactly why I asked if IPv6 was an issue. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table design question
rmck wrote: I have ip_address and ports that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion? Should I use int for ports, which will have an index. Not sure how to store ip_address. Use a varchar for the IP address. It allows greater flexibility and will adapt to format changes much more easily. An int for the port number would work well. -- jimoe at sohnen-moe dot com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[5]: Please analyze my project table design
% I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) Well of course, but that begs the question, which normal form? 1, 2, and 3. There's an abundance of explanations on E. F. Codd's normal forms on the web; I just picked the first Google result: Again, which normal form? There are three main normal forms, each with increasing levels of normalization: 2.1 First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field. But he never gave any indication that that's an issue. 2.2 Second Normal Form (2NF): No field values can be derived from another field. For example, if a table already included a birthdate field, it could not also include a birth year field, since this information would be redundant. But he never gave any indication that that's an issue. 2.3 Third Normal Form (3FN): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table. Right, that's *nominally* possible with the tables he gave, but the chance that a particular person is both an agent and a lawyer (or whatever) is probably about zero. So the _true_ redundancy is not very much at all. Which is why I asked: how does it violate normalization rules? And I mean in a practical sense, not in a well, perhaps some single person *could* be both a laywer and an agent sense. And I'm not disagreeing that he shouldn't combine the fields into a single table. It's just not obvious why it follows from normalization considerations. Sorry for being unclear -- I didn't mean to suggest to consider cases when a laywer is also an agent. I might have mixed normalisation issues with other suggestions for good database design, too. What I was to say is, whenever you have the same kind of information for a number of database entities (like lawyer, agent, etc.), it's always a good idea to keep that information in a separate table. Phrased in a more hands-on way: If you store contact information for lawyer, agents, and other groups, that should go to a separate table. Regards, Stefan Hinz -- Are you MySQL certified? http://www.mysql.com/certification/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Stefan Hinz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL Documentation Team /_/ /_/\_, /___/\___\_\___/ Berlin, Germany ___/ www.mysql.com +49 30 8270294-0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please analyze my project table design
Greetings, my hands on school project is a small real estate database.
I am using MySQL 3.23 without InnoDB, but would like to migrate to 4.x
w/InnoDB at some point.
I would greatly appreciate any feedback on this design.
These are the business rules:
1) 1 matter may have several vendors and/or several purchasers
2) 1 matter will have 1 lawyer and 1 agent
Thank you for any advice!
CREATE TABLE other_lawyers (
other_lawyer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
other_lawyer_fname VARCHAR(25) NULL,
other_lawyer_lname VARCHAR(25) NULL,
other_lawyer_email VARCHAR(25) NULL,
other_lawyer_address VARCHAR(50) NULL,
other_lawyer_city VARCHAR(15) NULL,
other_lawyer_provice VARCHAR(15) NULL,
other_lawyer_postal VARCHAR(6) NULL,
other_lawyer_phone VARCHAR(10) NULL,
other_lawyer_fax VARCHAR(10) NULL,
other_lawyer_firm VARCHAR(40) NULL,
PRIMARY KEY(other_lawyer_id)
);
CREATE TABLE agents (
agent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
agent_fname VARCHAR(25) NULL,
agent_lname VARCHAR(25) NULL,
agent_email VARCHAR(25) NULL,
agent_address VARCHAR(50) NULL,
agent_city VARCHAR(15) NULL,
agent_provice VARCHAR(15) NULL,
agent_postal VARCHAR(6) NULL,
agent_phone VARCHAR(10) NULL,
agent_fax VARCHAR(10) NULL,
agent_agency VARCHAR(40) NULL,
PRIMARY KEY(agent_id)
);
CREATE TABLE matters (
file_number SMALLINT UNSIGNED NOT NULL,
agents_agent_id INTEGER UNSIGNED NOT NULL,
other_lawyers_other_lawyer_id INTEGER UNSIGNED NOT NULL,
sale_or_purchase ENUM('s','p') NULL,
property_address VARCHAR(50) NULL,
property_city VARCHAR(15) NULL,
property_province VARCHAR(15) NULL,
price FLOAT(8,2) NULL,
file_open_date DATE NULL,
file_posession_date DATE NULL,
PRIMARY KEY(file_number),
INDEX matters_FKIndex1(other_lawyers_other_lawyer_id),
INDEX matters_FKIndex2(agents_agent_id)
);
CREATE TABLE vendors (
vendor_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
vendor_fname VARCHAR(25) NULL,
vendor_lname VARCHAR(25) NULL,
vendor_email VARCHAR(25) NULL,
vendor_address VARCHAR(50) NULL,
vendor_city VARCHAR(15) NULL,
vendor_provice VARCHAR(15) NULL,
vendor_phone_home VARCHAR(10) NULL,
vendor_phone_work VARCHAR(10) NULL,
vendor_postal VARCHAR(6) NULL,
vendor_fax VARCHAR(10) NULL,
vendor_firm VARCHAR(40) NULL,
PRIMARY KEY(vendor_id),
INDEX vendors_FKIndex1(matters_file_number)
);
CREATE TABLE purchasers (
purchasers_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
purchaser_fname VARCHAR(25) NULL,
purchaser_lname VARCHAR(25) NULL,
purchaser_email VARCHAR(25) NULL,
purchaser_address VARCHAR(50) NULL,
purchaser_city VARCHAR(15) NULL,
purchaser_provice VARCHAR(15) NULL,
purchaser_phone_home VARCHAR(10) NULL,
purchaser_phone_work VARCHAR(10) NULL,
purchaser_postal VARCHAR(6) NULL,
purchaser_fax VARCHAR(10) NULL,
purchaser_firm VARCHAR(40) NULL,
PRIMARY KEY(purchasers_id),
INDEX purchasers_FKIndex1(matters_file_number)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please analyze my project table design
From: Paul Fine [EMAIL PROTECTED]
Date: 2003/11/28 Fri AM 11:14:25 CST
To: [EMAIL PROTECTED]
Subject: Please analyze my project table design
Greetings, my hands on school project is a small real estate database.
I am using MySQL 3.23 without InnoDB, but would like to migrate to 4.x
w/InnoDB at some point.
I would greatly appreciate any feedback on this design.
One quick comment---there appear to be many columns in common between the tables
representing people playing different roles (e.g. names, phone numbers).
Maybe you could create a single person table instead?
I don't know of a DB theory rule that says it's a bad idea to have the same columns in
many tables, but it might make the design more compact to take the common stuff and
put it into one table.
These are the business rules:
1) 1 matter may have several vendors and/or several purchasers
2) 1 matter will have 1 lawyer and 1 agent
Thank you for any advice!
CREATE TABLE other_lawyers (
other_lawyer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
other_lawyer_fname VARCHAR(25) NULL,
other_lawyer_lname VARCHAR(25) NULL,
other_lawyer_email VARCHAR(25) NULL,
other_lawyer_address VARCHAR(50) NULL,
other_lawyer_city VARCHAR(15) NULL,
other_lawyer_provice VARCHAR(15) NULL,
other_lawyer_postal VARCHAR(6) NULL,
other_lawyer_phone VARCHAR(10) NULL,
other_lawyer_fax VARCHAR(10) NULL,
other_lawyer_firm VARCHAR(40) NULL,
PRIMARY KEY(other_lawyer_id)
);
CREATE TABLE agents (
agent_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
agent_fname VARCHAR(25) NULL,
agent_lname VARCHAR(25) NULL,
agent_email VARCHAR(25) NULL,
agent_address VARCHAR(50) NULL,
agent_city VARCHAR(15) NULL,
agent_provice VARCHAR(15) NULL,
agent_postal VARCHAR(6) NULL,
agent_phone VARCHAR(10) NULL,
agent_fax VARCHAR(10) NULL,
agent_agency VARCHAR(40) NULL,
PRIMARY KEY(agent_id)
);
CREATE TABLE matters (
file_number SMALLINT UNSIGNED NOT NULL,
agents_agent_id INTEGER UNSIGNED NOT NULL,
other_lawyers_other_lawyer_id INTEGER UNSIGNED NOT NULL,
sale_or_purchase ENUM('s','p') NULL,
property_address VARCHAR(50) NULL,
property_city VARCHAR(15) NULL,
property_province VARCHAR(15) NULL,
price FLOAT(8,2) NULL,
file_open_date DATE NULL,
file_posession_date DATE NULL,
PRIMARY KEY(file_number),
INDEX matters_FKIndex1(other_lawyers_other_lawyer_id),
INDEX matters_FKIndex2(agents_agent_id)
);
CREATE TABLE vendors (
vendor_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
vendor_fname VARCHAR(25) NULL,
vendor_lname VARCHAR(25) NULL,
vendor_email VARCHAR(25) NULL,
vendor_address VARCHAR(50) NULL,
vendor_city VARCHAR(15) NULL,
vendor_provice VARCHAR(15) NULL,
vendor_phone_home VARCHAR(10) NULL,
vendor_phone_work VARCHAR(10) NULL,
vendor_postal VARCHAR(6) NULL,
vendor_fax VARCHAR(10) NULL,
vendor_firm VARCHAR(40) NULL,
PRIMARY KEY(vendor_id),
INDEX vendors_FKIndex1(matters_file_number)
);
CREATE TABLE purchasers (
purchasers_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
matters_file_number SMALLINT UNSIGNED NOT NULL,
purchaser_fname VARCHAR(25) NULL,
purchaser_lname VARCHAR(25) NULL,
purchaser_email VARCHAR(25) NULL,
purchaser_address VARCHAR(50) NULL,
purchaser_city VARCHAR(15) NULL,
purchaser_provice VARCHAR(15) NULL,
purchaser_phone_home VARCHAR(10) NULL,
purchaser_phone_work VARCHAR(10) NULL,
purchaser_postal VARCHAR(6) NULL,
purchaser_fax VARCHAR(10) NULL,
purchaser_firm VARCHAR(40) NULL,
PRIMARY KEY(purchasers_id),
INDEX purchasers_FKIndex1(matters_file_number)
);
--
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: Please analyze my project table design
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! ...and then [EMAIL PROTECTED] said... % ... % I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) HTH HAND good luck Paul :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/x8LcGb7uCXufRwARAq2vAKCDgl6tAoZyQMxRcuweK4fPFO8flQCfT1QU pJXdxLO02cnospbngiqMqzI= =eKBn -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Please analyze my project table design
From: David T-G [EMAIL PROTECTED] Date: 2003/11/28 Fri PM 03:49:17 CST To: mysql users [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Please analyze my project table design -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! ...and then [EMAIL PROTECTED] said... % ... % I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) Well of course, but that begs the question, which normal form? HTH HAND good luck Paul :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/x8LcGb7uCXufRwARAq2vAKCDgl6tAoZyQMxRcuweK4fPFO8flQCfT1QU pJXdxLO02cnospbngiqMqzI= =eKBn -END PGP SIGNATURE- -- 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: Please analyze my project table design
Thanks guys. I thought that normalization would refer to redundant info not necessarily the same column names? I would further see the problem with the design if say phone_number in the agent table and lawyer table contained the same data, which of course they won't. I thought about the persons table instead however you might notice that there are some columns that each do not have. Ie. home and work numbers for vendors and purchasers, firm for lawyers etc. Also I am expecting several columns to be NULL values for vendors and purchasers but not the other persons. I am very concerned with how I have setup the PK/FK/indices! Thanks guys! The repeated column names for example phone_number -Original Message- From: David T-G [mailto:[EMAIL PROTECTED] Sent: Friday, November 28, 2003 3:49 PM To: mysql users Cc: [EMAIL PROTECTED] Subject: Re: Please analyze my project table design -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! ...and then [EMAIL PROTECTED] said... % ... % I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) HTH HAND good luck Paul :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/x8LcGb7uCXufRwARAq2vAKCDgl6tAoZyQMxRcuweK4fPFO8flQCfT1QU pJXdxLO02cnospbngiqMqzI= =eKBn -END PGP SIGNATURE- -- 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[3]: Please analyze my project table design
% I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) Well of course, but that begs the question, which normal form? 1, 2, and 3. There's an abundance of explanations on E. F. Codd's normal forms on the web; I just picked the first Google result: There are three main normal forms, each with increasing levels of normalization: 2.1 First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field. 2.2 Second Normal Form (2NF): No field values can be derived from another field. For example, if a table already included a birthdate field, it could not also include a birth year field, since this information would be redundant. 2.3 Third Normal Form (3FN): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table. http://www.databaseanswers.com/normal_forms.htm If you're ambitious, you might want to try to abide by the other 330 requirements for relational databases as well, but in most cases, NF 1 through 3 is sufficient: http://www.aisintl.com/case/olais/pb96/biblio.htm#Codd74 Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Please analyze my project table design
Paul, Thanks guys. I thought that normalization would refer to redundant info not necessarily the same column names? I would further see the problem with the design if say phone_number in the agent table and lawyer table contained the same data, which of course they won't. The layout would basically look like this: - table agent - id - name - table lawyer - id - name - table contact - id - street - city - phone All information that is specific for agents only would go to the agent table, and ditto for the lawyer table. All _kinds_ of information both have in common would go to the contact table. In relational databases, things like this are called entities. I thought about the persons table instead however you might notice that there are some columns that each do not have. Ie. home and work numbers for vendors and purchasers, firm for lawyers etc. Also I am expecting several columns to be NULL values for vendors and purchasers but not the other persons. See above; what's specific for a particular group of people can be regarded as an entity, and will therefore go to a specific table, rather than to a lookup table like contact. I am very concerned with how I have setup the PK/FK/indices! Basically, id is what your primary/foreign keys would be. In the above example, agent.id and lawyer.id would both be primary keys, while contact.id would become a foreign key. In your application, and/or through foreign key constraints in MySQL, just make sure you cannot delete an entry that is associated with an id in one of the parent tables (ON DELETE RESTRICT), or that deletions also delete entries in the parent tables, or the other way around (ON DELETE CASCADE). Same applies to UPDATEs: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Also, remember that you cannot use foreign key constraints (in a meaningful way) with MyISAM tables, but that you should use InnoDB tables instead: http://www.mysql.com/doc/en/Using_InnoDB_tables.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[3]: Please analyze my project table design
From: Stefan Hinz [EMAIL PROTECTED] Date: 2003/11/28 Fri PM 04:45:24 CST To: [EMAIL PROTECTED] CC: David T-G [EMAIL PROTECTED], mysql users [EMAIL PROTECTED] Subject: Re[3]: Please analyze my project table design % I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) Well of course, but that begs the question, which normal form? 1, 2, and 3. There's an abundance of explanations on E. F. Codd's normal forms on the web; I just picked the first Google result: Again, which normal form? There are three main normal forms, each with increasing levels of normalization: 2.1 First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field. But he never gave any indication that that's an issue. 2.2 Second Normal Form (2NF): No field values can be derived from another field. For example, if a table already included a birthdate field, it could not also include a birth year field, since this information would be redundant. But he never gave any indication that that's an issue. 2.3 Third Normal Form (3FN): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table. Right, that's *nominally* possible with the tables he gave, but the chance that a particular person is both an agent and a lawyer (or whatever) is probably about zero. So the _true_ redundancy is not very much at all. Which is why I asked: how does it violate normalization rules? And I mean in a practical sense, not in a well, perhaps some single person *could* be both a laywer and an agent sense. And I'm not disagreeing that he shouldn't combine the fields into a single table. It's just not obvious why it follows from normalization considerations. http://www.databaseanswers.com/normal_forms.htm If you're ambitious, you might want to try to abide by the other 330 requirements for relational databases as well, but in most cases, NF 1 through 3 is sufficient: http://www.aisintl.com/case/olais/pb96/biblio.htm#Codd74 Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Please analyze my project table design
From: Paul F [EMAIL PROTECTED] Date: 2003/11/28 Fri PM 04:35:04 CST To: 'mysql users' [EMAIL PROTECTED] Subject: RE: Please analyze my project table design Thanks guys. I thought that normalization would refer to redundant info not necessarily the same column names? I would further see the problem with the design if say phone_number in the agent table and lawyer table contained the same data, which of course they won't. I agree, which is why I continued to disagree/question the claim of the other posters that the issue is normalization. That being said, regardless of whether the design issue is ascribed to the (important) topic of normalization, what you should do is pretty clear. As Stefan Hinz put it: All information that is specific for agents only would go to the agent table, and ditto for the lawyer table. All _kinds_ of information both have in common would go to the contact table. I thought about the persons table instead however you might notice that there are some columns that each do not have. Ie. home and work numbers for vendors and purchasers, firm for lawyers etc. Also I am expecting several columns to be NULL values for vendors and purchasers but not the other persons. Right. But note that the advice of Stefan that I quoted above implicitly addresses these concerns. I am very concerned with how I have setup the PK/FK/indices! Hmm... Well, PK indices are really not all that hard. FK indices are trickier, insofar as not all versions of MySQL have them implemented. But if you get stumped, just write up a proposed set of table designs (in accordance with the principles Stefan listed), and people here (or at USENET comp.database(s).*, if I recall correctly) would be able to help you. Thanks guys! The repeated column names for example phone_number -Original Message- From: David T-G [mailto:[EMAIL PROTECTED] Sent: Friday, November 28, 2003 3:49 PM To: mysql users Cc: [EMAIL PROTECTED] Subject: Re: Please analyze my project table design -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! ...and then [EMAIL PROTECTED] said... % ... % I don't know of a DB theory rule that says it's a bad idea to have the same columns in many tables, but it might make the design more compact to take the common stuff and put it into one table. Oh, there is, indeed. CF normalization :-) HTH HAND good luck Paul :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/x8LcGb7uCXufRwARAq2vAKCDgl6tAoZyQMxRcuweK4fPFO8flQCfT1QU pJXdxLO02cnospbngiqMqzI= =eKBn -END PGP SIGNATURE- -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table design of multi-lingual content
Hi all ,
I want to design a table, say
T_PRODUCT { PRD_ID, NAME, DESCRIPTION, PRICE }
where I want NAME and DESCRIPTION to have multi language inputed.
I searched before where found two solutions :
1)
T_PRODUCT {PRD_ID, PRD_DETAIL_ID, PRICE}
T_PRODUCT_DETAIL { PRD_DEATIL_ID, LANG, NAME, DESCRIPTION }
2)
T_PRODUCT {PRD_ID, NAME_LANG_ID, DESCRIPTION_LANG_ID, PRICE}
T_LANG { LANG_ID, LANG, CONTENT }
But 1) seems to be a very trivial process where I have to have one
additional table for each table for language. But I am afraid 2 will have
too much join if multi-lingual attributes increase.
Is there a better design ? Please advise
Thanks.
Perseus
_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design help
I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table design help
you are going to want a 'buster' table... also known as a many-to-many table so you have: contacts Contact_Key Industries - Industry_Key Contact_Industry_assoc -- Contact_Key Industry_Key -Original Message- From: DeBerry, Casey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:09 PM To: '[EMAIL PROTECTED]' Subject: Table design help I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- 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: Table design help
Casey, I would consider, on the most basic level, a method like this: Contacts (all the company contact info, etc) using primary key contact_id - IndustrialRelationships table - industry_id primary key on Industries table (all the different type of industries) So, in the IndustrialRelationships table, it's simply two columns... the contact_id for the contact, and the industry_id for the industry. Thus, you can put an arbitrary number of industries associated to a single contact. Then, later on, you can use this data to determine how many contacts you have for a given industry. To get the data back out, you could use a JOIN perhaps. This is very simple, but it should get you pointed in the right direction. I might suggest getting a book about or reading some pages regarding data modeling for more information on normalization. -- R. Deuce I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with table design/performance
Hi All, I'm designing a rather large database, and I'm concerned about performance. Was wondering if anyone had any comments/advice? The particular table I'm concerned about will have about 10 million lines, each referencing a physical item I need to track. Each item in the table will have a auto_increment primary key that will be indexed (of course). However, I will also want to search on these items by other fields... their current location, for example (and a few other fields). This table will also be very active-- probably 10-20 inserts/deletions a second, so I am concerned about performance with having lots of fields indexed. So, I thought, instead of indexing all of these fields in the single table, I was considering creating multiple auxillary lookup tables that would only have two fields for each of the 10 million lines. First, the field (indexed) I want to search on (item location, for example), and the second field being the auto_increment primary key for the master table. So, I could search on these other fields (find all items in location A, for example), but the only field indexed in the main table will be the primary key, while the other search points will be easily accessible/searchable from the auxillary lookup tables. The main item table will still store the value, but it will not be indexed. Make sense? I feel like breaking the table apart, and having only a single index per table should help, rather than having a single table with multiple indexes. How have other people handled this sort of thing? TIA. -=-=-=-=-=-=-=- Brad LaJeunesse, PINES System Administrator Georgia Public Library Service www.georgialibraries.org Scotty, I need warp speed in three minutes or we're all dead! --Admiral James T. Kirk, Star Trek II: The Wrath of Khan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design suggestions?
I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. I have a need to create and maintain a list of groups. Then, each group will have a list of members. I will need to be able to search by member name and get a list of what groups the member is in. Also, I will need to maintain the members in each group (add, delete members). I would appreciate any suggestions on how best to model this in MySQL. I've come up with two ideas and I'm not crazy about either. *** * The first idea is simple to create a table with two columns: *** groupNamevarchar, primary key groupMembers text (comma seperated list) - ex: groupOnemember1, member2, member3 groupTwo member2, member4 *** * The second idea is to create two tables. *** table groups groupName varchar primary key groupDescription text table groupRelations groupName groupMember - ex: groups table: groupOneFirst test group groupTwo Second test group groupRelations: groupOnemember1 groupOnemember3 groupTwomember2 groupTwomember3 Like I said before, I'm not very happy with either method. If anyone else has any better ideas they wouldn't mind sharing, I'd really appreciate it! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wednesday 11 June 2003 08:47 pm, JJ wrote: I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wed, 11 Jun 2003 22:17:09 -0230, JJ [EMAIL PROTECTED] wrote: [...] | *** | * The second idea is to create two tables. | *** | table groups | groupName varchar primary key | groupDescription text | | table groupRelations | groupName | groupMember | | - ex: | groups table: | groupOneFirst test group | groupTwo Second test group | | groupRelations: | groupOnemember1 | groupOnemember3 | groupTwomember2 | groupTwomember3 | | Like I said before, I'm not very happy with either method. If anyone else | has any better ideas they wouldn't mind sharing, I'd really appreciate it! This scheme will give you the most flexibility. There is no limit as to how many people can be in a group or how many groups a person can be in. You will want to create a joined unique key on the group_id and member_id. Have Fun! Michael -- Michael Brunson 504.200. [EMAIL PROTECTED]ICQ: 83163789 Data Center Manager -- www.zipa.com --Zipa, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
given this, how would you write a query to list all members that are part of group_id=1 AND group_id=2 AND ... group_id 20. without having to write 20 JOIN relationship r1 on r1.group_id =1 JOIN relationship r20 on r20.group_id =20) wich gets extremely slow with large ammounts of JOINS On Wednesday 11 June 2003 08:47 pm, JJ wrote: I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- 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: Table design suggestions?
Table Group id int auto_increment name char Table Member groupID int auto_increment name char Table Relationship group_id int member_id int i'd set it up like this Table Group id int auto_increment name char Table Member userID int auto_increment name char groupID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Design
Band members should probably be in a separate table. One person may be a member of several bands, and the membership in a band changes over time. This will make modeling difficult. [EMAIL PROTECTED] wrote: Hello, I`m doing a database in MySQL to catalog cds, and i`m not sure if my table structure is the best way to do it: Artist Table Artist_Id int unsigned not null auto_increment primary key Name char(120) // Artist or Band Name Country char(30) // Artist Or Band Country Members char(255) // Band Members Biography text // Artist or Band History CDS Table CD_Id int unsigned not null auto_increment primary key Artist_Id int unsigned not null Title char(150) // CD Title Style char(150) // CD Style Year year(4) // CD Release Year Song Table CD_Id int unsigned not null Position tinyint // Song position on cd Name char ( 255 ) // Song Name length char(10) // Song Length lyric text // Song Lyric Is it the best structure and relationship for my database? Some exemples If i try to find the a artist name i can search for artist name in artist table, but if i want to know what cds a artist got? SELECT * from CDS WHERE Artist_Id = SOMENUMBER but if i don`t know the artist id then i need to do a search for the id first? So is not better use artist name to my primary key? And in same way use CD title as primary key in cd table and in song table? But it's not a waste of space?? I`m a bit new in database so sometimes i get a bit confude about structure and my frontend program will be a java gui to serch and insert things in database so i`m trying to make easy as possible. Thank You. -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Design
Hello, I`m doing a database in MySQL to catalog cds, and i`m not sure if my table structure is the best way to do it: Artist Table Artist_Id int unsigned not null auto_increment primary key Name char(120) // Artist or Band Name Country char(30) // Artist Or Band Country Members char(255) // Band Members Biography text // Artist or Band History CDS Table CD_Id int unsigned not null auto_increment primary key Artist_Id int unsigned not null Title char(150) // CD Title Style char(150) // CD Style Year year(4) // CD Release Year Song Table CD_Id int unsigned not null Position tinyint // Song position on cd Name char ( 255 ) // Song Name length char(10) // Song Length lyric text // Song Lyric Is it the best structure and relationship for my database? Some exemples If i try to find the a artist name i can search for artist name in artist table, but if i want to know what cds a artist got? SELECT * from CDS WHERE Artist_Id = SOMENUMBER but if i don`t know the artist id then i need to do a search for the id first? So is not better use artist name to my primary key? And in same way use CD title as primary key in cd table and in song table? But it's not a waste of space?? I`m a bit new in database so sometimes i get a bit confude about structure and my frontend program will be a java gui to serch and insert things in database so i`m trying to make easy as possible. Thank You. -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Design
First off, this post is going to be long :)
I have designed a database that tracks member earnings on the site. Currently, the
site has been operating for two weeks, has 10,000 members, and the earnings table
already has 750,000 rows. I ran a query to see how many rows were being added each
day, and it is growing exponentially. I.e., 5 days ago, 60,000 rows were added,
yesterday, 100,000 rows were added.
Before I explain why the table is growing so quickly, some background:
Members are paid to read emails, click banners, etc. They are also paid when one of
their direct referrals (a member who signed up under their referral URL) reads an
email or clicks a banner, or when their indirect referrals (a member who signs up
under one of their referrals' referral URL) reads an email or clicks a banner.
Therefore, each time an email is read or a banner is clicked, 3 rows are added to the
earnings table. This table also keeps track of misc earnings, such as signup bonuses,
referral bonuses, etc. I designed it this way because if the site ever wants to add on
(say, pay their members to search the web), the table does not need to be modified at
all.
So, now it's obvious why the table is growing so rapidly. So far, the size of the
table is not a problem. The site is run on a dedicated server, so physical file size
will probably never be a problem (maybe it will?). With 750,000 rows, the current file
size is about 70mb. Also, since the table is indexed properly, queries are running
smoothly, and I also do not think that should ever become a problem. However, data
from this table will never be deleted, so it is just going to continue growing
forever. With 100,000 members to the site instead of 10,000, this table will grow by
millions of rows each day.
My question: Can MySQL handle a table with tens or hundreds of millions of rows?
Here is the design of the table:
CREATE TABLE earnings (
id int(9) unsigned NOT NULL auto_increment,
member_id int(8) unsigned NOT NULL default '0',
action varchar(50) NOT NULL default '',
details varchar(100) default NULL,
amount decimal(7,3) NOT NULL default '0.000',
time timestamp(14) NOT NULL,
paid enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (id),
KEY earnings_member_id(member_id)
) TYPE=MyISAM;
I have played around with some possible reconstruction of this table to see the
results.
1) Removing the details column alltogether cuts the physical size of the table to 70%
of the original. Only about 5% of all the rows in this table use the details field,
but the 5% that do use it absolutely require it. So this really isn't much of an
option, but the 95% of rows that are not using this field are really wasting space. Is
there some other way to accomplish this?
2) Modifying the action field from varchar(35) to int(2). Since there are only about
15 different actions (Banner Clicked, Banner Clicked by Direct Referral, etc), I could
create a new table with all the actions, and then simply store the foreign key in the
earnings table. This also cuts the physical file size down to 70% that of the
original.
However, physical file size (at least I think) is not really the issue here, the issue
is the number of rows in the table, and neither of the above solutions help with this.
The only other solution I have come up with, is the following:
1) Create a table with the following fields:
member_id, # of banners clicked, amount from banners clicked, # of banners clicked by
direct referral, amount from banners clicked by direct referral, etc, etc. This would
require 12 fields just for banners clicked and emails read, and is certainly not a
good way of doing things (I would think anyway). Then, I would have another earnings
table in addition to this one which tracked misc earnings, such as signup bonuses,
referral bonuses, and any other future addons the site may have. That way, I keep the
advantages of what I have now, and I sacrifice little. (What I sacrifice is the
ability to view a list of every single earnings credited to a members account.
Instead, I only have the total # of banners clicked, and the total amount earned from
that...I don't have a list of all banners clicked, etc).
The method explained above would require a *LOT* of recoding, and I certainly don't
wish to spend the time if it's not even the best way of doing things. If anyone has
any ideas as to a good way of resolving this issue, I'd greatly appreciate it! I am
fully willing to pay someone for consulting services if they can present to me a good
solution to this problem!
Filter: SQL, Query
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
THIS IS NOT AN ADVERTISEMENT Table Design
First off, this post is going to be long :)
I have designed a database that tracks member earnings on the site. Currently, the
site has been operating for two weeks, has 10,000 members, and the earnings table
already has 750,000 rows. I ran a query to see how many rows were being added each
day, and it is growing exponentially. I.e., 5 days ago, 60,000 rows were added,
yesterday, 100,000 rows were added.
Before I explain why the table is growing so quickly, some background:
Members are paid to read emails, click banners, etc. They are also paid when one of
their direct referrals (a member who signed up under their referral URL) reads an
email or clicks a banner, or when their indirect referrals (a member who signs up
under one of their referrals' referral URL) reads an email or clicks a banner.
Therefore, each time an email is read or a banner is clicked, 3 rows are added to the
earnings table. This table also keeps track of misc earnings, such as signup bonuses,
referral bonuses, etc. I designed it this way because if the site ever wants to add on
(say, pay their members to search the web), the table does not need to be modified at
all.
So, now it's obvious why the table is growing so rapidly. So far, the size of the
table is not a problem. The site is run on a dedicated server, so physical file size
will probably never be a problem (maybe it will?). With 750,000 rows, the current file
size is about 70mb. Also, since the table is indexed properly, queries are running
smoothly, and I also do not think that should ever become a problem. However, data
from this table will never be deleted, so it is just going to continue growing
forever. With 100,000 members to the site instead of 10,000, this table will grow by
millions of rows each day.
My question: Can MySQL handle a table with tens or hundreds of millions of rows?
Here is the design of the table:
CREATE TABLE earnings (
id int(9) unsigned NOT NULL auto_increment,
member_id int(8) unsigned NOT NULL default '0',
action varchar(50) NOT NULL default '',
details varchar(100) default NULL,
amount decimal(7,3) NOT NULL default '0.000',
time timestamp(14) NOT NULL,
paid enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (id),
KEY earnings_member_id(member_id)
) TYPE=MyISAM;
I have played around with some possible reconstruction of this table to see the
results.
1) Removing the details column alltogether cuts the physical size of the table to 70%
of the original. Only about 5% of all the rows in this table use the details field,
but the 5% that do use it absolutely require it. So this really isn't much of an
option, but the 95% of rows that are not using this field are really wasting space. Is
there some other way to accomplish this?
2) Modifying the action field from varchar(35) to int(2). Since there are only about
15 different actions (Banner Clicked, Banner Clicked by Direct Referral, etc), I could
create a new table with all the actions, and then simply store the foreign key in the
earnings table. This also cuts the physical file size down to 70% that of the
original.
However, physical file size (at least I think) is not really the issue here, the issue
is the number of rows in the table, and neither of the above solutions help with this.
The only other solution I have come up with, is the following:
1) Create a table with the following fields:
member_id, # of banners clicked, amount from banners clicked, # of banners clicked by
direct referral, amount from banners clicked by direct referral, etc, etc. This would
require 12 fields just for banners clicked and emails read, and is certainly not a
good way of doing things (I would think anyway). Then, I would have another earnings
table in addition to this one which tracked misc earnings, such as signup bonuses,
referral bonuses, and any other future addons the site may have. That way, I keep the
advantages of what I have now, and I sacrifice little. (What I sacrifice is the
ability to view a list of every single earnings credited to a members account.
Instead, I only have the total # of banners clicked, and the total amount earned from
that...I don't have a list of all banners clicked, etc).
The method explained above would require a *LOT* of recoding, and I certainly don't
wish to spend the time if it's not even the best way of doing things. If anyone has
any ideas as to a good way of resolving this issue, I'd greatly appreciate it! I am
fully willing to pay someone for consulting services if they can present to me a good
solution to this problem!
Filter: SQL, Query
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
RE: Table Design
First of all, changing action to INT(2) vs INT(11) takes the same space
{# inside ()'s is for display purposes not storage} on disk {I think
it's 4 bytes, but would have to look it up}. TINYINT or maybe even ENUM
{my preference} would only take 1 byte and would serve your purpose.
You could make details a 2nd table joined with the id field in earnings.
This complicates your code somewhat, but does save the space.
The size issue isn't a # of records issue but more of a max file size on
the OS of your server. If your server OS only supports a max file size
of 2GB and you are using MyISAM then you will hit this limit way before
you have to worry about # of rows. There is a max # rows variable in
my.cnf, but it is usually set very large.
You might want to consider using MERGE or UNION if you never delete any
records {See Manual}. This gets around the OS size issue and with
MyISAMPACK and MERGE could make queries against on the whole data set
run much faster.
Gordon Bruce
Interstate Software
A MySQL Training Consulting Partner
-Original Message-
From: Daren [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 23, 2002 4:45 AM
To: [EMAIL PROTECTED]
Subject: Table Design
First off, this post is going to be long :)
I have designed a database that tracks member earnings on the site.
Currently, the site has been operating for two weeks, has 10,000
members,
and the earnings table already has 750,000 rows. I ran a query to see
how
many rows were being added each day, and it is growing exponentially.
I.e., 5 days ago, 60,000 rows were added, yesterday, 100,000 rows were
added.
Before I explain why the table is growing so quickly, some background:
Members are paid to read emails, click banners, etc. They are also
paid
when one of their direct referrals (a member who signed up under their
referral URL) reads an email or clicks a banner, or when their
indirect
referrals (a member who signs up under one of their referrals'
referral
URL) reads an email or clicks a banner. Therefore, each time an email
is
read or a banner is clicked, 3 rows are added to the earnings table.
This
table also keeps track of misc earnings, such as signup bonuses,
referral
bonuses, etc. I designed it this way because if the site ever wants to
add
on (say, pay their members to search the web), the table does not need
to
be modified at all.
So, now it's obvious why the table is growing so rapidly. So far, the
size
of the table is not a problem. The site is run on a dedicated server,
so
physical file size will probably never be a problem (maybe it will?).
With
750,000 rows, the current file size is about 70mb. Also, since the
table
is indexed properly, queries are running smoothly, and I also do not
think
that should ever become a problem. However, data from this table will
never be deleted, so it is just going to continue growing forever.
With
100,000 members to the site instead of 10,000, this table will grow by
millions of rows each day.
My question: Can MySQL handle a table with tens or hundreds of
millions of
rows?
Here is the design of the table:
CREATE TABLE earnings (
id int(9) unsigned NOT NULL auto_increment,
member_id int(8) unsigned NOT NULL default '0',
action varchar(50) NOT NULL default '',
details varchar(100) default NULL,
amount decimal(7,3) NOT NULL default '0.000',
time timestamp(14) NOT NULL,
paid enum('N','Y') NOT NULL default 'N',
PRIMARY KEY (id),
KEY earnings_member_id(member_id)
) TYPE=MyISAM;
I have played around with some possible reconstruction of this table
to
see the results.
1) Removing the details column alltogether cuts the physical size of
the
table to 70% of the original. Only about 5% of all the rows in this
table
use the details field, but the 5% that do use it absolutely require
it. So
this really isn't much of an option, but the 95% of rows that are not
using this field are really wasting space. Is there some other way to
accomplish this?
2) Modifying the action field from varchar(35) to int(2). Since there
are
only about 15 different actions (Banner Clicked, Banner Clicked by
Direct
Referral, etc), I could create a new table with all the actions, and
then
simply store the foreign key in the earnings table. This also cuts the
physical file size down to 70% that of the original.
However, physical file size (at least I think) is not really the issue
here, the issue is the number of rows in the table, and neither of the
above solutions help with this. The only other solution I have come up
with, is the following:
1) Create a table with the following fields:
member_id, # of banners clicked, amount from banners clicked, # of
banners
clicked by direct referral, amount from banners clicked by direct
referral, etc, etc. This would require 12 fields just for banners
clicked
and emails read, and is certainly not a good way of doing things (I
would think anyway). Then, I would have another earnings table
Table Design!!
Dear MySQL User,
I am sending the table schema of 5 tables.Here pid (partner ID) is in the top layer,
aid (Affiliate ID) is in the middle layer and uid (User Id OR Customer ID) is in the
bottem layer.User may promoted to Affiliate and affiliate may promoted to partner.We
have to store there billing and contact info...Could you pls check that design is
perfect?Pls send ur comments?
Regards,
mysql desc tbl_user;
+-+---+--+-+--++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+--++
| uid | smallint(8) | | PRI | NULL | auto_increment |
| aid | smallint(8) | | PRI | 0||
| pid | smallint(8) | | PRI | 0||
| username| varchar(16) | | | ||
| password| varchar(16) | | | ||
| user_status | enum('A','I','S') | YES | | NULL ||
| secret_word | varchar(20) | YES | | NULL ||
| language| varchar(8)| | | ENG ||
| flag_group | double(8,0) | | | 0||
| group_name | varchar(16) | YES | | Customer ||
| db_update | timestamp(14) | YES | | NULL ||
| update_user | smallint(8) | | | 0||
+-+---+--+-+--++
12 rows in set (0.00 sec)
mysql desc tbl_affiliate;
+--+---+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| aid | smallint(8) | | PRI | 0 | |
| pid | smallint(8) | | PRI | 0 | |
| affil_type | enum('1','2','3') | | | 1 | |
| affil_order_date | timestamp(14) | YES | | NULL| |
| affil_status | enum('A','I','S') | YES | | NULL| |
| affil_aux_data | varchar(64) | YES | | NULL| |
| db_update| timestamp(14) | YES | | NULL| |
| update_user | smallint(8) | | | 0 | |
+--+---+--+-+-+---+
8 rows in set (0.00 sec)
mysql desc tbl_partner;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| pid| smallint(8) | | PRI | 0 | |
| partner_type | enum('1','2','3') | | | 1 | |
| partner_order_date | timestamp(14) | YES | | NULL| |
| partner_status | enum('A','I','S') | YES | | NULL| |
| partner_aux_data | varchar(64) | YES | | NULL| |
| db_update | timestamp(14) | YES | | NULL| |
| update_user| smallint(8) | | | 0 | |
++---+--+-+-+---+
7 rows in set (0.00 sec)
mysql desc tbl_user_contact_info;
+-+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-+---+
| uid | smallint(8) | | PRI | 0 | |
| email | varchar(64) | | | | |
| email_alt | varchar(64) | | | | |
| first_name | varchar(12) | | | | |
| last_name | varchar(12) | | | | |
| company | varchar(32) | YES | | NULL| |
| address_1 | varchar(32) | YES | | NULL| |
| address_2 | varchar(32) | YES | | NULL| |
| city| varchar(20) | YES | | NULL| |
| state | varchar(20) | YES | | NULL| |
| zip | varchar(9)| YES | | NULL| |
| country | char(2) | | | | |
| phone | varchar(24) | YES | | NULL| |
| fax | varchar(24) | YES | | NULL| |
| cell_phone | varchar(24) | YES | | NULL| |
| db_update | timestamp(14) | YES | | NULL| |
| update_user | smallint(8) | | | 0 | |
+-+---+--+-+-+---+
17 rows in set (0.00 sec)
mysql desc tbl_user_billing_info;
++---+--+-+-+---+
| Field | Type
Table design (innodb) question
When you have a table with both numeric and variable-length text data, and you need to update the numeric part a lot, it made sense in MyISAM to split the numeric from the textpart. (Because working on fixed-length tables is so much faster.) Say... Original: Table 1: id1 int, id2 int, articletext text MyISAM optimized: Table 1: id1, id2 Table 2: id1, articletext (primary key on id1) Does the same go for InnoDB tables? Or would the original table be just as fast? (Or perhaps faster) sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB table design Q
When you have a table with both numeric and variable-length text data, and you need to update the numeric part a lot, it made sense in MyISAM to split the numeric from the textpart. (Because working on fixed-length tables is so much faster.) Say... Original: Table 1: id1, id2, articletext MyISAM optimized: Table 1: id1, id2 Table 2: id1, articletext (primary key on id1) Does the same go for InnoDB tables? Or would the original table be just as fast? (Or perhaps faster) sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database Table Design Help
I am creating site for greeting cards and need help desiging tables for DB Currently I have three tables.. 1) maincat which has following fields: maincatid (autoincrement) main_name (name of category) 2) subcat which has following fields: subcatid (autoincrement) maincatid sub_name (name of sub category) Here's is my problem..each sub categories can also have sub categories (If main category is Birthday and Sub category is His then His can also have sub categories such as Brother,Uncle,Father etc.) Then I need to associates all these categories in cards table...which holds information about each card. To make it more complicated...each card can be displayed in multiple sub categories as well. How should I design all these? Any help will be greatly appreciated! sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table design and structure
I had two questions about table design and format. I'm designing an application that has two tables. The first table will have -Many selects that return about 20 results on average. -Few inserts -Very few UPDATEs For this table it is not important that the newest inserts show up in selects immediately. So i was thinking of doing selects with insert delays and maybe a table lock for the update Now i understand that MySql does not support row locking, so i was wondering whether to use versioning with a MyIsam table or just use table locks. I'm looking to maximize speed and concurrency. Question1: Please give me a suggestion on the best way to approach this. The second table will have for every request sent by the browser: -Two selects that return data from a unique key ( select...where =unique key#) -one update that also update a unique key# Note that this will happen an a request scope and there maybe the occassional delete but that very insignificant. The site quotes "Concurrent users is not a problem if one doesn't mix updates and selects that needs to examine many rows in the same table. " Now is this true for the default table in MySql (which i believe is MyIsam) or do i need a special table structure. Note timing is important in this table so i cannot do low priority updates because the update has to happen with the selects in the request scope. Also the requests that come in are not necessarily unique ( they would be in most cases but not all) so they could be accessing the same row of the table. Question2: Would table locking be my best bet or will it be optimized with the default table. Please give me a suggestion on the best way to approach this. Thanks Ray - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table design - Second Options
Hello all, Sorry about the slightly off-topic request, but table designs structures are not my strong point as of yet! :) I have a project we are working on, and have a rough draft put together before actually starting. I would greatly appreciate some feedback if improvements, changes or addition are needed. Referral Add Form: http://jyt.com/jyt/rs/add.html and Mock Search Results desired: http://jyt.com/jyt/rs/results.html Search Form: http://jyt.com/jyt/rs/search.html Basically a person fills out a referral form to add a company listing to the database for other to search on. The searches will be performed on Stats or Zipcodes using the business categories selected. All searches are done with the criteria on States or Zip codes and selecting a Business Category Referral Table which stores the info about the person filling out the form and only the refer_comments column will be included in the search result display from the contact and business_location table. All other referral info is for administration purpose only. Tables -- Referral info referral table: refer_id auto_increment primary key refer_fname refer_lname refer_email Business info business table: bus_id auto_increment primary key category_id INDEX busindex1 (category_id) bus_cfname bus_clname bus_name bus_address bus_hours bus_days bus_keywords bus_email bus_url bus_phone bus_comments (all fields will be included in the search results display if not empty(NULL) Business Location info busniess_location table: category_id city state zip INDEX busindex1(category_id) INDEX busindex2 (zip,category_id) INDEX busindex3 (state,category_id) (to store redundant data for all companies as there will be various companies with the same city or state or zip) All search queries are done on city or state by choosing a Business category Business Category category table: category_id PRIMARY KEY NOT NULL pricat seccat addcat (this table will be pre-loaded with all current categories/sub categories) then when a referral is added from the add form the category_id is pulled and entered into the business table. the Pri/Sec/Add columns are used for display purposes only: ArtsMusicRadio for the search display results when the search display is presented depending on the search criteria) Example +++-+-+ category_id primcat seccataddcat +++-+-+ AR Arts NULL NULL ARD Arts Dance NULL ARF Arts Fine Art NULL ARG Arts Galleries NULL ARM Arts MoviesNULL ARMU Arts Music NULL ARMUOArts Music Opera ARMURArts Music Radio Again, appreciate any feedback from the list. Mickalo Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Design -- which is better?
I have a general design question. Is it better to design one table with 45 columns or to split it into 3 tables with 15 columns each. There would be about 5000 rows in the table and it would be used more for reading rather than writing. I'm new to this, is there anything I'm missing? Thanks, Nino - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table Design -- which is better?
Hi, How would the data look a like. And how about the query's Greetz Tbone - Original Message - From: "Nino Skilj" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 24, 2001 12:17 AM Subject: Table Design -- which is better? I have a general design question. Is it better to design one table with 45 columns or to split it into 3 tables with 15 columns each. There would be about 5000 rows in the table and it would be used more for reading rather than writing. I'm new to this, is there anything I'm missing? Thanks, Nino - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table Design -- which is better?
The data would be 1's and 0's (on/off) Nino -Original Message- From: Tbone [mailto:[EMAIL PROTECTED]] Sent: Friday, February 23, 2001 4:18 PM To: [EMAIL PROTECTED] Subject: Re: Table Design -- which is better? Hi, How would the data look a like. And how about the query's Greetz Tbone - Original Message - From: "Nino Skilj" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 24, 2001 12:17 AM Subject: Table Design -- which is better? I have a general design question. Is it better to design one table with 45 columns or to split it into 3 tables with 15 columns each. There would be about 5000 rows in the table and it would be used more for reading rather than writing. I'm new to this, is there anything I'm missing? Thanks, Nino - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
