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

Question

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?

just insert one more row with `device_type = 3` and `components = **any device components id**` — Alexey, Jun 01 '22 at 14:03
Note that tables have _rows_ and _columns_, not records or fields. — jarlh, Jun 01 '22 at 14:05
DONT store a comma delimited list in a database cell if that is where you are going!!! [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — RiggsFolly, Jun 01 '22 at 14:06
@jarlh I still have my college textbook on databases from 2001 that disagrees with you. (Database Processing Fundamentals, 7th edition) — Joel Coehoorn, Jun 01 '22 at 14:07
@JoelCoehoorn, it does? `select ('a', 2) as b from t`, will return a row-type _column_ named b, which consists of 2 _fields_. And a date _column_ value consists of a year _field_, a month _field_ and a day _field_. To see _field_ as a synonym for _column_ easily becomes confusing. — jarlh, Jun 01 '22 at 14:27
Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jun 01 '22 at 23:27
When referring to the table as a table, you may use column and row terminology. When you referring to the table as a database, field and record are the traditional terms. A database _record_ is a _row_ of tabular data because relational databases store data _in tables_. No one _should_ complain about mixing the terms because it's really just a vestige of when you learned about them. — TylerH, Apr 27 '23 at 18:54

Joel Coehoorn · Answer 1 · 2022-06-01T14:33:29.747

3

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.

edited Jun 01 '22 at 14:33

answered Jun 01 '22 at 14:15

Joel Coehoorn

399,467
113
570
794

score 1 · Answer 2 · answered Jun 01 '22 at 15:32

1

Consider a single table:

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

answered Jun 01 '22 at 15:32

Rick James

135,179
13
127
222

score 1 · Answer 3 · edited Apr 27 '23 at 18:55

Table "Device" with columns: ID, name, device_type, components that's not exactly a good idea.

Sometimes a device can belong to several types of devices, e.g. a laptop can work as a notebook and an ultrabook at the same time (if you are creating an online store with PCs). So a better solution is to create child tables like "Device Type" and "Components". Each one should have its PK ID (id) and device_id (did) pointing to the device ID.

Below is a diagram of such a solution:

sqldbd diagram for devices db

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

3 Answers3