We create applications that mimic EC sites.
I would like to normalize the product table.
Currently, the table configuration is as follows.
1 id
2. Product name
3. List Price
4. Number of stocks
5 Date of arrival
6. Number of items in stock
7 Sales
In my opinion, the arrival date and number of items in 5 and 6 are transaction data that are expected to be added multiple times, so it should be managed in a separate table.Otherwise, the product name and list price will be duplicated.
On the other hand, 4 and 7 are types that update record values, not transaction data, but since they are variable data, I think it should be managed separately from the master data.
Is there anything wrong with my understanding so far?
Also, should I manage each of the 4 and 7 in a separate table?
As the number of sales increases, the number of stocks decreases, and I feel that it is always operated at the same time, but the color of the data is different.
I would appreciate it if you could give me your feedback and advice.
database database-design
"Depending on the requirements" is the first prerequisite.
1: For example, the possible scope of work is
·Authentication authorization (account and login) module
·EC order receiving subsystem
·EC shipping subsystem
·Inventory warehouse subsystem
·Approximate sales/totalization subsystem
·Accounting accounting or accounting linkage subsystem to another system
and so on.
Please clarify from where to where the system covers.
2: Even if you focus on EC order sub-systems,
·Whether it is a general purpose and functional EC or a system specializing in specific industries
·What about campaigns and coupons on the EC site
·How do you need to search for products? Is it okay to use one axis for product categories?
and so on.
Let's make assumptions about these as well.
Based on the assumption that I understand so far, I think there are generally two possible patterns of product master.
·Product catalog master corresponding to what is actually displayed on the EC site in EC order sub-system etc.
Depending on the requirements, a product selling price master, a product set master, etc. will be required as separate tables.
·Product master for inventory management subsystem.
If there are multiple warehouses, we usually have to think about them.
An inventory master has a warehouse ID, a commodity ID, a quantity, an allotable number, etc.
Quantity, availability, etc. must be consistent with the warehousing table, delivery table, and correction table after inventory
In the case of the inventory master, the quantity may be duplicated separately from the warehousing/delivery, but
(In theory, inventory can be calculated backwards from inventory correction, etc., but it is necessary to refer to multiple tables.)
I think it is very common for EC order receiving product catalog master to have sales volume.
(Because it can only be aggregated from the order table.)However, of course it depends on the requirements and table design.)
Again,
·For what industries
·Cover the entire system
Let's imagine if we're going to create an EC site.
As you said, I would split the table as follows.
Is it like displaying the number of stocks on a management screen somewhere?
If that is the case, I think the inventory is not always kept in DB, but rather aggregated and displayed in SQL.
(Number of stock per product ID) - (Number of sales per product ID) Can you calculate the number of stock per product ID?
On the other hand, if the amount of data is too large and it takes time to calculate each time, I think it would be better to create a table for inventory once a day at Batch.
© 2024 OneMinuteCode. All rights reserved.