Array of foreign keys in one column

Question

I have two different types of objects, let’s say event and person. Each person can attend multiple events. I want to have two tables

event
- id      [int (4),primary]
- name    [varchar (30)]
- …

person
- id      [int (4),primary]
- name    [varchar (30)]
- mail    [varchar (60),unique]
- attends [Array of events.id]

Therefore, the field person.attends should be an array of integers of length 4 and I want to tell MySQL that each value in this array is a foreign key from event.id. Is there any way to do this?

Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Don't Panic, Jan 02 '19 at 22:12
An **N:M** relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes. — The Impaler, Jan 02 '19 at 22:15
First problem you're likely to encounter: how to select all people attending a specific event. — Don't Panic, Jan 02 '19 at 22:18
For more kinds of problems, read my old answer to [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/3653574#3653574) — Bill Karwin, Jan 02 '19 at 22:48

score 2 · Accepted Answer · answered Jan 02 '19 at 22:39

2

you need to have bridge table for many-to-many relationships.

thus, add PersonEvent Table to your model

PersonEvent
- EventId      [int (4),primary]
- PersonId     [int (4),primary]

answered Jan 02 '19 at 22:39

Derviş Kayımbaşıoğlu

28,492
4
50
72

score 1 · Answer 2 · answered Jan 02 '19 at 23:11

This is not the proper way to proceed. You need to think in terms of database normalization : you have a relationship between person and event where each person may have many events, and each event can have many persons. To model that relationship, you need a bridge table, like :

CREATE TABLE attendance (
    id INT AUTO_INCREMENT,
    person_id INT NOT NULL,
    event_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY fk_person (person_id) REFERENCES person (id),
    FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);

ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);

The bridge table contains an autoincremented id, foreign keys that reference the 2 other tables, and a unique constraint to prevent duplicates.

Now, say that you want to list the names of all persons that participated a given event :

SELECT p.name
FROM 
    event AS e
    INNER JOIN attendance AS a ON a.event_id = e.id
    INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';

Array of foreign keys in one column

2 Answers2

Linked