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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
