Help with relationships and normalization

[gmagerr]

Good afternoon guys, i need some help with my database design, i made a webpage because i thought it would be easier, i had to include a picture. anyway this isn't a scam to get people to ckick on my webpage, i sincerely need the help. here's the page

http://www.geocities.com/gmagerr/Index.html

 

[Gazer44]

If you right click on the join lines and EDIT relationship.

As long as the Primary & secondary fields are of the same type you an tick all 3 boxes- (ref integrity, and the 2 cascade fields)

This will make sure all your tables are kept up to date and any deletes/updates are made throughout the joined tables !

 

[Quintios]

The first thing I notice is that apparently you can only have one item per order? If you can have multiple items, perhaps you'll need another table to represent the individual items in an order. In fact, I would suggest perhaps using this format instead for your tables:

tblCustomers           tblOrders             tblOrderDetails
------------           ---------             ---------------
CustomerID (PK) --->   CustomerID            ItemID (CPK)
                       OrderID    (PK)---->  OrderID (CPK)

Use a separate table for Order Details that has a composite key of the OrderID and the ItemID. Unless, like I said, you can only have one item per order, forever and always.

Also, I notice that you have CustomerID both in the Customers table and in the JobSite table, but there are no join lines. Is this duplicated data? Should there be a relationship between the two? If so, what is it? Will the 'Site Details' table provide the link between CustomerID and JobSiteID such that you don't need hte 'CustomerID' field in the JobSite table?

Check around for duplicate fields. Unless there is a relationship between two tables, you need to yank the duplicates out and find them a unique home.

From what I can tell (and I'm probably wrong, but correct me) is that a Customer orders a product. This requires a Job at the CustomerSite. An Employee is assigned to the Job at the CustomerSite. The details about the CustomerSite are held in the JobSite table. You have the details split out in the JobSite table as a Customer might order more than one product for more than one CustomerSite. The rest is just details about customers, employees, and products.

Is this kind of how things go?

Onwards,

Q-