Dabase Design

[trading_jacks]

I need some help with my website. I will need to write a boolean
value every 5 minutes for 100 users. that is about 28,000 writes per
day. This data must be kept for at least a month. Can I simply write
this to a db as a new record each time? Should I write once per hour
with 12 columns? Or should I try to find a way to use some sort of
compress the data... such as some sort of algorithm?

This just seems like a lot of activity for an access db. Please let
me know what you think.

Thanks,
Mark

[Smartin]

trading_jacks wrote:

I need some help with my website. I will need to write a boolean
value every 5 minutes for 100 users. that is about 28,000 writes per
day.

One write every 3 sec on average. Cake.

This data must be kept for at least a month.

About 1M records per month. Cake, with frosting.

Can I simply write
this to a db as a new record each time?

Yes. Add whipped cream, and a cherry yet.

Should I write once per hour
with 12 columns?

Not just no, but heck no. Don't stratify your data across columns.
Columns are expensive. Rows are cheap. Non-score. Deduct cherry. Deduct
whipped cream.

Or should I try to find a way to use some sort of
compress the data... such as some sort of algorithm?

Nah, one table row per event. KISS (Keep It Simple Silly). You can store
bajillions* of records like this in a single Access DB table.

*untested; YMMV.

This just seems like a lot of activity for an access db. Please let
me know what you think.

Barring massive spikes at peak usage times, this should not be a problem
as long as you have a fast network. You could stress test this with a
handful of autobot routines to rapidly stuff a table over the network if
you are not sure.

Thanks,
Mark

HTH

--
Smartin

[lyle fairfield]

"trading_jacks" <MA**********@gmail.comwrote in
news:11*********************@v33g2000cwv.googlegro ups.com:

I need some help with my website. I will need to write a boolean
value every 5 minutes for 100 users. that is about 28,000 writes per
day.

Really?

This data must be kept for at least a month. Can I simply write

this to a db as a new record each time? Should I write once per hour
with 12 columns? Or should I try to find a way to use some sort of
compress the data... such as some sort of algorithm?

If you are accomplished enough to use bit operations you could write an
16 character string, or four doubles or whatever and write just once.

>
This just seems like a lot of activity for an access db. Please let
me know what you think.

It's unlikely that you have persisitent connections to your DB if you are
operating from a website, so each write can connect, do its work and
disconntect. A connect, write, disconnect should actually be connected to
the JET DB for a very small fraction of a second. One hundred of these
every five minutes should be trivial.
Of course, if you can do the 100 inserts or updates on the same connect,
you will very, very slightly increase the time of the write, but have
only one every five minutes.

Here in CDMA we often think of persistent connections. Access is cursed
with the notion of persistent connections; I expect because some genius
in the previous century decided it would be agood idea to mix procedures
and data in the same file. This underlies many unique Access problems.
But we don't have to use persistent connections, and, TTBOMK, on a
website, we don't. And when we don't use perisistent connections we use
the JET db without the Access millstone around its neck; and then it is
really JET-like.

Thanks,
Mark

--

[trading_jacks]

If you are accomplished enough to use bit operations you could write an
16 character string, or four doubles or whatever and write just once.

that is exactly what I had in mind. But it seems that Access can
handle the load of 1M records, so I am thinking it will be much
simpler to maintain if I just use a new record each time.

I haven't started coding this part yet, so I am not 100% sure, but I
should be able to write all 100 in one connection. But again, you
guys don't seem to think it would greatly impact performance either
way. And yes it is not a persistent connection, and yes it uses jet.

thanks for your help.

[AllenWhite]

If you're just keeping track of a boolean value across time, why not
only record the changes in value? I agree with the other posters that
it isn't a troublesome amount of activity, but I don't see any value
in recording all those data points if you can record just the changes
and not lose any information.

On Mar 9, 11:36 am, "trading_jacks" <[e-mail]:

If you are accomplished enough to use bit operations you could write an
16 character string, or four doubles or whatever and write just once.

that is exactly what I had in mind. But it seems that Access can
handle the load of 1M records, so I am thinking it will be much
simpler to maintain if I just use a new record each time.

I haven't started coding this part yet, so I am not 100% sure, but I
should be able to write all 100 in one connection. But again, you
guys don't seem to think it would greatly impact performance either
way. And yes it is not a persistent connection, and yes it uses jet.

thanks for your help.

[trading_jacks]

On Mar 9, 11:54 am, "AllenWhite" <[e-mail]:

If you're just keeping track of a boolean value across time, why not
only record the changes in value?

so do something like this:

autoid - userid - trackerid - status - date - time
1 - tjacks - 101 - yes - 1/1/07 - 12:55
2 - tjacks - 101 - no - 2/2/07 - 19:20
3 - tjacks - 101 - yes - 2/2/07 - 22:40
then use some code to determine when it is yes and for how long (and
no).

that is not a bad idea. The logic should be fairly easy as well.

thanks!