Link to home
Start Free TrialLog in
Avatar of mitdaniels
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?
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mitdaniels
mitdaniels

ASKER

Thanks for the correction (surrogate keys).

Do you have an example?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks so much, you've given me a few good pointers. I will see how to apply those to my database.