[development] table vs tables

[Steve Ringwood]

Glen

	Regarding

>> I'm starting a project where I need to make a larger database that I'm
used to make. I can use a little advise on this project.
>> Each record in the database will include 1 to 3 photo and some other
information, in total aproximately 100k of memory per record. I expect a
total of maximum 500 records per category (1000 categories). So 500 x 1000 x
100K = 50G. It >> is possible to store all data in one table. This database
will be used between a larger database and the clients.
>> My question is: is one table feasable for this amount of data? Or would
it be recommended to store the data in different tables. The key factor here
is speed. If the client wants to see some information (always with at least
1 photo), >> the time to get the information from the database to the screen
should be minimal.

Are you really planning on storing the photo's in the table.  It is
generally more useful to store the image on the file system and just the
path in the database (you just need the image path and not the image for the
browser).  If you are not planning on placing the images in the database
100K is a lot of data, more than make sense to display on a single screen.

As for speed, one of the factors is a unique key for each record another
though is how you plan to look stuff up.  Just the potential number of
records is large (500,000) and almost twice as many comments a drupal.org
has.  The "obvious" way to break up the table would be to use 1000 a smaller
tables, but too many tables can also cause a problem.  The bottom line is it
will take some tweeking to get things "right" and that will depend on the
details of the data and how the data is accesed.

Steve