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 : > > 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" > Reply-To: "Richard Reina" > Date: 07/29/15 10:19 AM > To: "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')
Re: table design question
> From: Richard Reina > > 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
Re: table design question
Thank you very much for all the insightful advice. I will keep the separated. 2011/9/19 Jerry Schwartz > >-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 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 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
>-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
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
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
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]
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
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: 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: 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
-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
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]
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]
