CODEWITHSUNDEEP
sqlpostgresql
Guid
Guid

Reputation: 2216

many to many to many SQL schemas

I have 2 tables: A and B.

A many-to-many relation units them through a join table A_B.

Now, my needs evolve: an A and a B can be related by more than 1 way.

I don't know what is the more conventional way to do that.

Must I declare a new "relation_way" table that contains the different "ways" for a A to be connected to a B and use this to compose a ternary key in A_B?

Upvotes: 0

Views: 59

Answers (1)

user330315
user330315

Reputation:

I would simply add a column to a_b that states the type of the relationship, e.g. relation_type that stores e.g. owned_by or referred_to or however you want to describe that relation (your obfuscated table and column names do not help a bit in answering this).

create table a_b 
(
   a_id integer not null references a,
   b_id integer not null references b, 
   relation_type text not null
);

If you allow multiple relations but with different types between two entities, then include the relation_type in the primary key of the a_b table.

If you want to restrict the possible relation types, you should create a lookup table:

create table relation_type 
(
  id integer primary key, 
  type_name varchar(20) not null unique
);

and reference that from the link table:

create table a_b 
(
   a_id integer not null references a,
   b_id integer not null references b, 
   relation_type_id integer not null references relation_type, 
   primary key (a_id, b_id, relation_type_id)
);

Upvotes: 3

Related Questions