Advice needed: Create a junction table, or a better way?

From: ./Rob & <invalid_at_invalid.net>
Date: Fri, 29 Oct 2004 06:32:47 -0400
Message-ID: <qYudnSQcePD7gR_cRVn-pw_at_speakeasy.net>



Hi Gang,

I'm looking for some advice.

I have a table called Estimate that contains information about an estimate given.
I'm having a problem where each Estimate my require 0 or more "SpecialEquipment"s.

(To keep it simple I'll eliminate much of the data.)

  • Problem ==

CREATE TABLE Estimate (
 Estimate_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  Customer_ID INT UNSIGNED NOT NULL,
 INDEX(Customer_ID),
 FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID),  SpecialEquipment_ID INT UNSIGNED NOT NULL,  INDEX (SpecialEquipment_ID),
 FOREIGN KEY (SpecialEquipment_ID) REFERENCES SpecialEquipment (SpecialEquipment_ID)
) TYPE = INNODB; CREATE TABLE SpecialEquipment (
 SpecialEquipment_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  SpecialEquipment VARCHAR(20)
) TYPE = INNODB; mysql> select * from specialequipment;

+---------------------+----------------------+

| SpecialEquipment_ID | SpecialEquipment |
+---------------------+----------------------+
| 1 | Dump Truck |
| 2 | Scaffolding |
| 3 | Roof Jacks |
| 4 | Crane |
+---------------------+----------------------+

Should I create another table Junction table, and have the Estimate table point to it?
E.G.:

  • Possible Fix A ==

CREATE TABLE SpecialEquipmentEstimateJunction (  SpecialEquipmentEstimateJunction_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 SpecialEquipment_ID INT UNSIGNED NOT NULL,  INDEX (SpecialEquipment_ID),
 FOREIGN KEY (SpecialEquipment_ID) REFERENCES SpecialEquipment (SpecialEquipment_ID),
 Estimate_ID INT UNSIGNED NOT NULL,
 INDEX (Estimate_ID),
 FOREIGN KEY (Estimate_ID) REFERENCES Estimate (Estimate_ID) ) TYPE = INNODB; CREATE TABLE Estimate (
 Estimate_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  Customer_ID INT UNSIGNED NOT NULL,
 INDEX(Customer_ID),
 FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID),  SpecialEquipmentEstimateJunction_ID INT UNSIGNED NOT NULL,  INDEX (SpecialEquipmentEstimateJunction_ID),  FOREIGN KEY (SpecialEquipmentEstimateJunction_ID) REFERENCES SpecialEquipment (SpecialEquipment_ID)
) TYPE = INNODB; mysql> select * from SpecialEquipmentEstimateJunction;

+---------------------------------------+---------------------+-------------
-+
| SpecialEquipmentEstimateJunction_ID | SpecialEquipment_ID | Estimate_ID |
+---------------------------------------+---------------------+-------------
-+

| 1 | 1
| 15 |
| 2 | 2
| 15 |
| 3 | 1
| 16 |
| 4 | 4
| 17 |
+---------------------------------------+---------------------+-------------
-+

However, it seems as if I'll be wasting space if an estimate only requires zero or one SpecialEquipment.
Furthermore, if an estimate requires 20 "SpecialEquipment"s, the SpecialEquipmentEstimateJunction can become polluted in a short time.

I thought of doing something like:

  • Possible Fix B ==

CREATE TABLE Estimate (
 Estimate_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  Customer_ID INT UNSIGNED NOT NULL,
 INDEX(Customer_ID),
 FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID),  SpecialEquipment_IDs INT UNSIGNED NOT NULL  /* Drop the fk */
) TYPE = INNODB; Then populate Estimate with
mysql> select * Estimate;

+-------------+-------------+------------------------+

| Estimate_ID | Customer_ID | SpecialEquipment_IDs |
+-------------+-------------+------------------------+

| 15 | 10 | 1,2
|
| 16 | 11 | 1
|
| 17 | 12 | 4
|
| 18 | 14 | 1,2,3,4 |
+-------------+-------------+------------------------+

Then have the programming language split SpecialEquipment_IDs on each comma and ensure data integrity.

Any advice is appreciated.
TIA :-) Received on Fri Oct 29 2004 - 12:32:47 CEST

Original text of this message