1 to 1 relation with more than 2 tables

Question

Example. In my system I have one object that is used in a 1 to 1 relation by several other types of objects. Let's call the object that is used Address, and the two objects that use it Company and Person. Every Company and Person has one Address, each Address is unique in the system, so each Address is referenced by either a Person or a Company.

I have two questions.

Question 1) what is this relation called? All the examples of a 1 to 1 relation I've found on the web are between two tables only, while this is a three way to 1 to 1 relation.

Question 2) What table structure best captures this relation? I see at least two potential designs that both look flawed.

Design a) Uses two nullable foreign keys with the constraint that at least one is not null. Like a compound foreign key.

- Company -
pkCompanyId
...

- Person -
pkPersonId
...

- Address -
pkAddressId
fkPersonId <nullable>
fkCompanyId <nullable>
...

Design b) Each Company and Person identifies the Address it uses.

- Company -
pkCompanyId
fkAddressId
...

- Person -
pkPersonId
fkAddressId
...

- Address -
pkAddressId
...

Design a) seems like a more conventional use of foreign keys to me in that the object that is being used identifies the objects that reference it, but I haven't seen any examples of this being done with multiple foreign keys like this.

Design b) looks like the reverse of how a 1 to 1 relation is normally modelled.

It occurs to me that in a conventional 1 to 1 relation (below) that fact that fkPersonId is a non-nullable unique foreign key helps to enforce the 1 to 1 relation in the actual table structure (it guarantees that each Address is referenced by a single Person that references no other Address). Neither of my designs achieves this. In design a) something has to ensure that one and only one of fkCompanyId or fkPersonId is null to ensure consistency. In design b) something has to ensure that no fkAddressId in Company or Person is duplicated in either table. This causes me to suspect I'm making a mistake here.

- Person -
pkPersonId
...

- Address -
pkAddressId
fkPersonId
...

Without much effort I can see other designs, e.g. using a guid for pkCompanyId and pkPersonId and a single foreign key in Address but I again I don't see any examples of this strategy in any of the online resources I've researched.

I've clearly exceeded my limited knowledge of database design and could use some guidence please. What is the best way to handle this?

UPDATE Response to Walter Mitty's answer

I think Single table inheritance is not appropriate for my situation. Although I've used the examples Company and Person here, in my real application these are both large complex tables that have nothing in common other then the fact that they both own an address. Not only is their data very different, they represent completely different concepts.

My actual entities that each have an instance of the same type are Organisation, Project and ProjectElement. Organisation represents companies licenced to use a system. Project represents projects created by users of a particular Organisation, ProjectElement represents a piece of project data.

The thing they all have a instance of is a Settings object. Default settings are provided at the Organisation level. When a Project is created it gets a copy of the Organisation's settings. Components within a Project get a copy of the Project's settings.

This architecture supports several use cases, including when the settings for an Organisation are modified those changes only impact new Projects, not Projects that have already been created. ProjectElements inherit the settings of the Project but may optionally override specific Project settings.

Further complications include the fact that Organisations, Projects and ProjectElement don't just have an instance of one shared type, but several. There are several different types of Settings objects each with their own table. This makes it awkward if not infeasible to use the ID's of instances of the shared types as the primary keys of the referencing objects. I'm also using Entity Framework Core ORM which doesn't currently support views.

Your answer is very useful and gives me much to think about, but so far it seems me that my design 2 best achieves my requirements. Is there anything inherently unsound with design 2 that makes it unsuitable?

Answer 1

Your question looks like a case of type and subtype (or class and subclass). Before I try dealing with your two questions, I'm going to try to reframe it, however, as two relationships.

First, I'm going to invent a new entity, which I'll call "Contact". Each address in the system is referenced by a contact, and each contact has one or more addresses.

Each contact is either a person or a company, but not both.

Now, conceptually at least, we first connect an address with a contact, then we connect that contact with a person or a company. At the logical and physical levels, this may be overkill. That is, it may add complexity without adding value. I cannot emphasize this enough.

But conceptual clarity is valuable for its own sake.

Now on to your question 1.

The relationship between a contact and an address is just a classic "has-a" style relationship, like many you have seen before.

But the relationship between Contact and either Person or Company, is a different kind of relationship, one that you may not have seen before. It's called by various names, and this is my answer to your question 1.

It is sometimes called precisely an "IS-A relationship". In ER modeling, it often goes by the name "specialization/generalization" relationship. In object modeling, it's frequently called "Class/subclass relationship" although the buzzwords "derived class" or "extended class" are sometimes used. It's also connected to the object concept of inheritance.

A word of caution about object modeling, however. If you are going to do object modeling and data modeling in the same project, you had better have a pretty deep understanding of both of them, and how each of them adds value to your project. Otherwise, you are likely to fall into the trap of thinking that one model or the other is simply incomprehensible or useless. Smarter people than me have fallen into this trap many times.

Now for the second question. There are two ways you can design tables for this situation. One is called "single-table-inheritence" and the other is called "class-table-inheritance". The reason I put the hyphens in is that there are tags in this area for these two topics, and looking over the questions and the info under these two tags might help you.

Single table inheritance.

We will have two tables, one for Address and one for Contact.

Address:

AddressID (PK)
ContactID  (FK references Contact)
Street Address
City,
State,
Zipcode,
etc.

Contact:

ContactID (PK)
ContactType  (Person or Company, coded)
First Name
Last Name,
Company Name,
etc.

Company name will be NULL in the case of persons, First Name, Last Name will be NULL in the case of Companies).

Class Table Inheritance

We will have four tables, Address, Contact, Person, and Company

Address:

AddressID (PK)
ContactID  (FK references Contact)
Street Address
City,
State,
Zipcode,
etc.

Contact:

ContactID  (PK)

Person:

ContactID  (PK, and FK references Contact)
First Namme,
Last Name,
etc.

Company:

ContactID (PK and FK references Contact)
Company Name
etc.

For your case, I recommend single table inheritance. But for very complicated cases of IS-A relationships class table inheritance can work out better.