多对多链接表的替代方案

Question

I am working on a project where I want to allow the end user to basically add an unlimited amount of resources when creating a hardware device listing.

In this scenario, they can store both the quantity and types of hard-drives. The hard-drive types are already stored in a MySQL Database table with all of the potential options, so they have the options to set quantity, choose the drive type (from dropdown box), and add more entries as needed.

As I don't want to create a DB with "drive1amount", "drive1typeid", "drive2amount", "drive2typeid", and so on, what would be the best way to do this?

I've seen similar questions answered with a many-to-many link table, but can't think of how I could pull this off with that.

Answer 1

Something like this?

CREATE TABLE `hardware` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(256) NOT NULL,
 `quantity` int(11) NOT NULL,
 `hardware_type_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `type_id` (`hardware_type_id`),
 CONSTRAINT `hardware_ibfk_1` FOREIGN KEY (`hardware_type_id`) REFERENCES `hardware_type` (`id`)
) ENGINE=InnoDB 

hardware_type_id is a foreign key to your existing table

This way the table doesnt care what kind of hardware it is