Best way to clone a database (structure only?)

Havin_it

Hello,

I'm working on a diary/appointments application, in which each year is represented by a separate database with identical structure. I want to make it so that when a new year is required, the new db can be created on-the-fly.

I just wondered if there's a recommended way of doing this, and if there might be any problems in implementing it with PHP5/MySQL5/PDO (on Windows). Each new db will simply be named "diary-YYYY" (corresponding to new year).

laserlight

You just need to write a script to create a template database (use a CREATE DATABASE statement and what not), then have it vary on the year.

Still, why such a design in the first place? Why not have it all in a single database?

tomhath

Starting a fresh database every year makes a lot of sense for this application. Previous years' data is just an archive, no reason to clog up the current db with it. Also makes backup and recovery smaller/faster.

Havin_it

What he said 😉

99% of the time only the current year will be in use during an entire client session, and older years will be archived to an XML or other text-based format eventually. It also means that I can use shorter primary keys and be more future-proof (no running out of integer values).

So, if I do a CREATE DATABASE followed by a series of CREATE TABLE statements, can I do all this through one PDO object or is it (as logic seems to dictate) necessary to instantiate a new PDO with the newly-created db in order to issue the CREATE TABLEs?

sid

Havin_it wrote:
What he said 😉

99% of the time only the current year will be in use during an entire client session, and older years will be archived to an XML or other text-based format eventually. It also means that I can use shorter primary keys and be more future-proof (no running out of integer values).

So, if I do a CREATE DATABASE followed by a series of CREATE TABLE statements, can I do all this through one PDO object or is it (as logic seems to dictate) necessary to instantiate a new PDO with the newly-created db in order to issue the CREATE TABLEs?

i really don't share these concerns... relational databases are designed to work smoothly with tables with millions of rows or even more. so unless you and your users twitter every minute, i dont see any issues for this application.

Havin_it

Weeelllll... I don't doubt the capabilities of the software, but let's just say in this case the hardware is a different matter. I'm really batting for any performance savings I possibly can.

laserlight

So, if I do a CREATE DATABASE followed by a series of CREATE TABLE statements, can I do all this through one PDO object or is it (as logic seems to dictate) necessary to instantiate a new PDO with the newly-created db in order to issue the CREATE TABLEs?

You should only need one PDO object. I never tried creating multiple databases at one go before using a script, but even if you need a separate PDO object for each, since you intend to "make it so that when a new year is required, the new db can be created on-the-fly", you would only need to bother on a yearly basis, methinks.

Havin_it

So PDO is happy to switch its focus to another db if I just do:

$db->exec("USE `diary-$newYear`;");

MarkR

You should not need to create a new database or tables for a new year or an archive.

Your best bet would be to create a number of views for the current year, and then use those views for SELECTs. Keep all years' data in the same tables until it becomes a problem (which it will not do for quite a while).

Mark

Havin_it

MarkR, that sounds interesting. I've only just started reading a bit about views since I started this thread; can you comment on the relative performance merits of this approach?

MarkR

Views offer no performance merits over doing those queries manually; what they do do is give you the possibility to reuse (potentially complicated) SQL in different queries / pages.

This makes your application more consistent (different pages can't be using different queries if they select from the same view) and maintainable (a change to the view affects all queries which use it).

Mark

Havin_it

Hmmm... although I'm interested in the idea from a more-straightforward-code perspective, it still seems to me that if I'm only using one (smaller) database at most times, this is more efficient than if I have to constantly query a db that's 2,3,...n times larger. With a view (if I understand it correctly) I'm essentially just adding a nested WHERE clause, which must be more work than just choosing which db to select from.

My query code so far is all unique to one script or implemented in includes, so I think I'm already avoiding unnecessary code replication.

Also it seems a helluva lot simpler to just drop a no-longer-needed db than to have to delete according to a quite abstruse set of criteria. (Though perhaps views would make this task trivial as well?)