Check this for info about normalization:
http://www.bkent.net/Doc/simple5.htm
As to Foreign Keys.
Typically each major data entity has a KEY...a unique identifier, usually a simple integer. Often these KEY FIELDS are given the label ID as
Customer.ID
Order.ID
OrderItem.ID
Part.ID
etc.
Let's suppose you had an order for a customer. You'd create a new Order record with ID 111. The customer id for this order is 999.
In the Order table, you'd have set up a column Order.CustomerID
For Order record 111, CustomerID would be 999.
CustomerID points to a KEY in a different table...the Customer Table. The Customer Table is 'foreign'. So CustomerID would be a 'foreign key'.
When you graphically map out a database, you'd draw a line from Order.CustomerID to Customer.ID to show the foreign key relationship.
A foreign key suggests that a JOIN will be useful.
SELECT name, address
FROM customer, order
WHERE order.customerid = customer.id
and order.id=111
There are several kinds of data relationships:
Shorthand is:
1 to 1
1 to Many
Many to 1
Many to Many
Examples:
1 to 1: for each record there is one and only one related record
example: you set up a table for ShipTo Address. Each customer must have 1 and only 1 ship to address.
SELECT * FROM ShipToAddress WHERE CustomerID=999
would return 1 record
1 to Many: 1 customer will have 1 to any number of orders.
SELECT * FROM Orders WHERE CustomerID=999
will return at least 1 and potentially many records
Many to 1: This is flip side of the 1 to Many. The many orders will each have 1 and only 1 customer. You can use this info to do reporting of Sums, averages, counts, etc, grouping by Customer Records
SELECT CustomerID, SUM(ordervalue) FROM order GROUP BY CustomerID
will return return 1 record for each order customer showing the sum of all that customer's orders.
Many to Many:
Sometimes there are multiple relationships between entities:
For example: Many OrderPackers pack each order.
OrderPacker.ID 1 is Joe
OrderPacker.ID 2 is Lourdes
OrderPacker.ID 3 is Howard
Order 111 is packed by packer 1
Order 112 is packed by packer 1 AND packer 2
Order 113 is packed by packer 1 AND packer 2 AND packer 3
Order 114 is packed by packer 1 AND packer 3
Order 115 is packed by packer 2 AND packer 3
Order 116 is packed by packer 2
Order 117 is packed by packer 3
How to show that MANY packers packed MANY orders?
For this you create a "RESOLVING" table.
Typically a resolving table consists of only 2 fields, both of which are foreign keys. Remember Foreign Keys?
Resolving tables are often named by combining the names of foreign key tables: in this case I'll call the resolving table OrderPackerOrder.
The OrderID field is a foreign key pointing to Order.ID
The OrderPackerID field is a foreign key pointing to OrderPacker.ID
OrderPackerOrder
OrderID , OrderPackerID
111,1
112,1
112,2
113,1
113,2
113,3
114,1
114,3
115,2
115,3
116,2
117,3
Many to Many tables rely on Joins to be useful:
SELECT order.id FROM order, OrderPackerOrder where orderpackerorder.PackerID=1
Would return the ids for the 5 orders packed by Joe
SELECT packerID FROM order, OrderPackerOrder where orderID=115
Would return ids for Lourdes and Howard.
Of course, sometimes you might have occasions where there is NOT NECESSARILY related record. This case is shown as 0,1...a 0,1 to Many or Many to 0,1 relationship. If there IS a related record, there will only be one...but there might not be any.
For example, you've created a customer record, but that customer has not yet placed an order.
This adds complexity. It leads to understanding the Outer Join.
