0

Hi I am having trouble trying to normalise my tables, I would really appreciate some help! So far I have:

Customer Table
CustId(pk)
Cust Name
Cust Address
Cust Postcode
Cust Email

Order Table
OrderID (pk)
Order Date_Time
CustID(fk)

OrderDetails
DetailsID(pk)
Quantity
OrderNo(fk)
DeliveryID(fk)

Product
ProductID(pk)
ProductType
ProductDescription
DetailsID(fk)
PartID(fk)
Price
VatID(fk)

Part
PartID(pk)
PartName
PartDescription
SupplierID(fk)

Vat Table
VatID(pk)
VatRate

Inventory
InventoryID(pk)
Inventory Quantity
PartID(fk)

Supplier
SupplierID(pk)
Supplier Name
Supplier Address
Supplier PostCode
Supplier TelNo
Supplier VatNo

Delivery Table
DeliveryID(pk)
DeliveryDate

Help would be gratefully appreciated!

3
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by Web Dev Rob
0

This looks like a good start.
Make a list of all business transactions and check if you can map them to your database structure in all details.
I do not understand the relation between orders, parts and products. What is a part? How does it relate to products? How many parts are there for one product?
The OrderDetails table lists OrderNo as a foreign key. This should probably read OrderID.
Where do you store the ordered items? In the order details table? Then you need a foreign key into products or parts.

0

Yes, as above, would need some more info on your order items table. A common way of storing orders and items is to have two tables - an order header table and order items:

OrderHeader
order_id (PK)
customer_id (FK)
order_date
etc.

OrderItems
order_item_id (PK}
product_id (FK)
quantity
attributes

For the OrderItems table you may also need to note your business rules in relation to the product_id foreign key. E.g. with the above your order info screen would change if the original product id was altered. Some business rules require the product_id not to be stored and original info (E.g. if the price of product ID 22 changed, you wouldn't want this updating in past orders query).

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.