Java DB rotation

[Jim Lee]

I have a Java server controller that read/write to Database table

Java 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 server 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
Java server with DB table named on

that date - on each Java 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 Java server running for load balance, it's not a good
idea.

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

 

[Arne Vajhøj]

I have a Java server controller that read/write to Database table

Java 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 server 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
Java server with DB table named on

that date - on each Java 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 Java server running for load balance, it's not a good
idea.

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

I can not see any any problems with construction the table
name every time you need it.

That string formatting will be insignificant compared to
the actual database operation.

And it will be far more expensive to read from a file.

So definitely #1.

Arne

 

[Arne Vajhøj]

I can not see any any problems with construction the table
name every time you need it.

That string formatting will be insignificant compared to
the actual database operation.

And it will be far more expensive to read from a file.

So definitely #1.

But also consider if you really want to switch table like
that.

Why not just use the same table all the time with a column
for the period?

For most cases that would be the best solution. Exceptions
do exist.

Arne

 

[Jim Lee]

I can not see any any problems with construction the table
name every time you need it.

to construct the table name, i need to use the server timestamp, what
if the server time is incorrect? then everything is messed up? On
linux, is the server time always in sync with internet time server? or
it's depended on the motherboard BIOS time?

 

[Arne Vajhøj]

to construct the table name, i need to use the server timestamp, what
if the server time is incorrect? then everything is messed up? On
linux, is the server time always in sync with internet time server? or
it's depended on the motherboard BIOS time?

If you can not get the time correctly to construct a tablename
then you can not get the time correctly to write to the file.

You can setup NTP to synch time.

But does it matter if one server is 2 seconds off?

Arne

 

[Jim Lee]

But also consider if you really want to switch table like
that.

Why not just use the same table all the time with a column
for the period?

For most cases that would be the best solution. Exceptions
do exist.

Arne

I must use a new table

 

[Martin Gregorie]

I have a Java server controller that read/write to Database table

Java 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

What problem are you using table rotation to solve?

What would prevent you from using a single table containing datestamped
rows which are archived and/or deleted the rows after "cycle length" days?

 

[Jim Lee]

1-2 sec off is not matter

If you can not get the time correctly to construct a tablename
then you can not get the time correctly to write to the file.

You can setup NTP to synch time.

But does it matter if one server is 2 seconds off?

Arne

 

[Jim Lee]

What problem are you using table rotation to solve?

What would prevent you from using a single table containing datestamped
rows which are archived and/or deleted the rows after "cycle length" days?

my main problem is how to make sure "how to get the correct table name
to read/write to" depending what day in the week

start a new DB table is a must since it's going through some other
REST backend layer

 

[Arne Vajhøj]

1-2 sec off is not matter

Synching time today is not difficult.

Arne

 

[Arne Vajhøj]

my main problem is how to make sure "how to get the correct table name
to read/write to" depending what day in the week

start a new DB table is a must since it's going through some other
REST backend layer

There is nothing in REST that requires such a table structure.

And it would be better to fix the bad code requiring such
a table rollover than to make other apps bad to work with it.

Arne

 

[Arne Vajhøj]

I must use a new table

New tables does not solve a business problem.

It is something invented inside the IT org.

It can be changed if there is a desire to do so.

Arne

 

[Arne Vajhøj]

I have a Java server controller that read/write to Database table

Java 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

BTW, drop the hyphen in table names it will cause
problems.

Arne

 

[Lew]

BTW, drop the hyphen in table names it will cause
problems.

and use YYYYMMDD format for sorting purposes if you must be so intransigent on
the repeated, excellent advice to abandon the approach in favor of a proper
table design.

 

[Robert Klemme]

+1

I must use a new table

Why?

robert

 

[Robert Klemme]

There is nothing in REST that requires such a table structure.

And it would be better to fix the bad code requiring such
a table rollover than to make other apps bad to work with it.

Another question: Jim, what database are you using? If the instance
requiring multiple tables is afraid of volume the typical solution to
this issue is called "partitioning". If your database supports it,
that's typically the way to go for such kind of data.

Cheers

robert

 

[Chris Riesbeck]

Another question: Jim, what database are you using? If the instance
requiring multiple tables is afraid of volume the typical solution to
this issue is called "partitioning". If your database supports it,
that's typically the way to go for such kind of data.

Pretty much every response from the OP has suggested either really bad
intra-team communication (distributed team?), or a system architect
angling for an appearance on the Daily WTF.

 

[Chris Riesbeck]

Pretty much every response from the OP has suggested either really bad
intra-team communication (distributed team?), or a system architect
angling for an appearance on the Daily WTF.

I change my mind. The same poster made the same query on comp.lang.php
but said

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

I now hypothesize trolling.

 

[Arne Vajhøj]

Another question: Jim, what database are you using? If the instance
requiring multiple tables is afraid of volume the typical solution to
this issue is called "partitioning". If your database supports it,
that's typically the way to go for such kind of data.

Yep.

But if one has a database of a size that requires partitioning, then
one better have both app and database specialists inhouse.

Arne

 

[Arne Vajhøj]

Pretty much every response from the OP has suggested either really bad
intra-team communication (distributed team?), or a system architect
angling for an appearance on the Daily WTF.

In both cases it would make sense to push back a little
bit.

(within the constraints given by the org chart)

Arne