Database for Orders/Inquiries with “custom” Product Data

database-design

At the moment i practice with SQL Databases and created a simple Python tool to manage Order and Inquiry with a simple GUI for testing.

Now I have a question i didn't found a good solution for:

There are standard values for Products like a price and costs etc. These could vary from Order to Order (volume effects etc.).
So i need to save them on a different place.

What is a common way to do that?

Having an extra table for Products used in Orders where i copy all standard values from the Product Database and change them there if the price or anything needs to be changed in an order?

Best Answer

Yes, If you create an extra table for storing ordered product information that would be helpful to you.

Make sure you insert all entries in orderedproduct table with reference to order id so it would be easily selected as and when we require all order details with product information.

Related Solutions

Optimum design / structure for product data database

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attriutes
Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)

Class- and instance- type rows

It is a well known pattern. If you want extensive literature on this topic, search on "temporal database".

Best Answer

Related Solutions

Optimum design / structure for product data database

Class- and instance- type rows

Related Question