Tables
Product table
ProductID
ProductName
ProductCode
ProductCategory
......
OrderItem table
OrderItemID
OrderID_FK
ProductID_FK
ItemQuantity
Order table
OrderID
OrderCustomerID
OrderProductID
OrderDateOrdered
OrderAuthorisedBy
OrderCustomerComments
OrderEmployeeComments
OrderStatus
....
Then I have a discount table for certain categories, but thats another story
