Correct way to change user table relationship?
Question by user1357684
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 by Starx
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
eventstable and storeRef_IDinstead ofCreator
In such way, you can fetch all the creators of an events in the normalized way.
