Designing foreign keys to one of several tables

Question

I have three tables: Customers, Providers and Locations. I need to create a fourth table called Contacts.

I'd like to have any number of Contacts associated with any row in the Customers, Providers and Locations table so I ended up with something like this.

CREATE TABLE [dbo].[Contacts] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [CustomerId] INT            NULL,
    [ProviderId] INT            NULL,
    [LocationId] INT            NULL,
    [Name]       NVARCHAR (80)  NULL,
    [Email]      NVARCHAR (80)  NULL,
    [Phone]      NVARCHAR (80)  NULL,
    [Title]      NVARCHAR (80)  NULL,
    [Address]    NVARCHAR (120) NULL,
);

I don't find this very elegant. In addition to having unused columns, I probably should add a constraint to ensure exactly one of CustomerId, ProviderId and LocationId are not NULL.

Another alternative is to create a many-to-many join table. This won't need any unused columns. But it still seems like a waste as no contact will ever related to more than one company.

Is anyone aware of any slicker solutions?

Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, May 25 '20 at 23:01
This is pretty opinion based, and might get closed as such. IMO if you are 100% sure a contact would never be shared and 100% sure that contact will only ever be associated with 3 different tables then I would stick to what you have. However in my experience after a period of time there often comes an exception to the rules and then you need a more complex solution such as that provided by GMB below. — Dale K, May 25 '20 at 23:07
I don't think it's opinion-based. The question is basically "what are the reasonable alternatives for this scenario?", which is a valid technical question. — David Browne - Microsoft, May 25 '20 at 23:11
@DavidBrowne-Microsoft but there are multiple alternatives, and trade-offs with all of them, so it comes down to a design decision. — Dale K, May 25 '20 at 23:12
@DaleK: I don't see why we have to close a question just because someone might have a different opinion. Just trying to explore what the options might be. — Jonathan Wood, May 25 '20 at 23:14
@JonathanWood thats the way the site works... and why there is an option to close "Opinion Based". — Dale K, May 25 '20 at 23:14
@philipxy: It's not really addressing the same thing, although the idea of having an intermediary table with a PK as a FK is definitely one approach that could be taken. — Jonathan Wood, May 25 '20 at 23:18
In my experience this is one of those problems which doesn't have a "neat", "slick" solution. Then properly normalised databases aren't always neat. It becomes a tradeoff decision. — Dale K, May 25 '20 at 23:19
@DaleK: I know there's an opinion-based option to close, but we have a lot of leeway here. The answers so far are not arguments about which way is best, they are presenting different options and there is some discussion about the pros and cons of some of those options. To me, that seems helpful. — Jonathan Wood, May 25 '20 at 23:23
I agree that its both interesting and helpful... but you already have 2 answers (and there are more possibilities), both of which completely solve the problem, so how will you select a single answer? Anyway I haven't voted to close, I was just pointing out the possibility because I have seen many such questions closed - because there is not one answer. — Dale K, May 25 '20 at 23:26
Well, I appreciate that you weren't the one who voted to close. But yes, I have two answers, and there are more possibilities. That was the part I found useful. — Jonathan Wood, May 25 '20 at 23:30
Asking for ways to do things is not a valid question. Exclusive FKs to multiple tables is a faq. But it's usually an anti-patteren for subtyping/inheritance/polymorphism--also a faq. Moreover clearly we can expect it & use case variants to be faqs. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. [ask] "not very elegant" means nothing. — philipxy, May 26 '20 at 00:00

score 1 · Accepted Answer · answered May 25 '20 at 23:03

An alternative would be revert the relationship, and create one mapping table for each entity that a contact may relate to, like:

CREATE TABLE [dbo].[Contacts] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (80)  NULL,
    [Email]      NVARCHAR (80)  NULL,
    [Phone]      NVARCHAR (80)  NULL,
    [Title]      NVARCHAR (80)  NULL,
    [Address]    NVARCHAR (120) NULL
);

CREATE TABLE [dbo].[ContactCustomers] ( 
    [ContactId]  INT NOT NULL REFERENCES Contacts([ContactId]),
    [CustomerId] INT NOT NULL REFERENCES Customers([CustomerId]),
    PRIMARY KEY([ContactId], [CustomerId])
);

CREATE TABLE [dbo].[ContactProviders] ( 
    [ContactId]  INT NOT NULL REFERENCES Contacts([ContactId]),
    [ProviderId] INT NOT NULL REFERENCES Providers([ProviderId]),
    PRIMARY KEY([ContactId], [ProviderId])      
);

CREATE TABLE [dbo].[ContactLocations] ( 
    [ContactId]  INT NOT NULL REFERENCES Contacts([ContactId]),
    [LocationId] INT NOT NULL REFERENCES Locations([LocationId]),
    PRIMARY KEY([ContactId], [LocationId])      
);

This gives you the full flexibility in terms of relationships, while keeping the Contacts table focused on its primary purpose: storing the data related to this entity.

This appears to be the same as my second idea of a many-to-many join table, even though it's not really a many-to-many relationship. Certainly a possibility, if not as efficient and concise as I'd like. — Jonathan Wood, May 25 '20 at 23:10
As @DaleK suggested, there isn't really a slick approach to this. I decided to take this approach, mostly because it's easier to ensure the data is valid through constraints. — Jonathan Wood, May 25 '20 at 23:44

David Browne - Microsoft · Answer 2 · 2020-05-26T13:30:26.717

In addition to @GMB's suggestion, the other alternative is to simply have different contact tables. Just because Locations and Customers both have contacts, and just because they initially have the same attributes, doesn't mean they have to be stored in the same table.

It's more efficient to store them separately like this, if you aren't going to query over all contacts. And the schema for the three may diverge over time.

They could even share a NotMapped superclass of Contact in the application, if you wanted to write common logic over the contact types.

EG

CREATE TABLE [dbo].[CustomerContacts] (
    [CustomerId] INT            not null references Customer on delete cascade,
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (80)  NULL,
    [Email]      NVARCHAR (80)  NULL,
    [Phone]      NVARCHAR (80)  NULL,
    [Title]      NVARCHAR (80)  NULL,
    [Address]    NVARCHAR (120) NULL,
    constraint pk_CustomerContacts primary key (CustomerId,Id)
);
CREATE TABLE [dbo].[ProviderContacts] (
    [ProviderId] INT            not null references Provider on delete cascade,
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (80)  NULL,
    [Email]      NVARCHAR (80)  NULL,
    [Phone]      NVARCHAR (80)  NULL,
    [Title]      NVARCHAR (80)  NULL,
    [Address]    NVARCHAR (120) NULL,
    constraint pk_ProviderContacts primary key (ProviderId,Id)
);
CREATE TABLE [dbo].[LocationContacts] (
    [LocationId] INT            not null references Location on delete cascade,
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (80)  NULL,
    [Email]      NVARCHAR (80)  NULL,
    [Phone]      NVARCHAR (80)  NULL,
    [Title]      NVARCHAR (80)  NULL,
    [Address]    NVARCHAR (120) NULL,
    constraint pk_LocationContacts primary key (LocationId,Id)
);

Yeah, this would work, and it's simple in some ways. But as far as elegance or efficiency, it's doesn't score very well. — Jonathan Wood, May 25 '20 at 23:12
It's the _most_ efficient so long as you don't need to query across all types of Contacts. GMB's solution requires an additional join to get the contacts, and a Primary key on Contacts.Id, both of which can be omitted here as the contact's id is a trailing key of the clustered primary key. The contacts for each entity will be stored together in the clustered index, so `select * from CustomerContacts where CustomerId = @CustomerID` will cost 4-5 logical IOs. Period. — David Browne - Microsoft, May 25 '20 at 23:25
@DavidBrowne-Microsoft: I agree with what you said. But efficiency can also be related to the number of tables and columns needed to represent the data. I wasn't only thinking of performance. — Jonathan Wood, May 25 '20 at 23:33
@JonathanWood From the other side, this is the ONLY design which makes the corresponding contact entry owned by its "parent", hence can freely be added, deleted or modified per specific parent. Also automatically deleted with parent. While in the accepted approach, the contact is separate entity which can be shared between customers, providers and locations. None of them "owns" it, hence cannot automatically delete it, and modifications will affect the others. I'm not saying it is bad, it's just a different behavior than the original (with constraint that exactly one FK is set). — Ivan Stoev, May 26 '20 at 06:32
@IvanStoev: Yes, I can see that. Cascading deletes could not be fully implemented and orphan contacts are certainly possible. — Jonathan Wood, May 26 '20 at 13:12

score 0 · Answer 3 · answered May 27 '20 at 09:57

0

Contacts is a generalization of Customers, Providers, and Locations. There are a couple of techniques you may find useful here. shared-primary-key and class-table-inheritance.

answered May 27 '20 at 09:57

Walter Mitty

18,205
2
28
58

Designing foreign keys to one of several tables

3 Answers3