Database Design – How to Link Users and Jobs with Addresses

database-design

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.

Best Answer

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:

Figure 1 - Users, Jobs and Addresses Simplified Data Model

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 resource, it would be convenient to create a VIEW (i.e., a derived or virtual table) that displays columns that belong to the base tables named UserProfile and MyAddress, e.g.:

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;

In this way, you can SELECT directly FROM the VIEW without having to write the JOIN clause and the condition every time you have to retrieve related information. Of course, you can employ a similar approach for job and address data.

Many-to-many relationship between User and Address

In case that you face the need to implement a many-to-many (M:N) relationship between the Address and User entity types —which would not be unusual in other contexts—, some small modifications to the business rules formulations would be required, as shown bellow:

  • A User keeps zero-one-or-many Addresses
  • An Address is kept by zero-one-or-many Users

Thus, I drew a new logical model that portrays this situation in Figure 2:

Figure 2 - Users and Addresses Simplified Alternative Data Model

As demonstrated, an associative entity that connects (a) User with (b) Address is included, and I named it UserAddress accordingly; its PK is made up of two different FOREIGN KEYs, and each of them makes a reference to the respective entity type.

I coded an illustrative DDL structure with this alternative arrangement:

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)    
);

If the cardinality of the relationship among Job and Address also happens to be (or, who knows?, at some point in time becomes) M:N in your business domain, you can follow a method comparable to the one just detailed.

Notes

1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) theoretical work authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.

2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.

Related Question