Need help to organize database

On Mon, Dec 20, 2004 at 12:13:31 +0000,
"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Hi
Sorry for my English..

I need to organize database structure for saving statistic data for objects. I
have about 24 * 31 * 4 fields (4 month, 31 days, 24 hours) of data for one
object. Each field contain 8 numbers (N in general). So:
object1 -> data -> field1, field2,...
object2 -> data -> field1, field2,...
...
How can I store this data in postgres database?

For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in table -
but it is not good idea, of course.

Or may be 8 fields and 24 * 31 * 4 rows per object in table, but table looked
very big...

You want to store rows with an object id, a field or fields storing the
hour and the 8 object fields (assuming these fields shouldn't also
be one per row - but without any more knowledge about them I can't say).
You should put a primary key constraint on the object id and the hour field(s).

Or may be it's not good idea to use postgres for this purpose?

Without seeing what you are going to do with the data it is hard to say
if using a dbms is overkill or not.

Instead of having separate fields for day, hour, ... - why not use
timestamp values?

On Dec 21, 2004, at 3:47 PM, Vladimir S. Petukhov wrote:

Ok, this is a real example:

CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...

daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);

On Tuesday 21 December 2004 14:38, Bruno Wolff III wrote:

On Mon, Dec 20, 2004 at 12:13:31 +0000,

"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Hi
Sorry for my English..

I need to organize database structure for saving statistic data for
objects. I have about 24 * 31 * 4 fields (4 month, 31 days, 24
hours) of
data for one object. Each field contain 8 numbers (N in general). So:
object1 -> data -> field1, field2,...
object2 -> data -> field1, field2,...
...
How can I store this data in postgres database?

For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in
table - but it is not good idea, of course.

Or may be 8 fields and 24 * 31 * 4 rows per object in table, but
table
looked very big...

You want to store rows with an object id, a field or fields storing
the
hour and the 8 object fields (assuming these fields shouldn't also
be one per row - but without any more knowledge about them I can't
say).
You should put a primary key constraint on the object id and the hour
field(s).

Or may be it's not good idea to use postgres for this purpose?

Without seeing what you are going to do with the data it is hard to
say
if using a dbms is overkill or not.

--
Riches cover a multitude of woes.
-- Menander

Now playing: track08.mp3
AutoGenerated by fortune & xmms...

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

Ok, this is a real example:

CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...

daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);

On Tuesday 21 December 2004 14:38, Bruno Wolff III wrote:

On Mon, Dec 20, 2004 at 12:13:31 +0000,

"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Hi
Sorry for my English..

I need to organize database structure for saving statistic data for
objects. I have about 24 * 31 * 4 fields (4 month, 31 days, 24 hours) of
data for one object. Each field contain 8 numbers (N in general). So:
object1 -> data -> field1, field2,...
object2 -> data -> field1, field2,...
...
How can I store this data in postgres database?

For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in
table - but it is not good idea, of course.

Or may be 8 fields and 24 * 31 * 4 rows per object in table, but table
looked very big...

You want to store rows with an object id, a field or fields storing the
hour and the 8 object fields (assuming these fields shouldn't also
be one per row - but without any more knowledge about them I can't say).
You should put a primary key constraint on the object id and the hour
field(s).

Or may be it's not good idea to use postgres for this purpose?

Without seeing what you are going to do with the data it is hard to say
if using a dbms is overkill or not.

--
Riches cover a multitude of woes.
-- Menander

Now playing: track08.mp3
AutoGenerated by fortune & xmms...

On Tue, Dec 21, 2004 at 20:47:31 +0000,
"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Ok, this is a real example:

CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...

daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);

That approach is reasonable but depending on what the val* columns mean
you might each of those as a separate role. You didn't add a lot of
information, but that they appear to be the same type suggests that you
might want one row per value. But without knowing what they mean it is
hard to say.

On Wed, Dec 22, 2004 at 00:16:06 +0000,
"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

On Tuesday 21 December 2004 21:21, Bruno Wolff III wrote:

On Tue, Dec 21, 2004 at 20:47:31 +0000,

"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Ok, this is a real example:

CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...

daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);

That approach is reasonable but depending on what the val* columns mean
you might each of those as a separate role. You didn't add a lot of
information, but that they appear to be the same type suggests that you
might want one row per value. But without knowing what they mean it is
hard to say.

OK, i want to store ststistic information, ingoing/outgoing traffic,
ingoing/outgoing errors(val1-val4) for example...

Those sound like different domains then for each column. So you probably do
want to keep the 8 values in one row.
I also noticed that you marked a lot of these values as NULL. I think that
daypos, hourpos and id form the primary key and you probably don't want to
allow NULLs for these.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why would it be?

The only real advantage to what you are suggesting would be slightly
reduced disk space usage, and just maybe *very* slightly improved
performance on low-memory servers, or servers with very slow disks, but
I find it unlikely that this would really be significant enough to be
worthwhile.

There are greater advantages to not doing something like that: easier
coding of your front-end software, a layout which is a bit more
comprehensible, making future maintenance of the design and code much
easier, etc.

Also, given the amount of data you are talking about, and assuming that
you are inserting all of this data in one big lump, you may wish to
VACUUM FULL after doing your INSERTs (not after each one, of course --
after doing all of the INSERTs, or after doing a big chunk of them. If
data is inserted incrementally over a period of time, then just do the
VACUUM ANALYZE every so often during that time, and you shouldn't have
a problem).

On Dec 21, 2004, at 8:24 PM, Vladimir S. Petukhov wrote:

Yes, of course, this is example only.
But relation between tables is not important now...
I whant to ask - is it a good idea to store 1 time's data (value1-4)
per row
in 24*31 rows? May be it is better and quicker to store, for example, 2
time's data per row (value1-4, day 1, value1-4, day 2) or other
structure?

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFByKie7aqtWrR9cZoRAhkNAJ9vQPL3Mdi4Z6xSi5S8y3Aqih/aXwCghZyi
sNOrQnGMA1kCXxqbhNjPQjs=
=BqP5
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

On Tuesday 21 December 2004 21:21, Bruno Wolff III wrote:

On Tue, Dec 21, 2004 at 20:47:31 +0000,

"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Ok, this is a real example:

CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...

daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);

That approach is reasonable but depending on what the val* columns mean
you might each of those as a separate role. You didn't add a lot of
information, but that they appear to be the same type suggests that you
might want one row per value. But without knowing what they mean it is
hard to say.

OK, i want to store ststistic information, ingoing/outgoing traffic,
ingoing/outgoing errors(val1-val4) for example...

--
Man's unique agony as a species consists in his perpetual conflict between
the desire to stand out and the need to blend in.
-- Sydney J. Harris

Now playing: (Воскресение) - Музыкант.mp3
AutoGenerated by fortune & xmms...

On Tuesday 21 December 2004 22:00, Bruno Wolff III wrote:

On Wed, Dec 22, 2004 at 00:16:06 +0000,

"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

On Tuesday 21 December 2004 21:21, Bruno Wolff III wrote:

On Tue, Dec 21, 2004 at 20:47:31 +0000,

"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Ok, this is a real example:

CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...

daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);

That approach is reasonable but depending on what the val* columns mean
you might each of those as a separate role. You didn't add a lot of
information, but that they appear to be the same type suggests that you
might want one row per value. But without knowing what they mean it is
hard to say.

OK, i want to store ststistic information, ingoing/outgoing traffic,
ingoing/outgoing errors(val1-val4) for example...

Those sound like different domains then for each column. So you probably do
want to keep the 8 values in one row.
I also noticed that you marked a lot of these values as NULL. I think that
daypos, hourpos and id form the primary key and you probably don't want to
allow NULLs for these.

Yes, of course, this is example only.
But relation between tables is not important now...
I whant to ask - is it a good idea to store 1 time's data (value1-4) per row
in 24*31 rows? May be it is better and quicker to store, for example, 2
time's data per row (value1-4, day 1, value1-4, day 2) or other structure?

I absolutly agree with you, thank.

On Wed, Dec 22, 2004 at 01:24:57 +0000,
"Vladimir S. Petukhov" <vladimir@sycore.org> wrote:

Yes, of course, this is example only.
But relation between tables is not important now...

It is important for design. You should use a normallized design initially
and consider denormalized designs if you have peformance problems.

I whant to ask - is it a good idea to store 1 time's data (value1-4) per row
in 24*31 rows? May be it is better and quicker to store, for example, 2
time's data per row (value1-4, day 1, value1-4, day 2) or other structure?

The normalized design is one set of values (for a particular day and time)
per row. This will make querying the data easier.

As far as what optimizations might be a good idea when you have problems,
we can't tell you, because you haven't told us what typical queries look
like. It still may be that postgres is overkill for your purposes and
something more lightweight like Berkeley DB might be better for you.

On Tue, Dec 21, 2004 at 17:50:06 -0500,
"Frank D. Engel, Jr." <fde101@fjrhome.net> wrote:

Also, given the amount of data you are talking about, and assuming that
you are inserting all of this data in one big lump, you may wish to
VACUUM FULL after doing your INSERTs (not after each one, of course --
after doing all of the INSERTs, or after doing a big chunk of them. If
data is inserted incrementally over a period of time, then just do the
VACUUM ANALYZE every so often during that time, and you shouldn't have
a problem).

You only need to vacuum after updates or deletes. After mass inserts you
just want to run ANALYSE.