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

user

has access to both

and

. 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

privileges

tables. one for

and another for

I thought there should be some way to have an overlapped

primary keys

primary keys

or something similar.

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

TTC

Theo's Typesafe Cult•4y ago

tarkov

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

user

has access to both

and

privileges

privileges

tables. one for

and another for

I thought there should be some way to have an overlapped

primary keys

primary keys

or something similar.

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

can I have overlapping primary keys? (mysql)

Similar Threads

can I have overlapping primary keys? (mysql)

Similar Threads

Similar Threads

Similar Threads