moving from Access; 20 - 200 record db's updating to one Master db

[newcow]

I have a simple coldfusion db web app that is running off an Access db. It would have 200 to 300 records at the most with about 20 text fields and 40 checkbox fields. I would like to move it over to mySQL. This web app will be going on about 20 web sites in the next year and I want each web site to be able to push there record edits, adds & deletes to a master mySQL db.
The app is a car dealer inventory display app.
Is this pretty simple to do. Is it better to have all the different sites working off one db or seperate db's on the same server? Give me some feed back on this one please.

Thanks,

newcow

 

[sleipnir214]

Your question really can't be answered as asked. Whether to use a single or multiple databases depends on what your code does and how it does it.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[newcow]

Anyone else care to take a wack at this question?
sleipnir214: LOL rather interesting reply.
The question can be asked as it was, but it may not be able to be answered by you without more information.
I am not sure what further information I could provide as far as "what your code does and how it does it."
As noted it is a coldfusion car dealer inventory web app, it wit it is written in coldfusion, and it is an inventory app that displays vehicles on a car dealers site.
I have noted the number and type of fields per record, the number of records in the site, the number of site db and that I wanted all the 20 site db to update one master db. This master db would display all the records in the same way the other 20 sites would.
What other information would you like or need to give me some kind of an idea the pro's and con's or one db or main db for the 20 sites?
newcow

 

[BNPMike]

It's such a smalll database you could run it on a cheese grater.

Putting everything on one database would make it easy for you to aggregate the data ie no need for replication. On the other hand you may need to change a lot of your code to recognise your additional key. There is also more you have to think about in terms of data privacy.

Having separate databases means you've got to have some replication scheme to draw the data together but against that you can use your additional code unchanged.

Beyond that, as sleipnir says, it will rather depend on the actual code you have so far.

 

[newcow]

BNPMike: Yes it is small isn't it. The issue of data privacy came to my mind also. I am wondering if that will be an issue with customers that there data would be in the same db as some one elses. Than also the issue of SFT. If you have 20 seperate db you have 20 db that can go down, but if you have them all in one db if that one goes down they all go down, which is better? Also if I make some changes in the db it is going to be easier to change it in just one db instead of 20.
As far as the code I have now, every thing is written in coldfusion and I will connect to the mySQL db via a DSN.
There are a sample of one of my simple db query's:

<cfquery name="CarResult" datasource="cardata">
SELECT  tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers ON tblCars.Dealer=tblDealers.Dealer
<cfif val(url.vn)> 
WHERE tblCars.VN = <cfqueryparam value="#val(url.vn)#" cfsqltype = "cf_sql_integer"></cfif>
ORDER BY tblCars.#url.or1# #url.for#, tblCars.#url.or2# Asc, tblCars.#url.or3# Asc
</cfquery>

 

[BNPMike]

I think it hinges on this master database. How often does that need updating - immediately, daily, on request?

 

[newcow]

BNPMike: Well Immediately would be nice but not required. The master db will be linked is a site where all the records/cars from the 20 other sites can be viewed/searched. So once a day would be fine.
newcow

 

[BNPMike]

Well newcow looks like just you and me at the moment. My feeling is you can't go wrong either way. For what it's worth, I'd keep them separate and draw them together at the end of the day, using a simple set of transfers rather than any fancy replication.

 

[newcow]

BNPMike: Ok well I am just putting feelers out, this is on my TODO. I'll probley get to this one in a week or less so I will active this thread when I start working on it.
Thanks for your input.
newcow