Would this model work for you?
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:
