How can I manage multiple options in a PostgreSQL database?-CodePudding

I only played with mongodb on tutorials, but I want to learn SQL databases aswell.

I struggle a lot when multiple options come into play.

My actual database is this:

CREATE TABLE users (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    username VARCHAR(25) NOT NULL UNIQUE,
    password VARCHAR(200) NOT NULL,
    address VARCHAR(200) NOT NULL,
    email VARCHAR(100) UNIQUE,
    city VARCHAR(45) NOT NULL,
    postal VARCHAR(15) NOT NULL
);


CREATE TABLE products (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    product VARCHAR(75) NOT NULL,
    stock VARCHAR(3)  NOT NULL,
    color VARCHAR(20) NOT NULL,
    sizes VARCHAR(50) NOT NULL,
    price VARCHAR(15) NOT NULL,
    img VARCHAR(125) NOT NULL
);

CREATE TABLE orders (
     id BIGSERIAL NOT NULL PRIMARY KEY,
     created_at TIMESTAMP NOT NULL DEFAULT NOW(),
     status VARCHAR(25) DEFAULT 'pending',
     products VARCHAR(500) NOT NULL,
     user_id BIGINT REFERENCES users(id)
);

I am practising with a little ecommerce and dunno how to manage when I have multiple options. For example, All products can have more than 1 color or size. Actually I am managing inserting and array, but as I understand that is not how you should do it.

And my second problem is orders. All orders are supposed to have a list of the products the client ordered, and dunno how to storage it without using an array of elements.

Thanks, Ruben.

CodePudding user response：

postgresql can handle array, but that would not be what you seek: you have to model relations between entities (or tables).

From your description:

For example, All products can have more than 1 color or size. Actually I am managing inserting and array, but as I understand that is not how you should do it.

This simply means you will have one or two table the available colors/sizes:

products_colors (products_id, color)
products_sizes (products_id, size)

In the case of colors:

The products_colors.products_id column will be a foreign key referencing the products.id table.
The tuple (products_id, color) will be a primary key: you can't have the same colour twice or more.

In terms of create table:

    create table products_colors (
      products_id bigserial not null references products(id),
      color  VARCHAR(20) NOT NULL,
      primary key (products_id, color)  
    )

Let's say you have a product:

{
  "id": SOME_ID,
  "price": 14.00,
  "colors": [ "white", "black", ],
  "sizes": [ 1, 2, 3 ],
}

Here is what the relation table would contain:

products_id	colors
SOME_ID	white
SOME_ID	black

This is one way to represent the data, and of course, this will affect how you save the data:

for insertion, you will have to save the product first then the relation; you will have also have to check for unicity of the colour: yes, the database will throws, but not, it is not proper.
for deletion, you will have to delete the relations first, then the product, unless you enabled cascade delete on the foreign key.

And my second problem is orders. All orders are supposed to have a list of the products the client ordered, and dunno how to storage it without using an array of elements.

The same as previous: orders_products (products_id, orders_id, quantity)

I added the quantity but this depends on your functional requirement and what the user can do: for example, if your take an online store, you can buy the same product several time.

If you use a primary key (products_id, orders_id), then you will have to store the quantity (and possibly other informations) to handle such case.
You could store the couple twice (products_id, orders_id) but that would be redundant.

CodePudding user response：

You would be really wise to read up on a data design discipline called "entity-relationship".

You have three entities: users, orders, and products. You're asking about product variants.

In an actual phyisical store you'll find separate piles of StackOverflow T-shirts for small, medium, and large, orange and white. That means six piles of shirts. That means six rows in your products table -- the one with the number of shirts you have on hand. You don't run out of StackOverflow T-shirts in your store. You run out of large orange ones. That's why you need a row for each variant in your table.

The question becomes, "how do you group all those T-shirts together for your sell-page, so your customer can see one item and select size and color?" Answering that is quite a complex design task. Maybe you can have a product_group entity with a one-to-many relationship with your products entity.

Something like these two tables.

CREATE TABLE products (
    product_id BIGSERIAL NOT NULL PRIMARY KEY,
    product_group_id BIGSERIAL NOT NULL,
    product VARCHAR(75) NOT NULL,
    stock VARCHAR(3)  NOT NULL,
    color VARCHAR(20) NOT NULL,
    sizes VARCHAR(50) NOT NULL,
    price VARCHAR(15) NOT NULL,
    img VARCHAR(125) NOT NULL
);

CREATE TABLE product_group (
    product_group_id BIGSERIAL NOT NULL PRIMARY KEY,
    title VARCHAR(75),
    description VARCHAR(2000),
    img VARCHAR(125)
);

If you had these product groups you'd pull together each group's products into a single item in your online catalog.

But only you know how those product groupings should work in your store. You should figure out your entities and the relationships between them, and only then design tables.

Your other entities, user and order, need definitions of the relationships between them too.