php DB rotation

[Jim Lee]

I have a PHP server controller thatl read/write to Database table

PHP server will start read / write to a new DB table every week/monday
e.g.
table-1-2-2012
table-1-9-2012
table-1-16-2012
table-1-23-2012 ... etc

I think of 2 ways to do the DB table rotation

1) check the serve timestamp, if today's date is week of 1-23-2012,
then read/write to table-1-23-2012

2) have a unix corn job run every monday to generate a text file on
PHP server with DB table named on that date - on each PHP request,
check the text file's table name - then read/write to that DB table

any other solution to DB table rotation?

the first way check timestamp have a drawback when server's time is
not set to correct time, since there are many PHP server running for
load balance, it's not a good idea.

the second way is a better solution, but request additional setup -
cron job

[Michael Fesser]

.oO(Jim Lee)

>I have a PHP server controller thatl read/write to Database table
>
>PHP server will start read / write to a new DB table every week/monday
>e.g.
>table-1-2-2012
>table-1-9-2012
>table-1-16-2012
>table-1-23-2012 ... etc
>
>I think of 2 ways to do the DB table rotation

The first question that comes to mind: Why table rotation? A good DBMS
can easily handle millions of records, if properly indexed.

Or do you really have millions of new records each week? If not, just
add a timestamp to your records and you can easily filter them by date.

Micha

--
http://mfesser.de/blickwinkel

[Jim Lee]

On Tue, 31 Jan 2012 04:36:37 +0100, Michael Fesser <net...@gmx.de>
wrote:

I must rotate to a new table, because there is another REST layer on
top of the DB, the REST call will have a new table-name every week to
read/write to.

my main problem now, is how to reliably compute the DB table-name?

using the server timestamp is a solution, I must make sure server time
is acurrant though.

[M. Strobel]

This subject cannot be discussed without knowing which database system is used on
which OS.

And then, go to the appropriate db group. I can tell that in the postgresql
performance groups "table partitioning" is a big subject.

/Str.

[Denis McMahon]

On Mon, 30 Jan 2012 18:05:56 -0800, Jim Lee wrote:

> the first way check timestamp have a drawback when server's time is not

> set to correct time ...

> the second way is a better solution, but request additional setup - cron
> job

A cron job running from the server clock will not be any more accurate
than using the server's time in PHP or in the database itself.

If the server clock is wrong, the cron job will update the text file at
the wrong time anyway. As both methods rely on the server clock being
correct, I'd go with checking the time when you write to the table.

You don't have to wait until the day you're going to start populating it
to create the table either.

Rgds

Denis McMahon

[Captain Paralytic]

On Jan 31, 4:03 am, Jim Lee <jimlee2...@yahoo.com> wrote:
> I must rotate to a new table, because there is another REST layer on
> top of the DB, the REST call will have a new table-name every week to
> read/write to.

This is not a "reason", since the REST call could easily supply the
timestamp instead of having a new table name.

[The Natural Philosopher]

That you can do by simply tying the server in to ntp.

or you might be able to do an NTP call to the 'net directly..if it fails
I guess you use the old server name.

My machines do record reasonably weird ties at boot..I suppose they
start off the realtime clock - but that changes pretty quickly.

IIRC you CAN use ntp to update the realtime clocks on most hardware..so
as to minimise the difference.

http://linux.die.net/man/8/hwclock

[Chris Riesbeck]

Having just read this same poster ask the same question on
comp.lang.java.programmer, but claiming it was for a Java controller,
I'm guessing troll.