1

I am new to databases and I needed help with something. I have one table in my database which contains information about all the events. So for example, there are rows, each one describing a different event.

Now, I also need to keep track of users attending events. So how would I design this? I was thinking that if there was some way, to have a new table specifically for that one event created every time a new row is added to the events table. Is that possible to do? Or would it be a good idea to just add a column in the main events table, with names of people attending delimited by a comma or a semicolon or something? Or is it better to make a table for each user, with each row being an event being attended?

Thanks in advance.

Kartik Prabhu
  • 411
  • 1
  • 4
  • 16

3 Answers3

3

A better way of doing this would be to have something like the following:

Create table events( 
   id integer,
   <OTHER EVENT DETAILS HERE (i.e. start time, location, duration etc>
   primary key (event_id)
);

Create table users(
   id integer,
   <OTHER USER DETAILS HERE (i.e. name, email, phone etc)>
   primary key (user_id)
);

CREATE TABLE event_users(
   event_id integer,
   user_id integer,
   <OTHER USER/EVENT DETAILS HERE (i.e. ticket price paid etc)>
   Primary Key (event_id,user_id), 
   Foreign Key (event_id) REFERENCES events(id),
   Foreign Key (user_id) REFERENCES users(id)
);

This way events can have 0 or many users attending and users can attend 0 or many events and you dont need to create more tables.

The way you would then get the data would be something like:

SELECT U.id
FROM User U, UserEvent UE
WHERE U.id = UE.user_id
AND UE.event_id = <event id you want to search for>;
Navik Hiralal
  • 757
  • 1
  • 6
  • 17
  • Sorry for asking this so late, but how would you add rows to the event_users table? Can I just do "INSERT INTO event_users VALUES(123, 123)"? Or is it something different? – Kartik Prabhu Jan 29 '15 at 03:54
  • 1
    That is correct, it is a standard insert. The only thing to keep in mind is that "event_id" and "user_id" are foreign keys, which means you cannot insert any values for these two fields which does not exist in the "events" and "users" tables respectively. – Navik Hiralal Jan 29 '15 at 04:20
  • Ahhh very nice. So suppose I delete a user. Will he disappear from the event_users table as well? – Kartik Prabhu Jan 29 '15 at 04:26
  • 1
    You would need to set up the tables with cascade on delete for that. These two pages are interesting reads for this: http://stackoverflow.com/questions/59297/when-why-to-use-cascading-in-sql-server and http://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavior – Navik Hiralal Jan 29 '15 at 05:09
1

... would it be a good idea to just add a column in the main events table, with names of people attending delimited by a comma or a semicolon or something?

Hmm you may want to read up on joins first this will help with relations between tables. You would create an events table and and a users table with a relational table to keep track of which users on which event. This way you don't have to have a comma list of users(which does not perform well at all).

I would suggest you have one event table that has a type in a different table, I'm betting that each event will contain the same information so it just makes sense.

Creating tables on the fly is just going to cause confusion and complexity that I don't think you can really justify here.

Community
  • 1
  • 1
dbarnes
  • 1,803
  • 3
  • 17
  • 31
1

It sounds like you are mapping a many-to-many relationship. Each users can attend one or more events, and each event can have 1 or more users. One option would be to have three tables. Table 1: Events, which describes each event. Table 2: Users, which describes each user Table 3: EventUsers, which would be an intermediary table. This table would have at least two columns: The primary key from Events and the primary key from Users.

Jim Evans
  • 31
  • 3