Can I have a Table with primary Key which is a Foreign Key in more than one Table

Question

I want to create a database for a group feature in my Flutter Application and I want the group to consist of administrators with different roles like Moderator, Secretary, etc...

Group Table
groupId	int not null primary key
roleId	int foreign key
firstName	string (50)
lastName	string (50)
email	string (80)
address	string (100)

Role Table
roleId	int not null primary key
roleType	string (20)
groupId	int foreign key

Admin Table
adminId	int not null primary key
roleId	int foreing key
groupId	int foreign key

Can I have groupId as a Foreign Key in Role_Table and also have groupId as foreign key in the Admin_Table?

Answer 1

It is not fully clear to me what you want to achieve with the group. But to answer your question: it is possible to reference the same table as foreign key in different tables. So yes, you can use groupId as foreign key in Role_Table and Admin_Table.

However there are some potential issues here: Admin_Table has its own roleId. This is either an undesired redundancy, if your code has to keep both consistent, or a design that is potentially ambiguous, since a given adminId could be associated directly with one groupId, and indirectly via the role table to another groupId. Thera are some situations where this triangular relationship is needed on purpose, but are you sure in your case?

There is a potentially bigger issue: In Group_Table, a groupId (primary key) is associated to a roleId (foreign key). This means that there is only one role possible for a given group. At the same time, in the Role_Table a roleId (primary key) is associated to a groupId (foreign key). This means that either there is a one-to-one relationship between groups and roles (that needs to be kept in sync), or roles and groups form a complex interleaved hierarchy.