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.


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.

enter image description here


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.

  • Have some ideas, but clarifying questions first: 1) Do HomeAddresses exist independently of Jobs? For example, could you have a HomeAddress in your database that's not associated with any Job? 2) Do People have HomeAddresses of their own, or are People only associated with HomeAddresses by virtue of being associated with some Job?
    – manniongeo
    Jul 21, 2022 at 22:45
  • 1) Yes, HomeAddress is its own entity and contains its own data. Other entities reference HomeAddress through a many-to-many relationship with it (Buyer and Seller for example). 2) People CANNOT have HomeAddress WITHOUT Job and a data of HomeAddress inside People is dependant on Job having a HomeAddress. Also, for a data present in People, destroying its link with Job should also remove it from HomeAddress.
    – Codingwiz
    Jul 22, 2022 at 0:58

3 Answers 3


Would this model work for you?

enter image description here

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.


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

    ID              INT
    ,Name           VARCHAR(64)

    ID              INT
    ,Name       VARCHAR(64)

    ID              INT
    ,Name           VARCHAR(64)


-- Associations

CREATE TABLE Job_Address (
    ID              INT
    ,JobID          INT
        NOT NULL
    ,AddressID      INT
    ,Comments       VARCHAR(64)
    ,CONSTRAINT Job_AddressU1 UNIQUE (

CREATE TABLE Person_Job_Address (
    PersonID        INT
    ,Job_AddressID  INT
    ,CONSTRAINT Person_Job_Address_PK PRIMARY KEY(


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;



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.

    ,Job.Name JobName
    ,Address.Name AddressName
    ,Person.Name PersonName
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


Here are the query results:

enter image description here

  • Yes, very detailed and spot on. Thank you very much.
    – Codingwiz
    Jul 22, 2022 at 14:27

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.

  • Yes. I did try the latter, but my junctions tables have their primary keys as a combination of 2 foreign keys from corresponding table. And so it will not allow duplicate data in junction tables. For table Job_People, I can remove the constraint on the PK "JobID" and then only have PK "PeopleID" to avoid duplicate data of People in Job_People. The issue is I will have duplicates data of Job in Job_People which I am not sure about its implication and impact on the rest of my tables.
    – Codingwiz
    Jul 21, 2022 at 22:10

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.