Recommended database structure with relations

Question

I have created an MVC solution where a user can register for an event. This is created with a Many-to-Many relation between a UserRecord and an EventRecord. When a user registers for an event, a relation between that user and event is created. I need to be able to add some credentials to the user when he/she is registered for an event. For example adding experience for the individual event.

The credentials should only be bound to a relation between an event and user. So if User1 registeres for Event1 and Event2, he is able to add a set of credentials to Event1 and another set of credentials to Event2.

How would you recommend a proper database structure where credentials is added as mentioned above?

My UserRecord:

public class UserRecord
{
    [Display(AutoGenerateField = false)]
    public Guid Id { get; set; }
    public string Gender { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public virtual ICollection<EventRecord> Events { get; set; }
}

My EventRecord:

public class EventRecord
{
    public Guid OwnerId { get; set; }
    public string Title { get; set; }

    public virtual ICollection<FighterRecord> Fighters { get; set; }
}

After using migration in Entity Framework, a junction table between the two records above is created in my database called UserRecordEventRecord.

The only correct way to achieve many to many in relational databases is the junction table — MajkeloDev, Nov 24 '15 at 10:21
By migrating the after adding the ICollection in each `record`, the junction table is added in my database. The many-to-many relation between the user and the event is already up and running. I am just not sure how to create the credentials as mentioned in my question. — Casper Nybroe, Nov 24 '15 at 10:25
You're asking about database structure, but your question contains EF models. What exactly do you want? — Dennis, Nov 24 '15 at 10:30
I added the EF models to show my current setup where the relation between the user and the events is created through EF migration. I am asking of how I can add the credentials as mentioned in my question. — Casper Nybroe, Nov 24 '15 at 10:31

score 0 · Answer 1 · answered Nov 24 '15 at 10:35

Ok, so to achieve many to many relationship in relational databases there is actually only one way - junction table.

junction table is a database table that contains common fields from two or more other database tables within the same database

Which means that You need another table to hold ids of both tables which are in many to many relation between each other. See example below:

You have two tables:

UserRecord

Guid Id

nvarchar FirstName

nvarchar LastName

EventRecord

Guid Id

nvarchar Title

Now You need to create third table holding references to both those tables

UserEvent_link

Guid UserId

Guid EventId

// you can also add some id as primary key or make UserId/EventId pair as composite primary key

Thanks to this You are now able to store multiple users against multiple events.

Thank you for your answer! I editted my question after your first comment. The junction table between user and event is already created. I am asking for a solution where credentials can be added to each relation between a user and an event. — Casper Nybroe, Nov 24 '15 at 10:42
just add another column to junction table which will store those credentials — MajkeloDev, Nov 24 '15 at 10:58

Recommended database structure with relations

1 Answers1

Now You need to create third table holding references to both those tables