MySQL Database Question

[Mark Palmer]

Hi List,

I have a largish table of 100,000 plus records set to grow to 200,000  
ish that will be supplied by my client (from Access).

Because I suspect this data will be updated and re-supplied on a  
regular basis I want to keep the table structure and field count etc  
the same as the client table structure. However I need to add  
additional fields related to web use - status, dates modified etc.

Is it efficient to pop these fields into another table and link the  
two one-to-one via the recordID's rather than add them to the master  
table?

The data will be mainly accessed on a day to day basis by SELECT *  
FROM `table` ORDER BY RAND() LIMIT x (its a quiz question database),  
with occasional access to build stats etc.

What is the most efficient join, or would a MySQL View work more  
efficiently (haven't used views before).

Just looking for any pointers.

Regards

Mark Palmer
E: mark@...
T: 01902 620500
W: www.pageworks.co.uk





--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[bilcorry]

Mark Palmer wrote on 7/24/2008 5:53 AM:
> Because I suspect this data will be updated and re-supplied on a regular
> basis I want to keep the table structure and field count etc the same as
> the client table structure. However I need to add additional fields
> related to web use - status, dates modified etc.
>
> Is it efficient to pop these fields into another table and link the two
> one-to-one via the recordID's rather than add them to the master table?

If they're just adding rows, you can have everything in one table.  But if they're modifying rows, removing rows, etc, then rather than trying to keep it all sync'd (you don't want to lose your stats), I'd use two tables and simply delete all the rows for their data and insert the latest version.  You just need to make sure that the primary key remains the same and is fed off of their data.


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/