RE: Cost of joins?

[Robert M (Bob) Bartis]

I have a question that may be similar to the one which Margaret asked recently concerning
the "Cost of Joins". I have a DB with numerous tables and have inserted keys to relate
one table to another. The method minimizes the data I store, but results in me joining
multiple tables, sometimes 10 at a time to retrieve information needed to satisfy a given
search request.

A simple version of my DB would be:

Table one
	oneKey
	a
	c
	b

Table two
	twoKey
	oneKey-Foreign key
	e
	f
	g

Table three
	threeKey
	twoKey-Foreign key
	x
	y
	z

If I want to collect data concerning x, y, z and its relation to 'a' I need to join tables
one, two and three. It seems to me this is the most efficient storage of information. It
also, assuming the resulting queries return a large number of records, is the most
efficient for end users when moving from record to record. Conversely, it also seems like
it will be the most inefficient while waiting for the query results to be calculated?


I've noticed another solution proposed by some is to carry forward Foreign Keys. For
instance:
Table one
	oneKey
	a
	c
	b

Table two
	twoKey
	oneKey-Foreign key
	e
	f
	g

Table three
	threeKey
      oneKey-Foreign key
	twoKey-Foreign key
	x
	y
	z 

In this case collecting the same information (x, y, z and its relation to 'a') I need only
join tables one and three or just three and do look-ups into table one. Obviously, the
issue scales if you add 10 tables into the equation. 

This method appears less efficient from a data storage perspective and complicates the
application. I need to store multiple Foreign keys each time a record is added to a given
table. The time to return query results would appear to be very short as each query would
only return a single record, but the record to record movement would result in a new
query each time.

What advantages or disadvantages are there to one method vs. another?

Bob