Database Design Question

Keep everything in a single table?

[PumpkinHead]

Hi all,

I'm designing a site that uses a MySQL database accessed with PHP. I need to make sure everything is efficient now as I don't want to do a complete restructure a few months down the line.

At present I have a test database with all records within one single table (Approx 20 columns). This is nice and fast with a single person on my site (me!) and in all honesty, it's the easiest way of doing things for a php newbie like me.

Now...If I had 10,000 people on my site (which relies heavily on the database), would it be best to break down the single table into multiple tables(even if this means more coding up front)?

[henry0]

In WebmasterWord's Google search
enter: MySQL indexing
You should find most of the answers you looking for

regards

Henry

[PumpkinHead]

Thanks for the reply Henry. I can't find the webmasterworld google search so I used standard Google. I had a look at around 10 sites but they all seem to be about response times or MySQL problems, rather than 'whats best'.

Can someone please provide more info or a link to the webmasterworld google search?

Thanks

[Timotheos]

Hi PumpkinHead,

I think your talking about 'normalization'. Do a search on that and it will bring up plenty. Here's an some examples
[webmasterworld.com...]
[webmasterworld.com...]

Personally, I'd stick with normalizing your database. The speed differences will be nominal.

Tim

[topr8]

are you repeating data in the columns?

eg is there a column like favourite colour, where lots of rows have the same result such as red or blue? if so then you need a relational database structure , in other words multiple tables, if all the rows are pretty much unique then there is no need.

if you are just reading the data from the table then there is no problem at all, if you are writing to the db as well things could slow up a little (but only with VERY heavy traffic)

[PumpkinHead]

Other than 1 column, all data will be staying the same.

Thanks for the help all :)

[henry0]

Here is how adding the "Google seach" to search WebmasterWorld

here you go [webmasterworld.com]

[killroy]

Multiple tables will cause multiple reads. If u select individual, or small numbers of rows by index on each request, then a single table will be faster for many requests. This is unless there is huge duplication (for example a log) where the split tables would be much smaller and could fit into memory.

SN