Advice needed: Create a junction table, or a better way?
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