Need help with DB

[Farhan_B]

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

thanks

[Begginnerdev]

Create an orders table to store the meta data of the order, and then store the order id in the customer table.

[toomutch]

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.

Toomutch

[Farhan_B]

would you have a sample by any chance i just want to compare my idea with what you recon
thanks

[Begginnerdev]

---Table Name: Orders--
-Or_ID- -Or_date- -Or_Amnt-
1 11/11/2011 204.45

[Farhan_B]

would i then use a query or somthing similiar for it to calculate the total amount

[Reverend Jim]

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.

Items

  *Item_ID 
  Description
  Unit_Cost

Orders
   
  *Order_No
  Order_Date

Order_Details

  *Order_No
  *Item_ID
  Unit_Cost
  Quantity

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.

[M.Waqas Aslam]

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
head/master table
1- headid PK
2-orderdate
3-orderid-
4-customerid

detail table
1-detailid PK
2-headid FK
3-productid
4-qty
5-rate
6-total

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 .

Regards

[Farhan_B]

so from what i have gathered for my system the tabels go as followes#

tblStock
StockID
itemName
itemDescription
supplyNName
supplierID
OnHand
OnOrder
Branchid
Price

TBlOrderLine

StockId
salesID
date

TblSales
SalesID
CustomerID
itemCode
itemName
Qty
unitPrice
totalPrice

the ones in red are my primary keys . am i following the correct path

[Reverend Jim]

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

supplyNName
supplierID

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.

[Farhan_B]

ok what i am trying to do is
the stock table shows how much stick i have
tbl sales is the sales order table

now i need a way in which a user can take several items from the stock table and project them on the sales table. how would i do this ... or am i taking the wrong approach

[Reverend Jim]

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.

[Farhan_B]

would anyone have any codes which i can view to see how i can make my sales part work i really need help here please

[Reverend Jim]

As far as I can see, you haven't done any actual work. Please show us what you have done so far.