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 IDEF1X¹ 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
  1. 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

  2. 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 specific attribute of the respective entity type;
• a particular data type is fixed for each column in order to ensure that all the values it contains belong to a single and well defined set, be it INT, DATETIME, CHAR, etc.; and
• multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined in the conceptual schema.

All of this in agreement with the stipulations by Dr. Edgar Frank Codd in his relational model (as far as the capabilities of the Structured Query Language permit at the time of writing).

Of course, with this logical layout you can still, e.g., derive in BOOLEAN form whether a given User is the Owner of a particular Property or not, with the aid of a SELECT operation that produces a scalar value that evaluates to either 'True' or 'False', which can be accessed from one or more (present or future) application programs sharing this database.

Covering the initial business rules with the same logical-level arrangement

These aspects are now mostly anecdotical, but some of the initial business rules were:

• A Property has zero-or-one Owner, which must be a User
• A User is the Owner of zero-one-or-more Properties
• A Property has zero-or-one Tenant, which must be a User
• A User is the Tenant of zero-one-or-more Properties

And they can be coped with using the same logical design previously discussed declaring the property_id column as an ALTERNATE KEY in both the ownership and the tenancy tables, via two more constraints, i.e.:

ALTER TABLE ownership
    ADD CONSTRAINT UNIQUE ownership_AK (property_id);

ALTER TABLE tenancy
    ADD CONSTRAINT UNIQUE tenancy_AK (property_id);

This shows other facet (that can be of help for a future visitor) of the versatility offered by the arrangement proposed in this answer.

Endnote

_{¹ 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) some of the theoretical works 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.}