mitdaniels
asked on
Too many ID's or not really??
I have a small database that I have normalized to about 3NF, but am finding about half of the tables have mainly foreign key ID's, and the actual data are stored in the other half of the tables. Part of the structure is hierarchical.
I am finding that I need to do JOINS for almost every type of query. Is it okay to have to do as many joins as necessary? Here is an example of the types of JOINS I am talking about:
SELECT table1.table1ID, table2.columnName1, table2.columnName2,
table3.table3ID, table4.table4ID, table5.table5ID,
table6.columnName1, table7.columnName1
FROM table1
INNER JOIN table2
ON table1.columnName1ID = table2.columnName1ID
INNER JOIN table3
ON table1.table3ID = table3.table3ID
INNER JOIN table4
ON table3.table4ID = table4.table4ID
INNER JOIN table5
ON table4.table5ID = table5.table5ID
INNER JOIN table6
ON table5.table6ID = table6.table6ID
INNER JOIN table7
ON table6.table7ID = table7.table7ID
INNER JOIN table8
ON table7.table8ID = table8.table8ID
INNER JOIN table9
ON table8.table9ID = table9.table9ID
I also find that I am finding it hard to test whether the data is correct since I have to follow the ID's to their actual data. I have made great success, but am also wondering whether there is an efficient way of dealing with all these ID's?
I am finding that I need to do JOINS for almost every type of query. Is it okay to have to do as many joins as necessary? Here is an example of the types of JOINS I am talking about:
SELECT table1.table1ID, table2.columnName1, table2.columnName2,
table3.table3ID, table4.table4ID, table5.table5ID,
table6.columnName1, table7.columnName1
FROM table1
INNER JOIN table2
ON table1.columnName1ID = table2.columnName1ID
INNER JOIN table3
ON table1.table3ID = table3.table3ID
INNER JOIN table4
ON table3.table4ID = table4.table4ID
INNER JOIN table5
ON table4.table5ID = table5.table5ID
INNER JOIN table6
ON table5.table6ID = table6.table6ID
INNER JOIN table7
ON table6.table7ID = table7.table7ID
INNER JOIN table8
ON table7.table8ID = table8.table8ID
INNER JOIN table9
ON table8.table9ID = table9.table9ID
I also find that I am finding it hard to test whether the data is correct since I have to follow the ID's to their actual data. I have made great success, but am also wondering whether there is an efficient way of dealing with all these ID's?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much, you've given me a few good pointers. I will see how to apply those to my database.
ASKER
Do you have an example?