Database Design – Relationship between two or more junctions tables

All we need is an easy explanation of the problem, so here it is.

I am a junior developer and I don’t know how to implement this next relationship.

Note: All entities described here are fictional but have equivalents in an actual production Database. Performance is irrelevant (OK with too many junctions tables) but would be nice to optimize the schema.

Additional Information: Using Entity Framework Code First to generate the tables on SQL Server.

Context

I have an entity named People, another named HomeAddress and finally one named Job.
They have a many-to-many relationship between each other such as People <-> Job and Job <-> HomeAddress. (Junction table have an underscore in their name).

This will need to also be replicated with other tables, including one for PhoneNumber.

Database Design - Relationship between two or more junctions tables

Problem

How do I link People and Address given this situation. The issue is some Jobs have an Address while others don’t. I need a relationship between People and Job and also specify an Address if available (all visual checks will be done on client side).

  • Example 1: Alice has a Job1 without an Address. Data is inserted into Database without specifying an Address.
  • Example 2: Bob has a Job2 with 2 Addresses. Bob chooses 1 Address from the list. Data is inserted into Database specifying 1 Address for Job2.
  • Example 3: Charlie has a Job3 with 4 Addresses. Charlie chooses all 4 of them. Data is inserted specifying all 4 Addresses for Job3.

I don’t know how to store those informations given this schema since Address should be dependant on Job and if Job is removed from People, so should Address too.

Tried Solutions

  • I tried to have a link between Job_People and HomeAddress, but since Job can have that information, this seems redundant.
  • Another solution I can think of is to also have an additional junction table between People and HomeAddress like so People <-> HomeAddress. I just don’t know if that is the right decision.

Again, all entities names are irrelevant, but their relationship is what I am looking for.

Any help is appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Would this model work for you?

Database Design - Relationship between two or more junctions tables

If I’m understanding correctly, people are not associated with jobs or addresses directly. Rather, people are associated with pre-established pairings of jobs and addresses. One twist is that the address is optional, but that’s OK; we can still model this in SQL Server with relational constraints (e.g. NOT NULL, FOREIGN KEY) to maintain integrity.

Think of the Job_Address table as your list of "actual jobs" – instances of some job happening at some address. Because of their many-to-many relationship, though, you need to factor out the jobs names and addresses to the Job and Address tables, respectively – which you did in your original model. I think the solution here is that people are associated with instances of the "actual jobs", not directly with the master lists of job names and addresses.

Code

Here’s some T-SQL statements that build the tables from the preceding diagram, populate them with the three examples from your original post, and display the results.

Create tables

Observe that in our Job_Address table, a job (JobID) is required (NOT NULL) but that an address (AddressID) is not (NULL).

-- Master lists of Jobs, Addresses, and Persons

CREATE TABLE Job (
    ID              INT
        PRIMARY KEY
    ,Name           VARCHAR(64)
);

CREATE TABLE Address (
    ID              INT
        PRIMARY KEY
    ,Name       VARCHAR(64)
);


CREATE TABLE Person (
    ID              INT
        PRIMARY KEY
    ,Name           VARCHAR(64)
);

GO

-- Associations

CREATE TABLE Job_Address (
    ID              INT
        PRIMARY KEY
    ,JobID          INT
        NOT NULL
        FOREIGN KEY REFERENCES Job (ID)
    ,AddressID      INT
        NULL
        FOREIGN KEY REFERENCES Address (ID)
    ,Comments       VARCHAR(64)
    ,CONSTRAINT Job_AddressU1 UNIQUE (
        JobID
        ,AddressID
    )
);

CREATE TABLE Person_Job_Address (
    PersonID        INT
        FOREIGN KEY REFERENCES Person (ID)
    ,Job_AddressID  INT
        FOREIGN KEY REFERENCES Job_Address (ID)
    ,CONSTRAINT Person_Job_Address_PK PRIMARY KEY(
        PersonID
        ,Job_AddressID
    )
)

GO

Insert example data

The three "master lists" contain all of the jobs, addresses, and people you described in your three examples. The "associations" implement your examples.

-- Master lists of Jobs, Addresses, and Persons

INSERT INTO Job VALUES (1, 'Job 1');
INSERT INTO Job VALUES (2, 'Job 2');
INSERT INTO Job VALUES (3, 'Job 3');

INSERT INTO Address VALUES (1, 'Address 1');
INSERT INTO Address VALUES (2, 'Address 2');
INSERT INTO Address VALUES (3, 'Address 3');
INSERT INTO Address VALUES (4, 'Address 4');

INSERT INTO Person VALUES (1, 'Alice');
INSERT INTO Person VALUES (2, 'Bob');
INSERT INTO Person VALUES (3, 'Charlie');


-- Job and Address associations

INSERT INTO Job_Address VALUES (1, 1, NULL, 'Alice example: One job with no address');
INSERT INTO Job_Address VALUES (2, 2, 1, 'Bob example: The job he chose');
INSERT INTO Job_Address VALUES (3, 2, 2, 'Bob example: The job he did not choose');
INSERT INTO Job_Address VALUES (4, 3, 1, 'Charlie example: Job one of four');
INSERT INTO Job_Address VALUES (5, 3, 2, 'Charlie example: Job two of four');
INSERT INTO Job_Address VALUES (6, 3, 3, 'Charlie example: Job three of four');
INSERT INTO Job_Address VALUES (7, 3, 4, 'Charlie example: Job four of four');


-- Person associations with Job/Address pairings

INSERT INTO Person_Job_Address VALUES (1, 1) -- Alice;
INSERT INTO Person_Job_Address VALUES (2, 2) -- Bob;
INSERT INTO Person_Job_Address VALUES (3, 4) -- Charlie;
INSERT INTO Person_Job_Address VALUES (3, 5) -- Charlie;
INSERT INTO Person_Job_Address VALUES (3, 6) -- Charlie;
INSERT INTO Person_Job_Address VALUES (3, 7) -- Charlie;

GO

Results

Finally, here’s a statement that shows the results of the various associations. Note that Alice’s example has no address, and that Bob is only associated with one of the two job/address pairings. Charlie is associated with all four job/address instances.

SELECT
    Job_Address.ID
    ,Job.Name JobName
    ,Address.Name AddressName
    ,Person.Name PersonName
    ,Job_Address.Comments
FROM Job_Address
LEFT JOIN Job ON Job_Address.JobID = Job.ID
LEFT JOIN Address ON Job_Address.AddressID = Address.ID
LEFT JOIN Person_Job_Address ON Job_Address.ID = Person_Job_Address.Job_AddressID
LEFT JOIN Person ON Person_Job_Address.PersonID = Person.ID
;

GO

Here are the query results:

Database Design - Relationship between two or more junctions tables

Method 2

One approach might include having a special HomeAddress instance corresponding to no address in particular. You would then make the Job to HomeAddress relationship mandatory (1..* instead of 0..*) and correlate Jobs that "don’t have an address" with that special HomeAddress.

In that case Job_HomeAddress becomes an entity in its own right (perhaps JobAtAddress) and you link People1 to that entity instead of Job.

Or you can say that it’s Job_People that is an entity, and have an optional relationship of that to Job_HomeAddress to restrict an individual’s Job to specific HomeAddresses. Which, if you think about it, is a more verbose way to implement the former approach.


1 – Bad naming choice; the other entity names are singular.

Method 3

To directly answer your question: you don’t need to implement a relationship between People and HomeAdress. You already implemented that relationship via the Job table and the two joint tables.

Furthermore: you wrote in one of your comments "a data of HomeAddress inside People is dependent on Job having a HomeAddress"

You have to get rid of that "data of HomeAddress" inside the People table. Otherwise it will create problems down the road.

This is about "database normalization". One rule is that: Every column must depend on the primary key and only on the primary key

According to Microsoft: "Eliminate fields that do not depend on the key. Values in a record that are not part of that record’s key do not belong in the table."

Otherwise your choice of tables is perfectly good and sufficient. Just keep in mind normalization.

Also, you wrote "I don’t know how to store those information given this schema since Address should be dependent on Job and if Job is removed from People, so should Address too."

This type of logic is usually implemented via software as opposed to database design. So you shouldn’t worry about these when you design your database. These will be implemented in front-end code, triggers or stored procedures.

Edit:

1)
I understood you want the PKs in both joint tables, to be the combination of the two PK’s of the entity tables the joint table references/links.
Meaning:

a) the PK in Person_JOB to be Person_ID + Job_ID, and

b) the PK in Job_Address to be Job_ID + Adress_ID.

That is the safest way to go!

2)
You wrote "The issue is some Jobs have an Address while others don’t."
No problem with that. You have two options to chose from:

a) You will get NULLs in queries when there is no address, as in the sample query provided by @manniongeo, or

b) You can insert a row in HomeAddress with a text like "No address" as in the answer by @mustaccio, and you will get "No Address" instead of NULL.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply