Database design with PostgreSQL: implementing foreign and primary keys

Question

I'm completely new to databases, and I'd like some insight on how to design a PostgreSQL database with this schema I have in mind. I'm not sure how to implement primary/foreign keys and relations in this case to achieve what I want.

I have created a a large table of customers, with the following information:

CUSTOMER
- unique ID
- review of the product ("positive", "negative")
- full name

Now, each CUSTOMER needs to have a PRODUCT table (which can be empty) and which contains a list of products:

PRODUCT
- product ID

and each product in this PRODUCTS table needs to be associated with an ITEMS table (which can also be empty), which contains a list of items.

ITEMS
- item ID

In the end, I want to be able to order individual items from the ITEMS table based on how "positive" or "negative" the customer reviews were. How do I implement foreign and primary keys in this case to be able to design this effectively?

Any insight would be much appreciated,

Thanks

Each product ID is associated with many item IDs @LaurenzAlbe — haxtar
– haxtar, Commented Jun 3, 2020 at 21:21

Laurenz Albe · Accepted Answer · 2020-06-03 21:33:08Z

1

You'd have a table for each items, products and customers (the latter without the review).

Then you have a table for reviews:

CREATE TABLE review (
   customer_id bigint REFERENCES customer NOT NULL,
   product_id  bigint REFERENCES product  NOT NULL,
   is_positive boolean NOT NULL,
   PRIMARY KEY (product_id, customer_id)
);

/* this is needed if customers ever get deleted */
CREATE INDEX ON review (customer_id);

answered Jun 3, 2020 at 21:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Basil Bourque Over a year ago

👍. I would add a mention of this being a classic example of Many-to-Many relationship. en.m.wikipedia.org/wiki/Many-to-many_(data_model)

haxtar Over a year ago

Thank you both, I think both the answer and this resource pointed me in the right direction.

Collectives™ on Stack Overflow

Database design with PostgreSQL: implementing foreign and primary keys

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related