CODEWITHSUNDEEP
postgresql
PythonNewbie
PythonNewbie

Reputation: 1163

How to create table (SQL) for storing products information

I have been doing quite a few tutorials such as SqlBOLT where I have been trying to learn more and more regarding SQL. I have asked some of my friends where they recommended me to check "JOIN" for my situation even though I dont think it does fit for my purpose.

The idea of mine is to store products information which are title, image and url and I have came to a conclusion to use:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    title TEXT,
    image TEXT,
    url TEXT UNIQUE, 
    added_date DATE
);

The reason of URL is UNIQUE is due to we cannot have same URL in the database as it will be duplicated which we do not want but then still I dont really understand why and how I should use JOIN in my situation.

So my question is, what would be the best way for me to store the products information? Which way would be the most benefit as well as best performance wise? If you are planning to use JOIN, I would gladly get more information why in that case. (There could be a situation where I could have over 4000 rows inserted overtime.)

I hope you all who are reading this will have a wonderful day! :)

Upvotes: 0

Views: 1302

Answers (1)

Mitko Keckaroski
Mitko Keckaroski

Reputation: 1040

The solution using stores.

CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    store_name TEXT
    -- add more fields if needed
);


CREATE TABLE products (
    id SERIAL, 
    store_id INTEGER NOT NULL,
    title TEXT,
    image TEXT,
    url TEXT UNIQUE, 
    added_date timestamp without time zone NOT NULL DEFAULT NOW(),
    PRIMARY KEY(id, store_id)
);


ALTER TABLE products
ADD  CONSTRAINT "FK_products_stores" FOREIGN KEY ("store_id")
        REFERENCES stores (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT;

Upvotes: 1

Related Questions