Hello, i'd like to ask the more experienced users here some advice.
I am talking about MySQL specifically but my question is actually much more about design than MySQl itself.

The situation is that i have a number of databases and i wish to sell these to individual users.

Each database is maintained on a server and data is constantly being added and modified. I do have full control over this process so i can control things like timestamps.

The individual user may have his database on a local PC or on a server and actually i don't know who uses what. The level of experience of the individual users will also vary quite a bit from die-hard experts down to beginners.

An individual can buy access to 1 or more databases. He should then get a copy of that database. So i'll need some method of verifying if a person has access. This is not something that neccessarily needs to be organised inside the database, it could be a surrounding application like a website.

With his copy he also needs a mechanism to (semi-) automatically get updates so his copy stays up-to-date. Some of the databases are a few hundred megabytes. Not something you want to download every day. You want to get that once and then from that point receive updates of only new information.


Solution 1 : file downloads.
The first solution i'm thinking of is along these lines:
- create a master database that has data up to a certain date
- create an update database with data from that date on
- create a dump file from the master, compress that and store it as a static file.
- create an application that does the same with the update database only this application would automatically update the file once every X minutes/hours.

I create a website where people can register and buy a product, being a database. The website then takes care that only authorised people can download specific files. When a person has bought a database he downloads the dump from the master once and installs that. He then downloads the dump from the update database as often as he wants and adds that to his copy of the master. Every now and then, like every 3 months, i will create a new master database dump file and reset the update database.

Actually as i'm typing that it sounds plausible but there's a nagging feeling in the back of my mind i missed something that makes this unpractical ? Thoughts anyone ?


Solution 2 : database remote access.
- i create a website for the purpose of handling the sales and with a sale i create a database username/password to allow the buyer to remotely access the database he's bought (probably just SELECT privilige).
- inside the database i make sure all data is accurately marked by timestamps and the user can then extract data by timeperiod. In other words he can set a timeperiod from now to the last update he did and then only extract/copy data added since that time. To facilitate things i can create a stored procedure or sql script that basically take a date as parameter and then runs an update.

Sounds plausible again. Especially as i think it should be possible to run the stored procedure with the update by an application on a timer. Thus the user can run automatic updates every X hours/minutes. Has anyone got experience with this type of set-up and would you recommend it ?


Solution 3 : MySQL replication.
I've read the manual and it sounds brilliant. Just one little problem: as soon as a slave has access to a master database it actually has access to the MySQL server and thus ALL databases. You cannot stop a slave from accessing a specific database. Remember i have no control over the individual users.

A possible solution would be to use a relay server. Setup a MySQL process on a VPS that acts as both master and slave. An individual database gets replicated to the VPS. Because i control the VPS i can set it to replicate only 1 specific database. Then i allow the individual user access to the VPS and so he can replicate only the 1 individual database on that VPS.

Alternatively i could achieve the same if the master databases all run under their own MySQL process on my master server. ( if technically possible )

Either way it sounds elaborate but somehow i got the feeling there's no way around it if i want to use replication AND controll access on an individual basis. Am i correct in thinking this or did a miss something about limiting access for MySQL slaves ?

What does replication mean in terms of managing maintenance ? For example i know there's a file used that contains a running record of database changes and that in turn is used by the slaves to see from what point they need to "catch-up". I'm going to have to watch the diskspace on the server and i'll periodically need to reset things, but what else will i need to take care of ?

Did i type all that
I guess my biggest problem is that i work alone and there's no-one around me i can have a chat with about these kind of things.

Something i should add. Althou i myself will be using MySQL i am trying to create something whereby the individual user could possibly use a different database application. This is what first brought me to the first solution with file downloads. If i create the dump files myself, so using an application and not the standard MySQL dump tool i can create those file to comply with standard SQL as much as possible. That in turn should make it possible for any number of database applications to import my data.

I would go with solution 2. Keep a customer table linked to a table of what they have access to. One of the attributes of the access table is when they last requested an update. When they ask for an update display the last update date as the default (which they can override), and when they hit go pass the date to a stored procedure that selects everything changed since that date. Not too complex and it allows them to adjust the date if their last download got lost or corrupted.

Thanks for your reply,
so you'd keep a record of the last update an individual made so he doesn't need to remember himself. That would be usefull indeed. That also allows me to track who's active and how often, could be usefull info as well.


Have given this a bit more thought and actually i think i'll need all 3.

Solution 1, file downloads,
I really don't want to get into a situation where i need to provide a convert program/application/script for every possible database application out there. So downloading neutral SQL files is a valid option.

At the same time i guess i should also do a dynamic XML feed.
From my point of view its only 1 application and then it's up to the individual to determine if an XML feed or downloading SQL files is most practical. But it should allow me to cover most of the database applications.

Solution 2,
would appear to be most appropriate for those people that use MySQL themselves. Not too sure how "intelligent" i can make the update procedure but i'm sure i can get some help on that one.

Solution 3,
basically looks like i'll need this for those that want to use the database as the background for a website. That simply requires and database that's as live as possible. I guess you can get a long way with XML feeds or running the update procedure periodically but you can't beat the speed of replication.

Hmmm, not sure if setting up replication is worth it ? Anyone ?