How to enter multiple foreign keys to one column

Question

Hi I am making a restaurant management system for an assignment and I have a customer table, item table and order table. I want to enter the customer ID and the item ID to the order table when a customer places an order. My question is how to handle this when one customer orders several items together, how can I insert this to the order table? Is there a way to enter the foreign key of multiple items to the item ID column of the order table?

I am using MySQL server 5.7, Java 1.8 and NetBeans 8.2

Thanks!

Question was nowhere related to java and netbeans. So removed tags — Ravi, Mar 12 '17 at 12:37
Questions is belong to SQL. But java programmer have to design the system also. — SANTOSHKUMAR SINGH, Mar 12 '17 at 12:40

SANTOSHKUMAR SINGH · Answer 1 · 2017-03-12T12:44:10.200

0

You need two table. Order and Order Details.

Order Table will contain Order ID, Customer ID, Date, Total Price, Tax, Offer, Coupon and other details regarding order. And Order Details will contain Order ID, Item IT, Price, Quantity, Discount, and other details regarding each order item.

edited Mar 12 '17 at 12:44

answered Mar 12 '17 at 12:37

SANTOSHKUMAR SINGH

476
3
11

Yes. Order Table will contain Order ID, Customer ID, Date, Total Price, Tax, Offer, Coupon, And Order Details will contain Order ID, Item IT, Price, Qunatity – SANTOSHKUMAR SINGH Mar 12 '17 at 12:42
1

how can you tell exact data model requirement ?? OP has not given any clue here and you provided all the column names :D – Ravi Mar 12 '17 at 12:47
He need to change the column according to his requirement. I have given general information. Not any specific requirement for restaurant management system. – SANTOSHKUMAR SINGH Mar 12 '17 at 12:51
What will be the primary key of Order Details table? – Devmin Abeyasena Mar 12 '17 at 14:41

score 0 · Answer 2 · answered Mar 12 '17 at 12:38

0

Entry in Order table should per user not per Item. And if you want to insert items their are two ways one is in Order table as meta with order entry and second is create another item table with order id as ref key.

answered Mar 12 '17 at 12:38

Bhuvnesh

44
5

How to enter multiple foreign keys to one column

2 Answers2