skip to Main Content

Here my design database, I working on E-commerce project
enter image description here

So when the admin create a new product its have new Id and the user have to select the color and size for the product. The problem is when create a new product (I set Product’s Id is Identity Increment) I enter the same data as the same name, same size and color, etc. just like the same product I imported earlier example Thats mean the product i just created and the previous product are different but in fact is just one product . Is there any solutions about this problem?

Is my design wrong?enter image description here I want when the user create like the product but with same attributes its will be the error and not allowed to save (btw I code in ASP net Core)

2

Answers


  1. Some suggestions:

    Don’t use "Name" or "Id" as column names. Instead use product_name, color_name, size_name, product_id, color_id, size_id.

    Remove amount from the product table. Create an new inventory table with inventory_id, product_fk, amount, container_type, container_location, insert_date, expiry_date, stocker_id.

    Each table should have an auto-incrementing primary key that is hidden from the users. product_pk, inventory_pk, color_pk, size_pk.

    validation of inserts and edits should be handled via your application and only via database constraints as a final safety net. The table designs and relationships should not be used to prevent duplicates because you may one day have an example were a duplicate is needed.

    There are many database design best practices you should be aware of before starting a project. See https://www.sisense.com/blog/better-sql-schema/

    Login or Signup to reply.
  2. If you are looking to block duplicates (in regards to a select number of columns) you have a few different ways to do it.

    The basic ones would be:

    1. UNIQUE CONSTRAINT.

    Will block any attempts to add an existing combination of the columns.

    ALTER TABLE TABLE ADD CONSTRAINT unique_combo UNIQUE (column1, column2, ...) 
    
    1. Hide the INSERT behind a STORED PROCEDURE that checks for existing combination of the columns.

    Now design wise I would strongly suggest to rework your database design and think about things like if amount makes sence on a product level if you can have multiple sizes…

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search