0

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?

  • Why Person, Clerk and Client are separate entities? A Person-1 (which has many Clerks and many Clients) formally may be a Clerk for Person-2, and a Client for Person-3... – Akina Dec 14 '19 at 20:39
0

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,
  . . .
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.