Here my design database, I working on E-commerce project
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 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? 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
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/
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:
Will block any attempts to add an 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…