Theo's Typesafe CultTTC
Theo's Typesafe Cult•3y ago
venego

can I have overlapping primary keys? (mysql)

I have this DB where I have two entities that has to be managed by a user.
The user can manage either one of the two entities or both.
So here is the structure:
create table users(
  id varchar(50) primary key
)
create table A(
  id varchar(50) primary key
)
create table B(
  id varchar(50) primary key
)
create table privileges(
  id varchar(50),
  manager_fk varchar(50),
  A_fk varchar(50),
  B_fk varchar(50),
  primary key(manager_fk, A_fk, B_fk),
  foreign key(A_fk) references A(id),
  foreign key(B_fk) references B(id)
)


the problem:
If a user has access to both A and B. he also can accidentally have access to one of them and the table would have partial duplication.

solutions in mind:
- I could only add privilege to only one of them entities at a time.
- I could have two privileges tables. one for A and another for B

I thought there should be some way to have an overlapped primary keys or something similar.

my real question: Is there any other way to do this other than my simple solution?
Was this page helpful?