更正用户表关系的正确方法？

Question

I am working on an event system that has two tables, EVENTS and EVENT_CREATORS. I have been linking events to creators by placing creator id in the events table as I thought I would only ever have one event creator.

I now need the ability for more than one creator to edit an event. Should I add simply add additional fields to EVENTS table, i.e ec_id, ec_id_2, ec_id_3, etc. Or does it make more sense to add a cross reference table to the database and a separate table for additional creators?

Answer 1

This is those cases, where it would be wise to use a cross reference table. I will explain it step by step. First

• Create a new table. Call it "event_reference"
• Give the following FIelds: Id, Ref_Id, Creator_ID.

I will omit the need of the EventId, because we are creating a table which is a reference to the event, so event's table will hold the Ref_Id to keep in track of all the references.

• Next, Modify the events table and store Ref_ID instead of Creator

In such way, you can fetch all the creators of an events in the normalized way.