javamysqlsqlforeign-key-relationship

How to store multiple items in a database column?


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) {}

Solution

  • 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