Purchase order Database In SQL ??

[boomer32]

This is in the design of a purchase order database I have 3 tables that were given to me by my accounting people I am new to sql...I think that the tables need to be more tables and I am not sure what to do ...can you all give me some advice...I have been looking at the northwind db example and i am comfuse with my flow...here are these tables help me out you guys are usually great at help can you help me with my flow process as well.

tblvendors
Vendor
VendorID
Address1
Address2
City
State
Zip Code
Remit to address1
Remit to address2
remit to city
remit to state
remit to zip code
contact name
contact title
email
website

tblAssets
AssetId
AssetDescription
AssetCategory
VendorID
Model
ModelNumber
SerialNumber
DateAquired
DateSold
PurchasePrice
DepreciationMethod
DepriciableLife
SalavageLife
CurrentValue

tblPurchaseOrders
PO#
Vendor
VendorID
date
date requested
date promised
product ID
product description
account 1
qty ordered1
unit cost 1
product id 2
product description2
account 2
qty ordered 2
unit cost2
productid3
product description3
account3
qty ordered3
unit cost 3
product id 4
product description
account 4
qtyordered4
unit cost 4
product id5
product description 5
account 5
qty ordered5
unit cost 5

THis is it I hope you all can help. Thanks in advance and Merry Christmas to all

Boomer

 

[SQLSister]

Clearly your last table needs to be normalized. Anytime you see field names that consist of things like Unitcost1, unitcost2, etc. you are looking a needing a related table.
This would be the PurchaseOrderItems table. There is one purchase order, but many items might be on it. Therfore two tables are needed.

I personally might want to add the purchase order number to the assets table, so you can check to see if all the items ordered have been received. You might also want the current location in that table as sometime you are going to want to know where these assets are located.

Save yourself lots of work and make sure none of your fieldnames use spaces or are the same as SQL key words (like date) or use special characters which have meaning in SQL Server. Thes will alawys need to be placed in [] when you use them which is a big pain as you maintain the system.

All id fields are best if you can make them integer or big int and use autonumbering. Nummeric joins are much faster than alphanumerics. If at all possible don;t let them talk you into some arcane numbering system involving all sorts of calulations. These systems are prone to inefficieny and error and should be avoided.

Questions about posting. See faq183-874

 

[boomer32]

Thank you for you quick response this will help...any suggestions on how to understand the flow of all of this ...so i can put it together...I am making a form in cold fusion with in our internal network..so that approved individuals can create po's..do you have or know of any example forms or databse examples with the fields you suggested so i can look at just how to normalize ? I have searched the internet intensively and nothing there.

Thanks again SQL Sister