CODEWITHSUNDEEP
postgresqldatabase-designmany-to-many
Falcoa
Falcoa

Reputation: 2720

What the best way to model a many to many relationship

I'm trying to understand the best way to model the following relationship:

I have a table named Contact that has records of contacts uniquely identified using a serial field and other fields like name, phone, address and so on.

I also have a table named ProductInfo that has several fields including three fields named Author, Distributor and PointOfContact that each can have 1..n contacts from table Contact. So, in this way, this means that I can have two authors, 3 distributors and 1 point of contact to the same product.

I was thinking of storing this info in ProductInfo table with arrays of contact_id in each field, but this seems not to be the best approach.

Can you tell me what should I do?

Upvotes: 4

Views: 5476

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

A proper model that allows everything you need while enforcing referential integrity could look like this:

CREATE TABLE contact (
  contact_id serial PRIMARY KEY
, name text
, phone text
, ...
);

CREATE TABLE product (
  product_id serial PRIMARY KEY
, ...
);

CREATE TABLE product_role (
  role_id int PRIMARY KEY
, role text UNIQUE
);

CREATE TABLE product_contact (
  product_id int REFERENCES product
, contact_id int REFERENCES contact
, role_id    int REFERENCES product_role
, PRIMARY KEY (product_id, contact_id, role_id)
);

If the same contact can never act in more than one role for the same product, don't include the role in the PK:

, PRIMARY KEY (product_id, contact_id)

This allows to simply add a row to product_role to allow and additional type of contact.

If there is only a hand full of distinct roles, the data type "char" might be convenient for role_id.

Basics:

Upvotes: 4

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

Using arrays is not the best practice, yet for simple task like this it works fine. Especially if table productinfo contains millions of records, this way you avoid heavy joins, simpler and faster searches, some simple indexing speeding up queries ...

you can use a third table "link", table with references to productioninfo table and contacts assigned to it 

productInfo_contacts:
ID integer
productinfo_id integer
contacts_id integer
contact_type integer ( 1,2,3 - ... author, distributor, contact ...  )

using second approach is better, because then you have ways and room to add additional contact types, like co-author, contributors, translator ... or whatever additional contacts you may need to add later. Just keep in mind, joins sometimes will be more complex and will impact query performance significantly when working with large datasets

Well, that's my poor opinion :)

Upvotes: 1

Related Questions