SQL relationship issues

Question

I'm creating an app that will search a table called tags for events tagged with those tags. There will be two tables: tags and events, and when a user searches for something, the app searches the tags table and then displays information for the relevant information from the events table.

This is the flow of how the application will search and display data:

User searches -> 
SQL search on tags table -> 
using tag_ids found, search for relevant events on events table -> 
display event data

Am I thinking about this the correct way? I have a feeling I might not be. I don't want to store each of the tag_ids in columns named tag_id_1, tag_id_2, etc. on the events table, and I don't want to limit the number of tags that a user can have attached to an event. However, I don't want to rely on my application to do major processing of the SQL, so I don't want to store the tags as a JSON object in the table either. Is this possible, or will I have to change how I search?

Answer 1

You have here an N:N relationship. There are multiple types of tags that can be added to multiple different events. You need to create another table to link those together: a tags_per_event table, for example.

In the tags table you store only information about the tags themselves (id, description..). In the events table you only describe events (id, description, source...). Finally, in the tags_per_event table you have one row for each tag for each event. For each row, you will have the ID of the tag and the ID of the event. When you lookup the information, you need to join the 3 table in order to get all the information about which tags were assigned to which event and what is the information you have on those (adding descriptions and such).

Answer 2

IMHO you need one more table. You already have TAGS and EVENTS, but TAGS shouldn't be included in the EVENTS table, so create a TAGS_EVENTS table, with one row for every TAG_ID and EVENT_ID.

• TAGS TABLE

  ID | NAME 
  1  | sql 
  2  | oracle
  

• EVENTS TABLE

  ID | TYPE 
  1  | Question asked 
  2  | Question asked
  

• TAGS_EVENTS TABLE

  EVENT_ID | TAG_ID 
  1        | 1 
  1        | 2
  2        | 2
  

EXPLANATION:

• EVENT 1: Question asked with tags "sql" and "oracle"
• EVENT 2: Question asked with tag "oracle"