Theo's Typesafe Cult•2y ago

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)
)

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?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

can I have overlapping primary keys? (mysql)