How to store multiple items in a database column?

Question

In my project the employee inserts a table number, selects all the items the customer orders and saves that to a database. I have three tables:

Employee(empId, firstname, lastname)
Orders(orderId,tableNum,empIDFK,itemIDFK,totalPrice) 
Item(itemId,itemName, itemPrice)

My problem is that if the employee puts more than one item in the order in only saves the last item in the itemIDFK column. How do I go about attaining the id of all the items that the employee entered?

enter image description here

Here is some example code, mostly all the buttons have code similar to this:

 private void chickenbuttonActionPerformed(java.awt.event.ActionEvent evt) {                                              
    // TODO add your handling code here:
     try{
     st = connection.createStatement();   
    String query;
    query = "SELECT itemName, itemPrice FROM item WHERE itemID = '14446'";
    String itemName = " ",itemPrice =" ";  

      ResultSet rs = st.executeQuery(query);

       if(rs != null){
        while(rs.next())
        { 
         itemName = rs.getString(1);
         itemPrice = rs.getString(2);
        }
     model.addRow(new Object[]{itemName, itemPrice});
      total+= Double.parseDouble(itemPrice);
       String format = formatter.format(total);
       totalField.setText(format);
       }

       //inserts corresponding item id in itemIDFK 
      String query2 = "Update orders SET itemIDFK = '14446' Where tableNum =  " + tableNum;
      ps= connection.prepareStatement(query2);
      ps.executeUpdate();
         } catch (SQLException ex) {}

SOfanatic · Accepted Answer · 2013-04-28 21:23:22Z

5

What you want to do in this case is add another table, say OrderDetails, and this table would have:

PK: Id
FK: OrderId
FK: ItemId

So then you can add multiple items to an order. There would be a one-to-many between Order and OrderDetails and many-to-many between OrderDetails and Items

answered Apr 28, 2013 at 21:23

SOfanatic

5,5736 gold badges40 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JudgementFlame Over a year ago

Would there be a way to specify the one-to-many and many-to-many relationships in MySql? What would each of the foreign keys be referencing?

SOfanatic Over a year ago

Take a look at this post, it shows a detail example on how to create table relationships. stackoverflow.com/questions/260441/…

Zim-Zam O'Pootertoot · Accepted Answer · 2013-04-28 21:25:16Z

2

SOfanatic's answer is the correct way to do this. If for some reason you can't add another table, then itemIDFK will need to hold a delimited list of items such as itemId;itemId;itemId (or itemiditemIditemId if the ids are fixed width) - the problem being that this is much more difficult to query

answered Apr 28, 2013 at 21:25

Zim-Zam O'Pootertoot

18.2k4 gold badges45 silver badges71 bronze badges

Comments

Xubo Zhao · Accepted Answer · 2021-02-14 02:13:41Z

0

MySQL does have one data type Serial. So, there is another way to implement the order with multiple items, and item with different quantity.

It is possible to implement an Arraylist<>, serialize and unserialize the data in and out of a single column.

answered Feb 14, 2021 at 2:13

Xubo Zhao

1

Collectives™ on Stack Overflow

How to store multiple items in a database column?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related