I have created an accdb with customers and products tables. Now, I want to know how to store the sales order details in the Sales table, WHEN each sale has multiple products, each with different quantity and price.
Like Order S01 -> Customer X -> Products a, b, c -> qty 1, 2, 3 -> price 100, 200, 300
I am planning to insert and update sales details from my excel sheet.
- As my order number will be the unique primary key, I think its illogical to repeat order numbers for each product.
S01 | X | a | 1 | 100
S01 | X | b | 2 | 200
- Currently, I have implemented this as comma-separated values in a field, but it seems it is not a good practice to store like this. It involves lots of unnecessary scripting to unwrap and wrap the data and not at all intuitive.
S01 | X | a,b,c | 1,2,3 | 100,200,300
- I cannot create a separate table for each product to store the sales and purchase data(if this is called normalization). Too many tables, not good practice.
S01| X | 1 | 100
S01| X | 2 | 200
So, whats the most optimized and intuitive way to store such data and retrieve it.