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
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
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 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]
