List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:May 11 2004 5:14am
Subject:Re: Database design.. Asking again
View as plain text  
Scott Haneda wrote:

<snipped>

>If users become day 1000 and each of those users has 70,000 user_contacts,
>that would be 70,000 * 1000 total records in one table, as users grow, this
>becomes perhaps too many records in one table.  Or at least the potential
>for it.
>
>My next option would be to make a new table, user_contact-userid and make
>one for each user, would would then mean, rather than one table with a lot
>of records in it, there would be many tables with a max of 100,000 records
>in it.
>
>Can someone share with me their thoughts and suggestions on this?
>  
>
<snipped>

MySQL has a table type called 'merge' tables for this purpose.
See http://dev.mysql.com/doc/mysql/en/MERGE.html

Basically you create a collection of MyISAM tables, and then define a 
merge table which you can use to refer to all of them at once. You can 
then either query the individual tables, or the merge table to get the 
results you want.

As for whether this is necessary, that would depend on the type of data 
you're storing. How big is each record? If it's just a couple of bits, I 
think one table for everyone would be OK, even at 70,000,000 records. 
However if your records are large ( eg contain text column, blob 
columns, etc ) then merge tables might be the way to go, especially if 
your data hits the 2GB limit.

In your case, with the fields ( ID, FirstName, LastName ), I think you 
could get away with just one table. I haven't tried anything this big 
though, so maybe someone who has can enlighten us both.

If you don't need any features of InnoDB, I suppose it wouldn't be too 
much of a hassle setting up merge tables - just in case.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au

Thread
Database design.. Asking againScott Haneda11 May
  • Re: Database design.. Asking againDaniel Kasak11 May
  • RE: Database design.. Asking againelectroteque11 May
  • Re: Database design.. Asking againBrian Reichert12 May
    • Re: Database design.. Asking againRoger Baklund12 May