Using a Database for Logging: How to Design?

[zzapper]

Hi
I need to log the number of "brochure Requests" on a per Region, Per
date basis.

eg for say 20 Regions , have a 366 columns (one per day of the year).
Obviously I can write a script to Create all the tables, but it seems
kind of clumsy.
Do arrays exist in Mysql?
It might also be easier to organise it into 52 weeks of 7 days.

How do I design the tables?

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips

[swdev1]

hmmmm - i would keep it easier ? just a logging table - lets say your
brochure requests really are tallied somewhere else before you get to the
real table insert ..

so
log_id -> primary key, autoincrement
Region_id -> integer, foreign key back to some region table
Num_req -> integer, number of requests for that reqion, for that day
req_date -> date, date the request was made ..

It sounds to me ilke you were thinking of it like a spreadsheet ..

hth - mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"zzapper" <da***@tvis.co.uk> wrote in message
news:f6*************************@posting.google.co m...

Hi
I need to log the number of "brochure Requests" on a per Region, Per
date basis.

eg for say 20 Regions , have a 366 columns (one per day of the year).
Obviously I can write a script to Create all the tables, but it seems
kind of clumsy.
Do arrays exist in Mysql?
It might also be easier to organise it into 52 weeks of 7 days.

How do I design the tables?

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips

[swdev1]

hmmmm - i would keep it easier ? just a logging table - lets say your
brochure requests really are tallied somewhere else before you get to the
real table insert ..

so
log_id -> primary key, autoincrement
Region_id -> integer, foreign key back to some region table
Num_req -> integer, number of requests for that reqion, for that day
req_date -> date, date the request was made ..

It sounds to me ilke you were thinking of it like a spreadsheet ..

hth - mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"zzapper" <da***@tvis.co.uk> wrote in message
news:f6*************************@posting.google.co m...

Hi
I need to log the number of "brochure Requests" on a per Region, Per
date basis.

eg for say 20 Regions , have a 366 columns (one per day of the year).
Obviously I can write a script to Create all the tables, but it seems
kind of clumsy.
Do arrays exist in Mysql?
It might also be easier to organise it into 52 weeks of 7 days.

How do I design the tables?

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips

[zzapper]

On Fri, 12 Sep 2003 02:12:25 GMT, "swdev1" <ws**********@advmed.com>
wrote:

hmmmm - i would keep it easier ? just a logging table - lets say your
brochure requests really are tallied somewhere else before you get to the
real table insert ..

so
log_id -> primary key, autoincrement
Region_id -> integer, foreign key back to some region table
Num_req -> integer, number of requests for that reqion, for that day
req_date -> date, date the request was made ..

It sounds to me ilke you were thinking of it like a spreadsheet ..

So what you are suggesting is a long table with a record per
region/per day that's not a bad idea especially in the short term

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips

[zzapper]

On Fri, 12 Sep 2003 02:12:25 GMT, "swdev1" <ws**********@advmed.com>
wrote:

hmmmm - i would keep it easier ? just a logging table - lets say your
brochure requests really are tallied somewhere else before you get to the
real table insert ..

so
log_id -> primary key, autoincrement
Region_id -> integer, foreign key back to some region table
Num_req -> integer, number of requests for that reqion, for that day
req_date -> date, date the request was made ..

It sounds to me ilke you were thinking of it like a spreadsheet ..

So what you are suggesting is a long table with a record per
region/per day that's not a bad idea especially in the short term

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips

[swdev1]

zZapPer -
yup - that is what I am suggesting - you DID ask for a logging table schema
..

then to get any #'s back out of it - you are gonna be doing some sql
statements .
let me know your thoughts? regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"zzapper" <da***@tvis.co.uk> wrote in message
news:gv********************************@4ax.com...

On Fri, 12 Sep 2003 02:12:25 GMT, "swdev1" <ws**********@advmed.com>
wrote:

hmmmm - i would keep it easier ? just a logging table - lets say your
brochure requests really are tallied somewhere else before you get to the
real table insert ..

so
log_id -> primary key, autoincrement
Region_id -> integer, foreign key back to some region table
Num_req -> integer, number of requests for that reqion, for that day
req_date -> date, date the request was made ..

It sounds to me ilke you were thinking of it like a spreadsheet ..

So what you are suggesting is a long table with a record per
region/per day that's not a bad idea especially in the short term

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips

[swdev1]

zZapPer -
yup - that is what I am suggesting - you DID ask for a logging table schema
..

then to get any #'s back out of it - you are gonna be doing some sql
statements .
let me know your thoughts? regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"zzapper" <da***@tvis.co.uk> wrote in message
news:gv********************************@4ax.com...

On Fri, 12 Sep 2003 02:12:25 GMT, "swdev1" <ws**********@advmed.com>
wrote:

hmmmm - i would keep it easier ? just a logging table - lets say your
brochure requests really are tallied somewhere else before you get to the
real table insert ..

so
log_id -> primary key, autoincrement
Region_id -> integer, foreign key back to some region table
Num_req -> integer, number of requests for that reqion, for that day
req_date -> date, date the request was made ..

It sounds to me ilke you were thinking of it like a spreadsheet ..

So what you are suggesting is a long table with a record per
region/per day that's not a bad idea especially in the short term

zzapper
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips