Web lists-archives.com

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.brawley@xxxxxxxxxxxxx>:

> > 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" <gatorreina@xxxxxxxxx>
> Reply-To: "Richard Reina" <gatorreina@xxxxxxxxx>
> Date: 07/29/15 10:19 AM
> To: "mysql@xxxxxxxxxxxxxxx" <mysql@xxxxxxxxxxxxxxx>
> 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 ----
>