DB Design Question

[Drakkon]

which is better:
Main table with refereces to tables1, table2, table3..etc
like Main_Table with cols (main_id,table1_id,table2_id,table3_id)

or

Main table then each table references back to main table
like Table# with cols(tab_id, main_id, data)

Looking for reducing size, increasing speed...the usual stuff for a DB...or is it just a matter of preference?

 

[oog]

usually the two are not interchangeable (especially when there is a one-to-many relationship). if it's a one-to-one relationship, it doesn't matter, and you should even consider merging them into a single table (unless there is a good reason to keep them apart)

 

[MonkeyK]

The two are completely different solutions.

In the first case you are creating one table to join many tables, this sets up many to many relationships amongst your n tables.
In the second case you are creating one master table that all other tables refer to this is setting up several one to many relationships.

Without knowing the problem you are solving, it is not possible to say which is better.

 

[Drakkon]

Ok well table main would address a persons simple profile (name/address/occupation/etc)
Table 1 would be their responces to a survey
Table 2 would be their image and sound data
Table 3 would be related to the questions being asked in the survey (no relation to main table so i dunno why im mentioning it)
Table 4 would be a further bio responces to longer questions

Its a database of interviews that are being conducted by a professor to her students...each semester there will be 100-200 interview...their application done online....and then a later interview done in person thats sound recorded...then a pic taken of them to associate it...

 

[MonkeyK]

Then very likely you want the second case since you are saying that you want many things to be related to one person. This normalizes your data (generally means removes the need to save space for datat that isn't there).

If for any of your tables, there will never be more than one per student, then that table should be included in the main table.

The purpose of your first case would be if one survey or sound data needed to be assigned to more than one student.

Regarding speed. At the size of the database you anticipate, even with a poor DBMS, you will not see performance problems related to either design. For space, you are probably better off with the second case (no need to store a reference to table3_id if there is no coresponding data in table3).