-1

I have a PostgreSQL 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
  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 want better logical coherency, better management, and higher efficiency.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Ahmet Yazıcı
  • 302
  • 9
  • [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [How do you effectively model inheritance in a database?](https://stackoverflow.com/q/190296/3404097) – philipxy Nov 29 '22 at 22:07
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Nov 29 '22 at 22:09

1 Answers1

1

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

  • badge gets a NOT NULL foreign key to issuer

  • a junction table user_badge has NOT NULL 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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you for the answer! I understand the 2nd and 3rd points but have few questions remaining regarding 1st. With the 1st one, there is no straightforward way to know the role of the user, so I believe LEFT JOIN needs to be done everytime a user's all information needs to be retrieved. Is that kind of a necessary overhead? Also would also storing a foreign key in `users` to `issuer`, with a possibility of null value could improve the solution or make it worse? – Ahmet Yazıcı Nov 29 '22 at 11:09
  • 1
    Yes, an outer join is the way to go. Having a foreign key from `users` to `issuer` would be an alternative, but I prefer `NOT NULL` columns. The outer join would be just the same. – Laurenz Albe Nov 29 '22 at 12:07