What is better - 1 large table or 3 small tables?

ainars

Hi,

I have a busy profiles website. All users data (username, age, sex etc) are being kept in one table. Now I want to to the profiles add more than 40 questions. What is better - to add new fields to the existing table or to create a new table? If I add new fields to the existing table this table will become very large (about 80 fields). If I create a new table search script will be forced to select data from 2 tables...

Thanks.

drawmack

I would create a couple of tables

question
    id
    order
    text

answers
    id
    querstionid
    userid
    answer

of course this assumes that all answers are typed in by the user. It'll be another table or two if you want to have typed in and select from list answers or even just select from list answers.

Roger_Ramjet

There is no 'Best' . It all depends on circumstances.

The whole point of a relational database is to eliminate duplication of data: we Write Once Read Many. If the information in a several fields is being repeated across multiple records, we break it out into a seperate table and include a referance in the original table.

In your case you wish to use additional tables because each record, although unique, contains so much data. Will this impact on hardware resources, I think is what you are worried about. And the answer is, probably, Yes!

In this case, we are encouraged to 'distort' the Relational model because of Domain Knowlege.
If we bump up against hardware or system limitations we will be forced to reorganise our data, before that happens we should tune our design for the given platform.
More importantly, if we have fore-knowlege of how the data is to be used, we can redesign our tables to simplify those uses. If , for instance, you knew that you were going to present 10 questions per page, then using 4 tables: 1 for each page: might simplify things no-end.

Certainly, you should ask the question:

How can I organise my data so that I can use simple queries that still only return the fields I need?

How can I organise my data so that my page scripts do not need complex control structures, loops, conditions etc to select and organise the query results?

Thus, there is no 'Best' solution in the abstract, but there are optimal solutions in every given case.