CODEWITHSUNDEEP
phpsqlmultidimensional-array
JoJ
JoJ

Reputation: 133

SQL structure for holding arrays?

How can I relate a table with multiple records from another table?

Basically, I have a table for an 'event', how do I keep track of which 'users' (in their own seperate table) are in a particular event? Right now I just have a column in the 'event' table with a comma separated list of the users IDs who have joined that 'event'.

There must be a better way to do this...right?

Upvotes: 2

Views: 66

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270637

Typically you have a table called users_in_event which holds one row for each user in a many-to-many relationship with the events table. So for each event, you will have a number of table rows mapped individually to users.

CREATE TABLE users_in_event
( 
  user_id INT,
  event_id INT,
  FOREIGN KEY user_id REFERENCES users (user_id) ON UPDATE CASCADE,       
  FOREIGN KEY event_id REFERENCES events (event_id) ON UPDATE CASCADE
  -- Optionally, use ON DELETE CASCADE for the events foreign key
  -- FOREIGN KEY event_id REFERENCES events (event_id) ON UPDATE CASCADE ON DELETE CASCADE
)

To find out which users are in an event, do:

SELECT 
  users_in_event.user_id,
  users.name
FROM users JOIN users_in_event ON users.user_id = users_in_event.user_id
WHERE event_id=1234;

Upvotes: 5

nekno
nekno

Reputation: 19267

If you have a many-to-many relationship, that is, users can attend many events, and events can be attended by many users, that would traditionally be represented with a mapping table with the primary key from each table, plus any attributes specific to the user-event relationship.

For example, if you have ID columns in your USER and EVENT tables that are both type INT:

CREATE TABLE USER_EVENT_MAPPING
(
    USER_ID INT,
    EVENT_ID INT
)

Upvotes: 1

Related Questions