0

I'm using PostgreSQL and new to databases in general.

Say I have a table of users, and I want to let users define that they are another user's wife/husband/whatever they want. Assume that a user can say another user is their grandma, and that user can say the other user is their "grandson in Maine" (i.e. it is directed).

Do I create a table for users, and another table that looks something like:

CREATE TABLE user_relationships (
  user0 INT REFERENCES app_user(id),
  user1 INT REFERENCES app_user(id),
  relationship VARCHAR(100)
);

to hold all of the relationships separately, or is there a better way to link both the other user and the defined relationship in the user table?

user3236716
  • 189
  • 1
  • 11
  • 1
    Separate table as defined. since a user could have many of these relationships and each relationship could have different data, a separate table is needed. It's about the cardinality of the relationship if it's 1-1 it likely belongs in the user table (if it's null alot maybe a different table) but since it's 1-N you need a separate table. – xQbert Aug 01 '17 at 20:16
  • This seems like a reasonable approach, you can always expand the user_relationships table to include more data if needed without affecting the app_users – ITDerrickH Aug 01 '17 at 20:16
  • Honestly seems like a pretty good approach. Since there can be as many entries as you want from a user, you have to use a separate table like this. – JoshKopen Aug 01 '17 at 20:18
  • I would suggest to create a new relationship table and it will avoid repeating values for the relationship. Also, from & to as the relationship type. Grandmother & Grandson will be the relationship type when from and to user changes. – Venkataraman R Aug 01 '17 at 20:18
  • I also suggest you to create a surrogate key and create index on the same. This will avoid dependency on the composite key of userid1, userid2. [Composite vs Surrogate keys] (https://stackoverflow.com/questions/23850396/composite-vs-surrogate-keys-for-referential-integrity-in-6nf#23851003) – Venkataraman R Aug 01 '17 at 20:24
  • perhaps a unique index on user0, user1 as well so that a relationship can't be defined more than once? – xQbert Aug 01 '17 at 20:26
  • @xQbert But what if they're kissin' cousins? – justiceorjustus Aug 01 '17 at 20:30
  • If you may later want to analyse your data by type of relationship then you may want to abstract this out to another table which could then hold other details of relationships. i.e. your column "relationship" would become: relationshipId int references relationships (relationshipId). Then your relationship table could be e.g. relationshipid int not null, relationshipdescription varchar (100), reciprocalrelationshipId int null references relationships(relationshipid). (So you could hold that Grandmother and Grandson are reciprocal.) – Lord Peter Aug 01 '17 at 20:33
  • @justiceorjustus kissin' cousins is ambigious. Like bi-weekly. Does it mean twice a week or every other week? is kissin cousin's first cousins cause you know them or after first cousins cause you can marry em. – xQbert Aug 01 '17 at 20:36
  • @xQbert But what if they stop being kissin' cousins and go back to being regular cousins? Would he update the value from "kissin' cousin" to "cousin" or remove the row "wife" or "husband" and leave the row "cousin"? – justiceorjustus Aug 01 '17 at 20:41
  • I guess I'd do what Lord Peter indicated and have a set of "allowed" relationship types of which kissin' cousins wouldn't be listed due to ambiguity. – xQbert Aug 01 '17 at 20:42

0 Answers0