CODEWITHSUNDEEP
entity-frameworkasp.net-core.net-coreentity-framework-coreef-core-3.1
Neo
Neo

Reputation: 163

How to have intermediate table for a 1 to many relationship?

I have a model class:

public class UserProfile
{
        public string UserID { get; set; }
        public string Name{ get; set; }
        public ICollection<AddressMaster> AddressMaster { get; set; }
}

The above class have a 1 to many relationship with AddressMaster model class given below:

public class AddressMaster
{
        public string AddrID{ get; set; }
        public string AddressLine1{ get; set; }
        public UserProfile UserProfile { get; set; }
        public TheatreLocation TheatreLocation { get; set; }
}

The problem is, there is one other model also that has a 1 to many relationship with addressmaster, which is:

public class TheatreLocation
{
        public string LocationID { get; set; }
        public string Name{ get; set; }
        public ICollection<AddressMaster> AddressMaster { get; set; }
}

So instead of having foreign key at the addressmaster, how can we have a intermediate table between addressmaster and the userprofile & another such table b/w addressmaster and theatre?

Or am i getting the whole concept wrong?

Thanks.

Upvotes: 2

Views: 2265

Answers (2)

Rena
Rena

Reputation: 36705

So instead of having foreign key at the addressmaster, how can we have a intermediate table between addressmaster and the userprofile & another such table b/w addressmaster and theatre?

If you do not want to set any foreign key and add a intermediate table.Design like below:

public class UserProfile
{
    [Key]
    public string UserID { get; set; }
    public string Name { get; set; }       
}
public class AddressMaster
{
    [Key]
    public string AddrID { get; set; }
    public string AddressLine1 { get; set; }
}
public class UserAddress
{       
    [Key]
    public string AddrID { get; set; }
    public string UserID { get; set; }
}

Add the primary key to the intermediate table UserAddress.The AddrId could only has one value,but the UserID could have many value which is like one-to-many relationship.

Or am i getting the whole concept wrong?

Nothing wrong.Using navigation property like what you did is also good.

Upvotes: 2

Paddy
Paddy

Reputation: 33867

Your table definitions would probably wind up something like this:

UserProfile
    UserId PK

Theather
    TheatreId PK

Address
    AddrID PK
    AddressLine1 

UserAddress
    UserId PK & FK
    AddressId FK

TheatreAddress
    TheatreID PK & FK
    AddressId FK

This is just good normalisation - i.e. you have a generic 'address' table in the database. Several entities may have an address and have either one-many or many-many relationships with addresses, but a specific address only needs to be recorded once.

The PK on the intermediate table only on the UserId (for example) ensures that this is one-many and not many-many.

Upvotes: 2

Related Questions