How to store multiple key values in single field in SQL database?-CodePudding

I have the following straightforward example with three tables: device types, device components and device, that is composed of data from the previous two tables.

So, if the device Type 3 (Emergency Intercom) is composed of two elements of the device components table, how can I do to specify it in the components column of the device table?

I mean, I know that I need ID 1, 3 and 4 from device components table, but...how do I reference those two values in a column of another table?

I need to do something like this or is possible to define a json column in the table?

CodePudding user response：

You need multiple rows. If an Emergency Intercom is composed of both a button and a speaker, it would look like this:

ID	Device_Type	Components
1	3	4
2	3	5

DO NOT, under any circumstances, give in to the temptation to store the data as a delimited or packaged (json, etc) column.

CodePudding user response：

Consider a single table:

CREATE TABLE devices (
    id ...
    name ...
    device_type  VARCHAR(...)  -- such as "mobile intercom"
    components SET('led','handset','speaker',...)
);