Firstly, I suggest you identify your tables with meaningful names – this will help determine what they should contain and will make future support easier. Secondly, I think there may be need for a fourth table here, as I’ll show below.
Picking up from your 2NF group:
It appears that TABLE 1 is the Product table. I suggest you keep naming consistent and call the identifier Product Code (even though it’s always a sandwich in your case) and the price Product Price. This latter field is actually the unit price and not total price (where does the notion of ‘total’ come from? Is it perhaps the sum of the ingredients plus mark-up?). You might include other fields such as ‘shelf life’, ‘dietary details’ and so on in this table. If you were to include dietary details, it might be a candidate for a new table (I’m thinking of ‘Vegetarian’, ’Wheat-free’, ‘gluten-free’ and the like).
TABLE 2 appears to be the Customer table. If this is correct, then Order No and Date of Order don’t belong here. In this case Customer Number would be the table’s primary key. Here you could include things like telephone number, primary contact name etc. However, if you have multiple contacts, their details would be split out into yet another table (this is not the additional table I referred to above).
TABLE 3 seems to be the Orders table. As shown, Order No is the primary key here and Order date would also belong in this table. It would contain the foreign key towards the Customer table to which it is linked. You might include ‘fulfilment date’ in this table if it’s different from the order date (depends whether you’re taking orders in advance of the delivery date).
Now here’s where the fourth table would fit in. An order would have many items, so I’d suggest an Item table. This is different from the Product table, as the latter is the generic sandwich detail, where item is a specific instance of this generic item which is in turn attached to an order. The item table would contain foreign keys pointing to Product and Order tables and would have the price (as sold in the order to which it relates) . Additional fields here might be ‘use by’ date, which is derived from the ‘shelf life’ of the Product table.
To summarise, the tables would look like this:
Product {table name}
Code {table Primary Key}
Description
Price
Shelf Life
Dietary details (candidate for FK to another table)
Customer {table name}
Id {table PK}
Name
Address
PostCode
… and you could add in telephone number, name of contact person etc.
Order {table name}
Order Id {table PK}
Customer Id {Foreign Key to Customer table}
Order No
Order date
Fulfilment date
Item {new table name}
Item No {table PK}
Product Id {FK to Product table}
Quantity
Total Item Price
Use by date
Without knowing your business model, it’s hard to suggest the best way to structure the tables. For example, is your business wholesale to customers for their cafeteria supply, or retail to the company staff as individuals? I’m assuming the former. Is the business entirely wholesale, or is there a mixture of retail included? If mixed, you might consider discount codes for wholesale deliveries for example in the customer table (this thought leads to a plethora of other things to include!).
A final thought: aim to keep your field names consistent throughout each table and avoid using spaces in them. That will make life easier as you develop the DB.
