Relational databases are the best tools we have for managing structured data. Structured data is what keeps the wheels turning. Structured data is everything except the augmented gossip that makes up social media. Structured data is: your bank account, your tax return, your medical records, your shopping, your satellite navigation, the accounts of every organisation you deal with. You want structured data to be true, accurate, reliable and efficient.
Relational databases manage structured data with a surprisingly small number of patterns: the rectangular table, the "primary key", and "foreign key" references to other tables. There are further patterns for using these sytructures, and these are described in Database Design. These patterns enable us to represent real-world situations and reliable processing methods. With these we can accurately represent all the data structures in our structured data.
I have never seen a database in a live system that stuck to the necessary structures. There is always some "clever" technique that circumvents the guard-rails which keep a database in good shape.
One area where invention is more common than good practice is in the handling of sub-types.
Here's an example. A charity (sort of like a "Non-Profit organisation" in the USA) will have donors. A donor may be an individual person, a grant-making foundation, a business, a school, a social society or club, a government department, and probably a few other things.
The things in this list (we'll call them "sub-types") will have different attributes. A person will have first_name, last_name, date_of_birth, national identity number. A business will have a name, a head_office address, a number_of_employees, a standard industry classification. A school will have a name, an education stage(early years, primary, ...), an inspection rating.
Each of these separate types of donors should be in a separate table. They all have different attributes - even the name will take different formats in different sub-types: each person will have two names at least, whereas the others have one.
Different sub-types will have different sets of attributes.
But all these sub-types are donors to the charity and we want to record which donor gave which donation.
We must model sub-types in such a way that we use the database features to ensure data quality and to make queries as easy as possible.
These different kinds of donors all make donations, and the donations are stored in the "transaction" table along with other financial transactions. We need to relate transactions to the donors and we do this with a foreign key for each sub-type.
This section explains some common approaches and what the problems with them are. You can skip these and jump to the recommended approach if you want to.
We see this approach frequently, and it is sometimes taken to extremes. The example we are using here has four types of donors. The real-world example that inspired this was a charity system that had 36 different "types" in its "contact" table. Of these, seven were types of supporters (who were actually contacts), others included different types of beneficiaries, programmes in various countries, analysis codes, products, "dummy" contacts for use in street collections, and a "bucket" account.
The first thing we did, when building the data warehouse, was to separate this table into different tables for different things. In a few cases, different types were very similar in meaning and had almost identical sets of attributes, so we did recombine those.
Separating the records into their various types reduced the number of columns in each table. When they were all combined, every record had several empty columns because they were not relevant to that type.
The separation also simplified queries that were run on the original table, eliminating the code that was needed to pull-apart the records in the original table.
A result of analysing the operational database and copying the data into a cleaner structure was that the total number of tables decreased and the number of columns in most tables decreased too. All the data (except some temporary tables that were created years ago and not used for years) was transferred to the data warehouse. But the data warehouse was smaller than the original live database, queries were simpler and the performance was better. Also we were able to implement more sophisticated interactive searching across all the data.
Far from being "normalised to death" the data warehouse was normalised to greater efficiency and ease-of-use. The normalisation principles exist for good reasons.
With these sub-types, it would be very wrong to try to use a single foreign key.
If anyone in your team tries to do this, you should have a "Come-to-Jesus talk" with them right away. The database will not let you define a constraint on multiple tables, and it should not, because you will have the same id value in different tables; you need the table name id to find a particular record a of a particular type. In this case you could never tell which table the donor_id refers to. You could add a donor-type column but then the meaning of the donor_id would depend on the value of the donor_type, and it should depend only on the id of the row.
A solution that is used more often is the many-to-many model. In this we add four relationship tables, one each for person, business, school or society. We now know which related object we are linking to, whichever type the transaction belongs to, and referential integrity constraints can be enforced.
But, using this approach, one transaction could be linked to more than one sub-type. Having a transaction linked to both a person and a business, for example, would be an error. This is the sort of structural error that we want the database system to prevent, but it cannot do that with this structure.
This uses the object-oriented concept of a super-type from which sub-types can inherit common attributes. In this case, the only thing that is inherited is the donor_id which delivers a key that is unique in the donor table. Note that, in each sub-type, the donor_id is the primary key as well as being a foreign key to the super-type "donor". But, the database still cannot prevent the same donor_id from being used for more than one donor sub-type. So this approach does not solve the problem.
And, this approach requires some extra programming support. When adding a new sub-type record (a school, say) we have to: first add a new donor record, then retrieve the key of this record (assuming that the key is generated, as it should be) and use that key to populate the new school record. Doing this in a batch process, as we would normally do with a data warehouse, it becomes more complicated. We would need to store another key for the school in the super-type (donor) so that we could match this key with something in then transaction table which told us what type of donor we were dealing with. Only then could we populate school records with the correct donor_id.
An approach which describes what we want to do accurately, is to provide, in the transaction record, an identifier column (a foreign key) for each type. In any one transaction,
We can get the database to enforce that there is always one, and only one, of these foreign key references in any one record, using a "check constraint", like this:
create table transaction
(
id integer generated always as identity,
person_id integer references person(id),
business_id integer references business(id),
school_id integer references school(id),
society_id integer references society(id),
created_date date,
amount numeric(9,2)
constraint exactly_one_donor_id
check (num_nonnulls(person_id, business_id, school_id, society_id) = 1)
);
Now, one transaction must be linked to one donor sub-type, and it cannot be linked to more than one donor sub-type.
This works with PostgreSQL because PostgreSQL:
In other databases we can write a function to count a number of nulls, although the number of parameters is fixed so calls to the function usually have a bunch of null parameters following the number that we actually need in a particular case. But with these other databases we cannot use a function in a check constraint anyway, so we have to do it the long way, like this:
create table transaction
(
id integer generated always as identity,
person_id integer references person(id),
business_id integer references business(id),
school_id integer references school(id),
society_id integer references society(id),
created_date date,
amount numeric(9,2)
constraint exactly_one_supporter_id
check
(
(person_id is not null and business_id is null and school_id is null and society_id is null) or
(person_id is null and business_id is not null and school_id is null and society_id is null) or
(person_id is null and business_id is null and school_id is not null and society_id is null) or
(person_id is null and business_id is null and school_id is null and society_id is not null)
)
);
In my experience, the number of sub-types is usually small, often just two. In the non-PostgreSQL version, the constraint has the same number of lines as the number of sub-types and each line contains an entry for each of the sub-type keys. If you have a lot of sub-types then this will get quite cumbersome, but once you have written it and tested it, you'll never need to look at it again unless you add yet another sub-type.
This is still better than any other method I have seen, even in Bill Karwin's fine book (details below).
The advantage of this approach is that we avoid adding linking tables or super-type tables, and we make our queries simpler, while ensuring that the database links work correctly.
This approach works with MariaDB, Oracle and Microsoft SQL Server. It works with PostgreSQL too, but the PostgreSQL-specific version above is neater. It probably works with other databases, but I have not tested it with them. It can be extended to many sub-types.
Many books have been written about Data Modelling. Three References that we find useful are:
An Introduction to Database Systems, by C J Date
Brilliant, but very mathematical, and hard work.
SQL Antipatterns - Avoiding the Pitfalls of Database Programming, by Bill Karwin
Practical advice about common mistakes and how to do better.
Relational Databases For Agile Developers, by Ron Ballard
Plain-English guide to data modelling (and other database topics) with many examples.