0

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.

  1. As my order number will be the unique primary key, I think its illogical to repeat order numbers for each product.

Like,

S01 | X | a | 1 | 100

S01 | X | b | 2 | 200

...

  1. 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.

Like,

S01 | X | a,b,c | 1,2,3 | 100,200,300

  1. 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.

Like,

Table a

S01| X | 1 | 100

Table b

S01| X | 2 | 200

...

So, whats the most optimized and intuitive way to store such data and retrieve it.

  • Need a related dependent table SaleDetails. Relationship is many-to-many (sale can have many products and each product can associate with many sales). This requires a junction table - SaleDetails. Primary keys for Sales and Products will be saved as foreign keys in SaleDetails - each pair would be unique. – June7 May 15 at 19:52
  • Customers, Orders, orderdetails is used as a common tutorial so look around for one. In my opinion, the easiest one to find is the northwind database. Open access and download the northwind traders template and work your way through understanding how the northwind database works. – mazoula May 16 at 6:47
  • @June7 , i understand the junction table design, but for my scenario, a single sales order can have as many as about 20 products. So, in junction table SaleDetails, there will be 20 rows for one orderID, now consider I will have about thousands of orders each month, won't this create a huge SaleDetails table ? Is this sound practice ? – Dave127 May 16 at 14:20
  • Yes, it is sound practice. – June7 May 16 at 16:51
  • @June7, thanks for helping, I implemented the junction table and its more organized now. – Dave127 May 17 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.