DB Design – Different tables that share a common table – each has their own values

database-design

I have a Persons table. This table has two tables depended on, the Clients and Clerks tables, Persons has many Clerks and Persons has many Clients

Something like this, visually:

Persons
|     \

Clerks Clients

Clients and Clerks both need a relation with Locations table; both the Clients and the Clerks has a latitude, longitude combination and many other geographic related elements. I'm not sure about this approach: I cannot link the Clerks and Clients table with a one to one relationship, because Locations will have two foreign keys from different tables.

Persons
|     \

Clerks Clients

 \        /
  Locations

Is there another approach?

Best Answer

Is there another approach?

The alternative approach is to use the "Table-per-Hierarchy" design, and store all types of Persons in a single table, eg

create table Persons
(
  Id int primary key,
  PersonType char(6) check PersonType in ('Clerk','Client'),
  LocationId int references Locations,
  . . .
)

Related Solutions

PostgreSQL Database Schema – How to Review and Optimize Design

You could try this or this for generating Entity Relationship Diagrams.

Sql-server – Relational Design – One table, two foreign keys or Two tables, one foreign key each

Yes, there is an option D that would involve a supertype table for the subtype tables Cases and LocationInventories. You can search for the "supertype/subtype" pattern and "shared primary key" for additional info.

Option D is moved below and I'll add the other options for comparison:

Option A

Cases
    CaseID   PK  
    ... more columns

LocationInventories
    LocationInventoryID   PK  
    ... more columns

InventoryNeeds
    ?        PK
    CaseID   FK -> Cases (CaseID)
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity
        CK: CHECK ( CaseId IS NULL AND LocationInventoryID IS NOT NULL
                 OR CaseId IS NOT NULL AND LocationInventoryID IS NULL )

Option B

Cases
    CaseID   PK  
    ... more columns

LocationInventories
    LocationInventoryID   PK  
    ... more columns

InventoryNeedsCases
    ?        PK
    CaseID   FK -> Cases (CaseID)
    NeededQuantity

InventoryNeedsLocations
    ?        PK
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity

Option C

Without foreign keys, you lose referential integrity. I see no reason to follow this option.

Cases
    CaseID   PK  
    ... more columns

LocationInventories
    LocationInventoryID   PK  
    ... more columns

InventoryNeeds
    ?        PK
    NeedType CK: CHECK ( NeedType IN ('C', 'L') )
    NeedID   FK 
    NeededQuantity

Option D

With the addition of this one table, the constraints are quite simplified. The only thing that gets complicated is the insertion (and deletion) of rows to the two tables (Cases and LocationInventories) that will involve an additional INSERT into (or DELETE from) the ~~Bases~~ Needs table.

Needs 
    NeedID   PK

Cases
    CaseID   PK  FK -> Needs (NeedID)
    ... more columns

LocationInventories
    LocationInventoryID   PK  FK -> Needs (NeedID)
    ... more columns

InventoryNeeds
    ?        PK
    NeedID   FK -> Needs (NeedID)
    NeededQuantity

Option E

This is a modified D option that combines the NeedType of C option, without losing any referential integrity. It also avoids the (accidental or not) pitfall of option D, that allows a Need to be inserted in both Case and LocationInventory.

Needs 
    NeedID   PK  UQ1
    NeedType     UQ1  CK: CHECK ( NeedType IN ('C', 'L') )

Cases
    CaseID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType PK  FK1 
    ... more columns
        CK: CHECK ( NeedType = 'C' )

LocationInventories
    LocationInventoryID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType              PK  FK1
    ... more columns
        CK: CHECK ( NeedType = 'L' )

InventoryNeeds
    ?        PK
    NeedID   FK1 -> Needs (NeedID, NeedType)
    NeedType FK1
    NeededQuantity

Best Answer

Related Solutions

PostgreSQL Database Schema – How to Review and Optimize Design

Sql-server – Relational Design – One table, two foreign keys or Two tables, one foreign key each

Related Question