snippetMinor

How to link Users and Jobs with Addresses in my database structure?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

withdatabasejobsaddressesstructurehowanduserslink

Problem

If I have two entity types in my system: User and Job (a particular Job being an available position of employment somewhere in the country). Both entity types are defined by Addresses.

For example, a User will have a Home Address, a Locality, a Country, a Postcode, etc. A Job will also have this information.
Implementation considerations

Should I use one Addresses table or split it in two tables as, for instance, User_Addresses and Job_Addresses?

-
The idea of a single table is simple, but I don’t know what to think of it when it comes to mixing foreign Ids of two entity types that have nothing in common. I wouldn’t be able to add a foreign key because that column could hold an Id that is either defining a User or a Job.

-
On the other hand, I am not sure about having two tables with the same column types.

Solution

Business rules

We determined via comments that —regarding your business context and within the scope of your question— there are actually three different entity types of relevance, i.e.:

User

Job

Address

We also defined that there are two distinct relationships that involve said entity types, and you provided more details about such connections via your following comments:

…[I]n theory two people living in the same place could use the system.

…[A] User is tied to a single address.

…[A] Job can only be associated with a single address at any given time. But two distinct Job's can have the same address.

Therefore, the significant business rules can be formulated as follows:

An Address houses zero-one-or-many Users

An Address locates zero-one-or-many Jobs

Logical model

So, I have derived a simplified IDEF1X1 logical model that depicts the aforementioned business rules in Figure 1:

In said logical model, you can see that I have represented the three discussed entity types separately, and established (a) the association between Address and User and (b) the conection between Address and Job by means of the migration2 of the Address PRIMARY KEY (PK) to both User and Job, which enforces two different one-to-many (1:M) relationships, as your scenario description demands.

Expository implementation

Consequently, I created an expository DDL structure based on the logical model presented above that can serve as a reference for a concrete implementation:

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient INDEX strategies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions. 

CREATE TABLE Country
(
    CountryCode     CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Country      PRIMARY KEY (CountryCode),
    CONSTRAINT AK_Country_Name UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Region
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Region                      PRIMARY KEY (CountryCode, RegionCode),
    CONSTRAINT AK_Region_CountryCode_and_Name UNIQUE      (CountryCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE Locality
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Locality                                 PRIMARY KEY (CountryCode, RegionCode, LocalityCode),
    CONSTRAINT AK_Locality_CountryCode_RegionCode_and_Name UNIQUE      (CountryCode, RegionCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)

);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    HouseAddressId  INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username          UNIQUE      (Username), -- ALTERNATE KEY.
    CONSTRAINT FK_from_UserProfile_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

CREATE TABLE Job
(
    JobNumber       INT      NOT NULL,
    AddressId       INT      NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Job                   PRIMARY KEY (JobNumber),
    CONSTRAINT FK_from_Job_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)    
);

ACID Transactions

As in any relational database implementation, you should seriously consider using ACID TRANSACTIONS in order to protect the integrity and consistency of the data you are working with.

Making data retrieval easier with the use of a VIEW

If you want to access user and address data points directly from a single resourc

Code Snippets

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient INDEX strategies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions. 

CREATE TABLE Country
(
    CountryCode     CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Country      PRIMARY KEY (CountryCode),
    CONSTRAINT AK_Country_Name UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Region
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Region                      PRIMARY KEY (CountryCode, RegionCode),
    CONSTRAINT AK_Region_CountryCode_and_Name UNIQUE      (CountryCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE Locality
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Locality                                 PRIMARY KEY (CountryCode, RegionCode, LocalityCode),
    CONSTRAINT AK_Locality_CountryCode_RegionCode_and_Name UNIQUE      (CountryCode, RegionCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)

);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    HouseAddressId  INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username          UNIQUE      (Username), -- ALTERNATE KEY.
    CONSTRAINT FK_from_UserProfile_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

CREATE TABLE Job
(
    JobNumber       INT      NOT NULL,
    AddressId       INT      NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Job                   PRIMARY KEY (JobNumbe

CREATE VIEW UserAndAddress AS

    SELECT U.UserId,
           U.FirstName,
           U.LastName,
           U.BirthDate,
           U.GenderCode,
           U.Username,
           A.CountryCode,
           A.RegionCode,
           A.LocalityCode,
           A.Postcode,
           A.Etcetera
      FROM UserProfile U
      JOIN MyAddress   A
        ON U.HouseAddressId = A.AddressId;

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(2)  NOT NULL,
    RegionCode      CHAR(2)  NOT NULL,
    LocalityCode    CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)
);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    AddressId       INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
   (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE UserAddress
(
    AddressId       INT      NOT NULL,
    UserId          INT      NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress              PRIMARY KEY (UserId, AddressId), -- Composite PRIMARY KEY.
    CONSTRAINT FK_UserAddress_to_User    FOREIGN KEY (UserId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserAddress_to_Address FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)    
);

Context

StackExchange Database Administrators Q#148857, answer score: 8

Revisions (0)

No revisions yet.