-
Jun 3rd, 2021, 09:42 AM
#1
Thread Starter
Addicted Member
How to insert multiple records against single order id/ primary key
Hi. When I purchase medicine or any grocery from the shopping mart. So the final receipt which i receive has an against which there are several items which I have purchased. Similarly Against a patient MR No. He/ she gets multiple tests or medicines which has the same idea of having several records against 1 Primary Key.
How it happens?
I'm learning web development and this is absolutely for my practice to working on different tables and web pages to improve my practice.
In my case: I have created a cart table where the sales man keeps adding the items which is purchased/ ordered by the customer. Its basically a kind of shop where the walking customers come, purchase, pay and take away their orders or dined in. On btnAdd the items are added in cart table where Cart_ID is the PK. What I want that Once the customer finishes his purchasing, so the sales man clicks the Sale Button and the data goes from CART table to the Orders Table, leaving the Cart table always empty.
Question?
1. what I want that just like the above example of MR No. or Order ID I want that all these items of the customer are stored in the Order Table against single Primary Key/ Order_ID.
My Cart Table:
cart_id (PK)
item_id (FK)
item_unit_price
order_qty
order_price
order_of
order_instructions
order_date
token_id (FK)
Each customer is given a token in the shop which he submits back when he takes away the order or he is dined in.
My order Table
order_id (PK)
item_id (FK)
item_unit_price
order_qty
order_price
order_of
order_instructions
order_date
token_id (FK)
Here is my Sale Table:
sale_id (PK)
order_id (FK)
total_payable
total_paid
sale_discount
sale_date
user_id (FK)
If my question needs explanation so I'm here to give more info.
-
Jun 3rd, 2021, 10:27 AM
#2
Re: How to insert multiple records against single order id/ primary key
your design is wrong.
You should have an Order table that holds a single order record:
Code:
orderID - pk
customerId - fk
dateOfOrder
orderTotal
... more fields as you need ...
Then you should have another table that then holds the details:
Code:
tblOrderDetails
id - pk
order_id - fk
item_id - fk
qty
price
instructions
... more fields as needed ...
Now when you want to save it, you insert into order table, get back the ID, then insert into order details using the order id you got in step 1.
-tg
-
Jun 3rd, 2021, 11:49 AM
#3
Thread Starter
Addicted Member
Re: How to insert multiple records against single order id/ primary key
@tg
Do you mean that make 2 order tables:
1. Order Master
2. Order Details? Right?
Secondly, Do you mean that Sale table will be removed and sale info will be stored in both order tables? Right?
-
Jun 3rd, 2021, 02:32 PM
#4
Re: How to insert multiple records against single order id/ primary key
Yes... two tables... Order and OrderDetails ... The ID then comes from the Order table... and inserted into the OrderDetails when you add items to it.
As for Sales... maybe, maybe not... if all of the info from it is moved to one of other tables, then sure, you wouldn't need it. Or, you keep it, and it too would have a FKey to the Order table. I didn't address it because it wasn't the source of the problem, your current Order table was. You had OrderID as the PKey, but then were inserting detail lines into it, giving each one a new OrderID, which isn't what you want.
To be honest... your Sales table should be the parent record... and the Order should be the child... if you take the Order_ID off of sales, and add Sales_ID to the Order table... that would probably solve it... Now everything is keyed off the Sales_ID - in short, you have it flipped.
-tg
-
Jun 3rd, 2021, 11:18 PM
#5
Thread Starter
Addicted Member
Re: How to insert multiple records against single order id/ primary key
[QUOTE
To be honest... your Sales table should be the parent record... and the Order should be the child... if you take the Order_ID off of sales, and add Sales_ID to the Order table... that would probably solve it... Now everything is keyed off the Sales_ID - in short, you have it flipped.
-tg[/QUOTE]
I'm not native English speaker. Can you explain it in more plain english. Pleaaassee.
Thank you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|