Storing Multiple Values (Foreign Key References) in One Column Oracle-CodePudding

I am trying to design a database for a restaurant. I have my restaurant items that make up a restaurant order. One order can contain many items. Within the order I want to reference multiple items (foreign key). Below are sample data from the tables.

Is there a way to store multiple ITEM_ID in the Orders table referencing the Items PK ITEM_ID?

CodePudding user response：

There is, but - you should normalize data model (so - you shouldn't do what you meant to). "Solution" is to create a new table, e.g. ORDER_ITEMS:

create table order_items
  (order_id  number constraint fk_oi_order references orders (order_id),
   item_id   number constraint fk_oi_item  references items  (item_id),
   --
   constraint pk_oi primary key (order_id, item_id)
  );

It would contain combination of order ID and all items it contains.