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