I have a postgres database for representing relational data. In my case the tables are as follows:

• User: Basic user
• Issuer: Upgraded user
• Badge: Badge to issue, can only be created by issuer, and can be issued to any type of user

The relation between the tables are as follows:

• Issuer - User: one to one, basic info of issuers are held in User table, issuer related info are held in Issuer table
• Issuer - Badge: one to many, an issuer can create multiple unique badges
• Badge - User: many to many, one user can hold multiple diff. badges and a badge can be issued to multiple diff. users

I have some specific questions in mind after doing some research:

1. Should I combine User and Issuer tables into one and add a field to indicate the role of the user, or should I keep them separated? Note that Issuer has multiple fields that are unique to them (such as createdBadges). This way User will have both createdBadges and issuedBadges fields.
2. Should I create separate table for indicating the relation between Badge and User called Issuances, or should I just have array references to each other?

I ask these questions with the intent of having better logical coherency, better management, and also higher efficiency. Any other suggestions to improve the design is very much appreciated!

CodePudding user response：

• issuer gets a foreign key to users with a unique constraint on it

• badge gets a foreign key to issuer

• a junction table user_badge has foreign keys to both users and badge and a primary key that is composed of both foreign keys

Don't call a table user, since that is a reserved word in SQL.