Hey i am just a little confused. I am creating a system where customers order items and i have just realized that i cannot add more than one item per order how do i get around this i am using sql and vb
your question is a bit vague, but from what I understand, I would have two tables - one for order numbers, order date, name of person ordering etc, and a second table recording the items ordered. Use the order number as a foreign key in the second table.
I would start with the following tables and add fields as required. The Items table is your basic inventory of available items that can be ordered (one record per item). The Orders table contains a unique (program generated) order number plus some other info that is common to the entire order. You may want to add a customer name (or ID), shipping address, etc. The Order_Details table contains the list of ordered items. Fields marked with "*" are primary keys. Note that the details table has a compound key because you need both the order number and the item number together to uniquely identify a record.
You don't need to store the item cost (unit price * quantity) because this can be calculated in the query. Although you may want to include the tax rate in the Orders table as this may change depending on the order date.
you have to use simple head detail concept or you can call it master detail concept . now here is a sample layout of your master - detail or head- detail tables
1- headid PK
now in your head table you can save the records which are not repeating in your orders , for example , your customer will be one in one order , your order id will be same , and in detail , you have so many products which are ordered by the customer , by using this style you can order more products at the same time. hope this will give you an idea .
If I follow your table naming, it looks like you should move CustomerID from TblSales to TblOrderLine because there is only one CustomerID per order and therefore no need to store it multiple times. I presume TblOrderLine contains one order per record in which case the name is confusing because it implies that it is a line-item table (containing one record per each line in the order).
As I suggested earlier, it is best to do the calculations of things like totalPrice in the query. I know storage is cheap but there is no need to waste space in the database for fields that can be easily calculated on the fly.
Also, tblStock containis the fields
You should have a separate table that maps supplier names to a supplier ID. There are times when it may be desirable (for performance reasons) to duplicate data, but in general it is better to normalize your tables.
Let's simplify the jargon a little. You don't "project" items from the stock table on the sales table. That is an unnecessarily vague term. I presume what you mean is "I want the user to be able to pick items from our inventory and create an order". If that is the case then one approach is to do the shopping cart method in which the user browses the inventory and selects items and quantities (at this point the user is told when an item is out of stock). As items are selected you build a list (collection) of items IDs and quantities. At any point the user can review his current list. Also, as items are selected, the quantities can be temporarily subtracted from the inventory (to prevent collisions with other users). A reasonable method of maintaining the shopping cart is to have another database table containing the in-progress ordering items. Once the user commits the order you can
1) insert one record into the orders table
2) insert one record per item into the orders detail table
If the user, instead, cancels the order, you can re-add the items back into the inventory. If the shopping cart table has the same structure as the order details table then committing the order is a simple matter of
insert into Order_Details select * from Order_Working where order_ID = ...
Then, of course, you have to delete those records from the working table.