Cindy-
And, I had always heard that lazy people make great programmers. :)
Actually I was extapolating (Is that the right word?) from a statement you made earlier that you didn't see how I found a simple 3 table join complicated.
Take the 3 table join you were referring to and add two more table joins and now you have 5.
But, I take it from this that your preference would be to store the id.
Another point. Some people for reporting purposes like to store IDs into tables to simplify joins at a later time, even though the IDs don't really belong.
I'm having difficulty coming up with a good example. But let's say work orders had a customer Id and in the Invoice Header you stored the Work order Id. To get at the customer information from the Invoice Header table you would have to join to the Work Order table and then to the Customer table.
So by adding a Customer Id, you would simplify the join to the Customer table.
>Dan,
>
>I don't know how you get 5 tables, unless you're including things like orders and order items. Remember, the lazy man takes the hardest way.
>
>*!* Table1 Customers
>*!* Example: 1; Winegarden, Cindy, 27; NC
>CREATE TABLE Customers (CustID, I, CustomerName C(x), CustomerCityID I, CustomerStateID C(2))
>
>*!* Table2 Cities
>*!* Example: 27; Durham
>CREATE TABLE Cities (CityID I, CityName C(x))
>
>*!* Table3 States
>*!* Example: NC; North Carolina
>CREATE TABLE States (StateID C(2), StateName C(x))
>
>SELECT ;
> Customers.CustID, Customers.CustomerName, ;
> Cities.CityName, ;
> States.StateName ;
> FROM Customers ;
> LEFT JOIN Cities ON Customers.CityID = Cities.CityID ;
> LEFT JOIN States ON Customers.StateID = States.StateID
>
>
>>Let me play Devil's advocate. Sometimes normalization leads to having city lookup tables and state lookup tables. Some like to store the Id which makes it easier to to updates. Some prefer to store the city and state directly.
>>
>>The problem with the IDs is that instead of a simple 3 table join, you now have a 5 table join, just to bring in the city and state names.