Can object oriented table design help to solve this problem?

Поиск
Список
Период
Сортировка
От Alan T. Miller
Тема Can object oriented table design help to solve this problem?
Дата
Msg-id 00cc01c2c509$6082fd50$6e01a8c0@webdev
обсуждение исходный текст
Ответ на Documentation needs significant improvement  (Chris Johnson <chris@chaska.com>)
Ответы Re: Can object oriented table design help to solve this problem?  (Jeffrey Melloy <jmelloy@visualdistortion.org>)
Hit Summaries  (Jonathan Bartlett <johnnyb@eskimo.com>)
Список pgsql-general
I have a farily general question about structuring a database, and
considering this is the 'general' mailist for postgresql my database of
choice for the project I am working on, I thought this would be a good place
to start. Please forgive me if this should be posted elsewhere. Of course a
hint on where it should be posted would be great if this is not the correct
place to ask this.

Anyway, I am trying to come up with a logging table scheme for an
application that indexes rental properties around the world. Currently we do
not have anything in place in our current database tables to keep an
accurate ongoing tally of the hits each of these properties gets on any kind
of time interval.

What I do have is a table that keeps a running total for each property. The
way that is implimented is through a javascript function that makes a
request to a script that then updates the database table containing a unique
id for that property and then ups its count for each hit. So we have a table
that looks like...

TABLENAME:     Hits
TABLE COLUMNS:     ID_Property | hit_total | last_updated

However, we want to expand this and be able to keep a running tally of hits
per week, month, day perhaps. But I am having trouble wrapping my head
around the best way to go about doing so. A sensible table scheme escapes
me.

My first inclination was to create a table as follows...

TABLENAME:     Weekly hit total table
TABLE COLUMNS:     ID_property | week 1 | week 2 | week 3 | week 4 | etc...

That table would be populated via an automated script that would tally up
the weekly totals for each property based upon the number of hits drawn off
of a modified version of the hits table described earlier. In other words,
the current hit counter table would be cleared every week to make way for
the tally to start fresh for the next week.

The problem with the above scheme however is that I would be adding a column
to the table every week, and it just seems like bad design to do so. There
must be a better way.

To avoid having to create a table that requires that I alter its structure
every week, I thought of the following solution but It seems overkill, as it
would add a huge number of tables. Anyway, this scheme would have a seperate
table for each property with a structure something like the following.

TABLENAME:     Hit Totals for Property 'foo'
TABLE COLUMNS:     Week | weekly hit total

For every week there would be a new row added to the table, again, it would
be populated by a cron script. However, I do not like this solution as I am
not interested in having 5000 tables set up for all the properties, and then
each time one of the properties were added or deleted I have to alter the
database to add or delete tables.

to sum it up....

Solution 1 which provides a unique row for each property, seems inadaquate
because it will require adding columns to the database on each update, thus
altering the table structure on a constant basis.

Solution 2 which provides a unique table for each property in the database
seems inadaquate for the same reason as above, I am altering the database
itself all too often, not to mention I have to deal with thousands of
tables.

Considering postgreSQL is an object oriented database, and I am new to that
concept in databases, I was hoping maybe someone here had a workable
solution or suggestion that may or may not take advantage of PostgreSQL's
object oriented features. Any help or suggestions on this would be very
highly appreciated.

Thanks,
Alan



В списке pgsql-general по дате отправления:

Предыдущее
От: Iker Arizmendi
Дата:
Сообщение: Re: [RFC] libpq extensions - followup
Следующее
От: Jeffrey Melloy
Дата:
Сообщение: Re: Can object oriented table design help to solve this problem?