one big tables vs. many smaller

what r the advantages / disadvantages of this?

sample:

Main (pk, id, c1, c2)
Sub1 (pk1, fk1, c10, c11)
Sub2 (pk2, fk2, c20, c21)

rows:

Main: 1,1,2,3
Sub1: 1,1,4,5
Sub2: 1,1,6,7

vs.

BigMain(pk,id,c1,c2,c10,c11,c20,c21)

rows:

1,1,2,3,4,5,null,null
2,1,2,3,null,null,6,7

the rows are always read as when Main and Subx would be inner joined

obvious is, that BigMain has many null values (there are up to 10 sub tables) but u do not need to join Main and Subx

i would use Main + Sub, coz it is better to maintain and to tune, but what about the costs of the join?

both tables may contain millions of rows

what r ur options? Received on Thu Apr 04 2002 - 08:00:59 CST