How to model the relationships (a) between properties and owners and (b) between properties and tenants?

I have a database design and I'm having trouble with two tables. I have a Users table, and a Properties table. The Properties table may only have 0, 1, or 2 users, let's call them owner and tenant, and only one of each (per property), and a User may be an owner or a tenant in many Properties.

So, here are my two options according to the case:

• Have two foreign keys in the Properties table (since the maximum of users referenced by a property are 2 and not N), called owner_id and tenant_id which reference the Users table.

• Have a N:M intermediate table, with the user_id and property_id, along with another column to indicate the type of user in that property (say boolean 1 = owner, 0 = tenant).

Which is the best design option?

Clarifications:

• A user has a name and email, and a property a street and its number as attributes.

• A user may be only an owner or tenant in a property, not both, since an owner implies he owns the property (wether he lives there or not), and a tenant is another user actually living there, so there needs to exist an owner before a tenant is added.

• A property may have no users assigned.

Business rules

So, (1) after we engaged in some deliberations via comments and chat, and (2) once you discussed the possibilities with your customer and colleagues, it has been defined that, in fact:

• A Property has zero-one-or-many Owners, each of which must be a User

• A User is the Owner of zero-one-or-many Properties

• A Property has zero-one-or-many Tenants, each of which must be a User

• A User is the Tenant of zero-one-or-many Properties

• A Property is uniquely identified by the combination of its Street and its Number

• A User is uniquely identified by its EmailAddress

Therefore —in your business context— among other aspects, there are two distinct conceptual-level associations (or relationships) of cardinality many-to-many (M:N) between the entity types Property and User.

Illustrative IDEF1X diagram

Consequently, I have created an IDEF1X1 diagram that consolidates the business rules formulated above, which is shown in Figure 1:

As demonstrated, every entity type —be it associative or independent— is portrayed by means of its individual box.

I depicted (a) ownership and (b) tenancy as two different associative entity types since, although very similar, they represent the prototypes of two distinct things of interest in you business domain, so it is very convenient to manage them separately because, e.g.:

• Apart from

• solely retaining the fact that a given user is the tenant or owner of a property by way of true or false attributes —named, say, is_owner and is_tenant—,

-
you might as well be interested in

• keeping the exact point in time when all the ownership and tenancy instances where registered in the system, thus establishing a time-related attribute —entitled, say, created_datetime— for said entity types would be very advantageous.

-
In the future, one of these entity types may entail keeping track of certain attributes that do not apply to the other, hence administering them discretely yields benefits from the beginning, making up a more extendable and versatile conceptual schema.

It is worth to mention that, in the diagram, the user_id attribute migrates from (i) the user entity type to (ii) ownership and (ii) tenancy as owner_id and tenant_id respectively —both quite descriptive role names that you provided in the question—.

Expository logical SQL-DDL design

Then, based on the IDEF1X diagram detailed above, I wrote the expository DDL layout that is declared as follows:

-- 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 based on the exact 
-- data manipulation tendencies of your business domain.

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

CREATE TABLE user_profile (
    user_id          INT      NOT NULL,
    first_name       CHAR(30) NOT NULL,
    last_name        CHAR(30) NOT NULL,
    gender_code      CHAR(3)  NOT NULL,
    birth_date       DATE     NOT NULL,
    email_address    CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT user_profile_PK  PRIMARY KEY (user_id),
    CONSTRAINT user_profile_AK1 UNIQUE ( -- Multi-column ALTERNATE KEY.
        first_name,
        last_name,
        gender_code,
        birth_date
    ),
    CONSTRAINT user_profile_AK2 UNIQUE (email_address) -- Single-column ALTERNATE KEY.
);

CREATE TABLE property (
    property_id      INT      NOT NULL, 
    street           CHAR(30) NOT NULL,
    number           CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    -- 
    CONSTRAINT property_PK PRIMARY KEY (property_id),
    CONSTRAINT property_AK UNIQUE      (street, number) 
);

CREATE TABLE ownership (
    property_id      INT      NOT NULL, 
    owner_id         INT      NOT NULL,   
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT property_PK              PRIMARY KEY (property_id, owner_id),
    CONSTRAINT ownership_to_property_FK FOREIGN KEY (property_id)
        REFERENCES property (property_id),
    CONSTRAINT ownership_to_owner_FK    FOREIGN KEY (owner_id)
        REFERENCES user_profile (user_id)
);

CREATE TABLE tenancy (
    property_id      INT      NOT NULL, 
    tenant_id        INT      NOT NULL,   
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT tenancy_PK             PRIMARY KEY (property_id, tenant_id),
    CONSTRAINT tenancy_to_property_FK FOREIGN KEY (property_id)
        REFERENCES property (property_id),
    CONSTRAINT tenancy_to_tenant_FK   FOREIGN KEY (tenant_id)
        REFERENCES user_profile (user_id)
);

Hence, in such logical-level design:

• each base table represents an individual entity type (which prevents ambiguities introduced by denoting distinct meanings and intentions by virtue of the same base table);

• every column stands for a spe

StackExchange Database Administrators Q#182998, answer score: 4