Null foreign key columns

Question

I have a Database with different types of users. The users are going to be assigned to a location based on the user type. For example a sector user will have a SectorId and a community user will have a CommunityId. Now this means a Sector user will have Null as his/her CommunityId. Whats the best way to resolve this?

I have tried creating different tables for different types of users to get rid of these null columns but I feel it is not the best approach.

Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jul 26 '19 at 01:39

Gordon Linoff · Accepted Answer · 2019-07-24T18:12:37.117

If you don't have too many columns, then your approach is fine. You can set up check constraints to ensure data integrity.

From what you describe, I think the logic looks like:

create table t (
    . . .,
    sectorId int references sectors(sectorId),
    communityId int references communities(communityId),
    check ( ( (case when sectorId is not null then 1 else 0 end) +
              (case when sectorId is not null then 1 else 0 end)
            ) = 1  -- exactly one set
          ),
    check ( type = 'sector' and sectorId is not null or
            type = 'community' and communityId is not null
          )
);

These constraints check:

That the columns refer to the correct tables.
That only one of the id columns is set.
That the id columns harmonize with the type column.

Thanks so much for the response. Can you please clarify what the checks are doing? — Kaptin Koda, Jul 24 '19 at 16:04

score 0 · Answer 2 · answered Jul 24 '19 at 18:09

0

I have resolved it by rather adding 2 tables (Location and LocationType). So every user has a LocationId instead.

answered Jul 24 '19 at 18:09

Kaptin Koda

51
1
5

Null foreign key columns

2 Answers2