Database design check

C

CC

Hi! I'm designing my first database, and I'm having a hard time because I'm
afraid my tables aren't set up right. It's a basic orders database to enter
and track orders and inventory. I've looked through the Access templates for
orders and inventory databases. I know this is a lot to look through, but
everyone here is so helpful, and I'd appreciate any help or suggestions you
can give. I have some customers with 50 different ship addresses, which is
why there is a separate table for those. Also, some of my products have
several parts to them, so that's why there are separate inventory tables set
up. Here are the tables I've set up:

TblCustomers
CustomerID (pk)
CustomerType (fk)
CompanyName
PaymentTerms (fk)
ContactFirstName
ContactLastName
Address1
Address2
City
State
Zip
Country
Phone
Fax
Email

TblCustomerType
CustomerType (pk)

TblPaymentTerms
PaymentTerms (pk)

TblOrders
SalesOrder (pk)
Customer ID (fk)
PONumber
OrderType (fk)
OrderDate
RequiredDate
ShipToID (fk)
ShipDate
ShipMethod

TblOrderType
OrderType (pk)

TblProducts
ProductCode (pk)
ProductName
ProductDescription
ProductCategory (fk)
UnitPrice

TblProductCategory
ProductCategory (pk)

TblPartsInventory
PartNumber (pk)
PartName
PartDescription
PartQty

TblProductInventory
ProductInvID (pk)
ProductCode (fk)
PartNumber (fk)
ProductInvQty

TblShipMethod
ShipMethod (pk)

TblShippers
Shippers (pk)

ShipTo
ShipToID (pk)
ShipFirstName
ShipLastName
ShipCompany
ShipAddress1
ShipAddress2
ShipCity
ShipState
ShipZip
ShipCountry
ShipPhone
ShipFax
 
C

CC

Sorry - forgot the OrderDetails table:

TblCustomers
CustomerID (pk)
CustomerType (fk)
CompanyName
PaymentTerms (fk)
ContactFirstName
ContactLastName
Address1
Address2
City
State
Zip
Country
Phone
Fax
Email

TblCustomerType
CustomerType (pk)

TblPaymentTerms
PaymentTerms (pk)

TblOrders
SalesOrder (pk)
Customer ID (fk)
PONumber
OrderType (fk)
OrderDate
RequiredDate
ShipToID (fk)
ShipDate
ShipMethod

TblOrderType
OrderType (pk)

TblOrderDetails
SalesOrder (fk, pk1)
ProductCode (fk, pk2)
UnitPrice (fk)
Qty
EndUser (pk3)

TblProducts
ProductCode (pk)
ProductName
ProductDescription
ProductCategory (fk)
UnitPrice

TblProductCategory
ProductCategory (pk)

TblPartsInventory
PartNumber (pk)
PartName
PartDescription
PartQty

TblProductInventory
ProductInvID (pk)
ProductCode (fk)
PartNumber (fk)
ProductInvQty

TblShipMethod
ShipMethod (pk)

TblShippers
Shippers (pk)

TblShipTo
ShipToID (pk)
ShipFirstName
ShipLastName
ShipCompany
ShipAddress1
ShipAddress2
ShipCity
ShipState
ShipZip
ShipCountry
ShipPhone
ShipFax
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top