EF - Multiple Primary Keys Linking Tables

Question

We need a solution to the following problem which makes sense an also follows best practices without making things to complicated.

We have 3 tables which need to be linked, Sector, Location and Company.

ie: A Company "Bobs B&B" is in Sector "Accommodation" which is in Location "New York". For hotel groups we could have a single company in multiple sectors across multiple locations.

So we decided to simply create a "CompanySectorLocation" table which has 3 primary keys linking the 3 tables:

CompanySectorLocation
    CompanyID (PK FK)
    SectorID (PK FK)
    LocationID (PK FK)

The curved ball here is that the Location is not required.

ie: "Protea Hotels" is active in the "Accomodation" sector leaving the location as NULL.

My developer is very much against having a table that allows for a NULL FK. His suggestion is to create another table called CompanySector which will deal with the links between Company and Sector where Location is not defined.

CompanySector
    CompanyID (PK FK)
    SectorID (PK FK)

My issue with this is that we will have to maintain 2 tables with effectively duplicate data in them.

What is the best way to do this?

Well, depending on your DBMS (SqlServer, Oracle, for example), you just can't have a composite key with a null value in any part of the composite key. (By the way, a table cannot have 3 PKs. I think you mean one composite PK based on 3 columns) — Raphaël Althaus, Jun 11 '12 at 14:41
I'm not entirely sure why you need the 'linking' table in this case... — Patryk Ćwiek, Jun 11 '12 at 15:00
You cannot have multiple primary keys - do you mean a **compound** primary key (made up from multiple columns)?` — marc_s, Jun 11 '12 at 15:27
Apologies, i did mean a composite key with 3 parts. My developer wants the linking table to prevent the need for `DISTINCT` or `GROUP BY` clauses which may over complicate future search queries. This is just a small part of the database. — Talon, Jun 12 '12 at 06:54

score 0 · Accepted Answer · answered Jun 11 '12 at 16:53

I would create a separate table. I have nothing against NULL FK's (many people do), but having one table means you're going to have duplicate data anyway. E.g. if Bob's B&B opens up a branch in California, you'll have duplicate entries in the table specifying the relationship between 'Bob's B&B' and 'Accommodation'. Having a single table also means you'll have to include DISTINCT in any join on this table only involving the CompanyID and SectorID columns (which seems likely if LocationID is nullable).

Effectively you will have two many-to-many relationships:

(Company) <-> (Sector)

and

(Company/Sector) <-> (Location)

It makes sense to use two linking tables to model two different relationships. If you need all 3 entries in one table (for convenience), you can always create an indexed view.

We gonna go on this one. It makes sense. The only problem is that EF (Code First) creates your joining tables for you. So the `CompanySector` table would only be created at run time which made referencing it from `Locations` difficult. But simply fixed, we created the `CompanySector` table manually. If we want the `Sectors` that a `Company` runs in we simply use `Company.CompanySector.Sectors`. Thanks for your help! — Talon, Jun 12 '12 at 07:44

EF - Multiple Primary Keys Linking Tables

1 Answers1