CODEWITHSUNDEEP
sqlpostgresqlrelationshipentity-relationship
WASD
WASD

Reputation: 320

Relationship between attributes inside the same entity and queries/view

We have a entity users with attributes id, name. How to 'implement' the relationship between these users with rules:

this is solely made up, just me learning db

So I made this ER diagram, the blue lines is what I set to unique/alternate-key

enter image description here

DB-Fiddle

  1. Is this the right approach or did I put it all together wrong?
  2. How to solve this reverse uniqueness? In case you missed it, I could place Jack-Anne to lovers, and Anne-Jack to married, even Jack-Jack (with ids). I could use CHECK constraints to not be equal or one to have smaller id (which wouldn't work with uuid). But is there a better way? Is the answer to this, to do the whole logic at front-end at insert?
  3. For the view, I get the right results but could that be done 'better'? I just want to show the users_relationship with users.name, rel_type.type.

Thanks, any tips/suggestions are welcome!

Upvotes: 1

Views: 95

Answers (1)

The Impaler
The Impaler

Reputation: 48770

  1. Yes, it's a good approach, and is at least 3NF.

  2. To avoid symmetric duplicates, add an extra constraint in the table users_relationship, as in:

    create table users_relationship (
  column1 definition,
  column2 definition,
  ...
  contraint uq_pair check (users_id < users_2_id)
  ...
);

  3. Yes, it looks fine.

Upvotes: 1

Related Questions