pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable design question...
12+ messages / 8 participants
[nested] [flat]
* Table design question...
@ 2002-08-06 09:17 Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 1 reply; 12+ messages in thread
From: Joerg Hessdoerfer @ 2002-08-06 09:17 UTC (permalink / raw)
To: pgsql-sql
Hi, all!
Ok, I've banged my head on this, and would like to hear some opinion from the
list (I'm just short of trying it, though I don't have the hardware yet).
We have along running data logging application, where we essentially get data
records at varying speeds from a facility.
The data can be treated as arrays of floats, arrays of strings or binary
dumps, where each element in the arrays represent one value.
Additionally, there is the possibility of having a varying number of
'multisampler' values in one packet, where each value itself is an array.
Note, that in one packet the arrays need not be of same length!
So, this gives me at least 2 tables for the data, one that can handle the
'scalar' data (float, string, binary) and one that needs an array for every
value (floats only).
All data will have two time stamps (facility and receive times), as data may
be sent off-line to the logger.
So, all this boils down to the simple question: Is it better (in terms of
indices) to have a seperate table for the time stamps and join the data to it
via an foreign key id field, or have both timestamps in each data table?
What if I would create more data tables?
Please keep in mind, that I expect to have multi-million records in each
table ;-)
Thx,
Joerg
--
Leading SW developer - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question...
@ 2002-08-06 10:35 Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 1 reply; 12+ messages in thread
From: Joerg Hessdoerfer @ 2002-08-06 10:35 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Thank you for your help,
On Tuesday 06 August 2002 11:46, you wrote:
> On Tuesday 06 Aug 2002 10:17 am, Joerg Hessdoerfer wrote:
>
> [ data logging with variable formats for data some of which might be arrays
> for a single item - nice :-/ ]
>
Yep. This is what it boils down to - and noone can tell me in advance what
the data type for each entry really is... it can even change mid-operation
(now how's that for design!).
> > So, all this boils down to the simple question: Is it better (in terms of
> > indices) to have a seperate table for the time stamps and join the data
> > to it via an foreign key id field, or have both timestamps in each data
> > table? What if I would create more data tables?
>
> First instinct is to have timestamps, data-type and reference in one table
> and separate tables for each data-type for the data.
> Second instinct - have one table with null columns for unused types and
> take a hit on the wasted space.
>
> But - it really depends on how you're going to use the data. Will you be
> listing all entries between time X and Y or viewing entries of a specific
> type? Do you have any feel for what the common operations are going to be?
>
> - Richard Huxton
Well, the access is mainly 'export dumps' in a special format, where varying
data items need to be exported from between two time stamps. So there was my
idea using the arrays coming from. I'd have something like
CREATE TABLE data_float(
times_id int4 references times(id),
names int4[],
values float[]
);
Where names would index into a table with the name->id mapping, because I
could throw out all unneeded data items after the SELECT(), thus having much
less data overhead (and much less records, we're talking ~500 data items per
packet - varying, of course ;-).
But is the above better than
CREATE TABLE data_float(
scet timestamp without time zone,
recv timestamp without time zone,
names int4[],
values float[]
);
given I have at least two data tables (to acommodate for the 'array in one
value' data items, which are much less common (1/250, roughly)).
Thanks,
Joerg
--
Leading SW developer - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question...
@ 2002-08-06 12:59 Richard Huxton <dev@archonet.com>
parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Huxton @ 2002-08-06 12:59 UTC (permalink / raw)
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: pgsql-sql
On Tuesday 06 Aug 2002 11:35 am, Joerg Hessdoerfer wrote:
> Thank you for your help,
>
> On Tuesday 06 August 2002 11:46, you wrote:
> > On Tuesday 06 Aug 2002 10:17 am, Joerg Hessdoerfer wrote:
> >
> > [ data logging with variable formats for data some of which might be
> > arrays for a single item - nice :-/ ]
>
> Yep. This is what it boils down to - and noone can tell me in advance what
> the data type for each entry really is... it can even change mid-operation
> (now how's that for design!).
> Well, the access is mainly 'export dumps' in a special format, where
> varying data items need to be exported from between two time stamps. So
> there was my idea using the arrays coming from. I'd have something like
>
> CREATE TABLE data_float(
> times_id int4 references times(id),
> names int4[],
> values float[]
> );
> But is the above better than
>
> CREATE TABLE data_float(
> scet timestamp without time zone,
> recv timestamp without time zone,
> names int4[],
> values float[]
> );
>
> given I have at least two data tables (to acommodate for the 'array in one
> value' data items, which are much less common (1/250, roughly)).
If your exports are all of one type (i.e. all arrays or all integer or all
float) I'd go for (B) since that makes things nice and easy. On the other
hand, if you want to export different types in one query you'll need to do a
union unless you put all possible fields in one table:
CREATE TABLE data_all (
scet timestamp,
recv timestamp,
data_type char(1) CHECK (data_type IN ('i','f','a')),
intval int4,
floatval float,
arr_names int4[],
arr_values int4[]
);
If you go the route of (A) or (B) I'd run a few tests to see which is more
efficient. You can always hide the implementation details behind a view and
some functions.
- Richard Huxton
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question...
@ 2002-08-06 15:24 Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 12+ messages in thread
From: Joerg Hessdoerfer @ 2002-08-06 15:24 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Hi again,
On Tuesday 06 August 2002 14:59, you wrote:
[...]
> If your exports are all of one type (i.e. all arrays or all integer or all
> float) I'd go for (B) since that makes things nice and easy. On the other
> hand, if you want to export different types in one query you'll need to do
> a union unless you put all possible fields in one table:
>
> CREATE TABLE data_all (
> scet timestamp,
> recv timestamp,
> data_type char(1) CHECK (data_type IN ('i','f','a')),
> intval int4,
> floatval float,
> arr_names int4[],
> arr_values int4[]
> );
>
> If you go the route of (A) or (B) I'd run a few tests to see which is more
> efficient. You can always hide the implementation details behind a view and
> some functions.
>
> - Richard Huxton
>
[...]
Yes, I'm currently going the testing route - my initial idea was also
something like your table def above, but this rules itself out (I really
think 500,000,000 rows is a bit too much ;-). Don't get me wrong, I think a
'big iron' could manage that easily, but I need to make do with a quite
common PeeCee (running Linux, that is)...
Thanks again for your input,
Joerg
--
Leading SW developer - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Table design question
@ 2006-06-01 11:05 David Clarke <pigwin32@gmail.com>
0 siblings, 4 replies; 12+ messages in thread
From: David Clarke @ 2006-06-01 11:05 UTC (permalink / raw)
To: pgsql-sql
I'm reading Joe Celko's book SQL Programming Style for the second time
and although I've been an OO developer for quite a few years I'm
fairly green wrt SQL. Joe is obviously something of a curmudgeon and I
would fall squarely into his newbie OO developer ordinal scale and I'm
trying to avoid the slide into stupid newbie OO developer.
So I'm designing a table and I'm looking for an appropriate key. The
natural key is a string from a few characters up to a maximum of
perhaps 100. Joe gets quite fierce about avoiding the use of a serial
id column as a key. The string is unique in the table and fits the
criteria for a key. So should I follow Joe's advice and use my natural
key as the primary key? It sounds reasonable but it will mean at least
one other table will have the string as a foreign key. My postgres
intro book has id columns all over the place but is it really that big
an issue these days to have a 100 character primary key? Are there
postgres-specific implications for either approach?
Thanks
Dave
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-01 13:41 Rod Taylor <pg@rbt.ca>
parent: David Clarke <pigwin32@gmail.com>
3 siblings, 0 replies; 12+ messages in thread
From: Rod Taylor @ 2006-06-01 13:41 UTC (permalink / raw)
To: David Clarke <pigwin32@gmail.com>; +Cc: pgsql-sql
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the
> criteria for a key. So should I follow Joe's advice and use my natural
> key as the primary key? It sounds reasonable but it will mean at least
> one other table will have the string as a foreign key. My postgres
Unfortunately as a result of the implementation of most commercial
databases (and PostgreSQL), using a string as the primary key will cause
a performance hit.
Yes, normally it is better and for things like status flags and other
constant values that rarely change it is a good approach.
I would tend to add a SERIAL and make it the primary key (using it in
foreign tables) but also making the string column unique and not
null'able.
Essentially it gives the table two primary keys in the hope that some
day PostgreSQL will add a layer of abstraction between primary/foreign
key lookups and presentation that allows for usage of strings directly
without a performance hit.
--
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-01 15:40 Greg Stark <gsstark@mit.edu>
parent: David Clarke <pigwin32@gmail.com>
3 siblings, 0 replies; 12+ messages in thread
From: Greg Stark @ 2006-06-01 15:40 UTC (permalink / raw)
To: David Clarke <pigwin32@gmail.com>; +Cc: pgsql-sql
"David Clarke" <pigwin32@gmail.com> writes:
> is it really that big an issue these days to have a 100 character primary
> key? Are there postgres-specific implications for either approach?
It's exactly the same size issue as ever. A 20% increase in space usage is a
20% performance hit in certain types of queries regardless of how fast or
cheap your hardware has become.
This is an issue where reasonable people differ quite vociferously so you may
get contradictory responses. But really it depends heavily on the exact
circumstances and requires a judgement call based on experience. Any time
someone tries to sum it up with a blanket rule it's going to be wrong some of
the time.
But that said I also tend to tilt towards creating serial ids. Unless the
string is already a synthetic unique identifier created by your application
you can't really trust its "uniqueness" for all time. Plenty of people have
built databases using natural unique keys that turned out to be not so unique
or turned out to just be a poor choice for external reasons (think of Social
Security Numbers, for example).
I've had my string unique identifiers corrupted by uppercasing, declared case
insensitive, declared private information that couldn't be leaked, and
declared offensive words that had to be updated. Each of which is a pain to
deal with when it's your primary key.
--
greg
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-01 16:44 postgres@jal.org
parent: David Clarke <pigwin32@gmail.com>
3 siblings, 0 replies; 12+ messages in thread
From: postgres@jal.org @ 2006-06-01 16:44 UTC (permalink / raw)
To: David Clarke <pigwin32@gmail.com>; +Cc: pgsql-sql
On Thu, 01 Jun 2006, David Clarke wrote:
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the
The use of surrogate keys is a mostly religious issue. Celko is an
Orthodox, many others are Reform. Where you want to align yourself
is a personal choice.
In defense of the Reform movement, I'd note that modern DBs are more
performant with ints than varchars. More importantly, I've found it
much easier to modify DBs designed with surrogate keys than natural
keys, especially when natural keys span columns. It allows a rather
simple convention for coders to write against, and avoids some messy
modification issues when the spec changes.
It is not my intention to bash the purists, and there are good arguments
on the Ortho side, too. I'm merely giving a bit of advice from the
point of view of someone who lives in the constant evolution side
of DB usage.
-j
--
Jamie Lawrence jal@jal.org
When I was a boy I was told that anybody could become President.
Now I'm beginning to believe it.
- Clarence Darrow
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-01 17:45 codeWarrior <gpatnude@hotmail.com>
parent: David Clarke <pigwin32@gmail.com>
3 siblings, 1 reply; 12+ messages in thread
From: codeWarrior @ 2006-06-01 17:45 UTC (permalink / raw)
To: pgsql-sql
I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my
PKEY's -- as an absolute rule -- I guess I am a purist... Everything else
(the other columns) can have unique constraints, etcetera and be FOREIGN
KEYS, etc...
Try INSERTING your 100 character "natural" key into a table with 10M++ rows
only to find out there there is already a duplicate.... talk about a
performance hit.... or SELECT -- you end up using way too much RAM and
bandwidth -- unecessarily...
IMHO: You ought to use a numeric, auto-generated sequence (SERIAL) for you
PKEY's ...
""David Clarke"" <pigwin32@gmail.com> wrote in message
news:12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com...
> I'm reading Joe Celko's book SQL Programming Style for the second time
> and although I've been an OO developer for quite a few years I'm
> fairly green wrt SQL. Joe is obviously something of a curmudgeon and I
> would fall squarely into his newbie OO developer ordinal scale and I'm
> trying to avoid the slide into stupid newbie OO developer.
>
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the
> criteria for a key. So should I follow Joe's advice and use my natural
> key as the primary key? It sounds reasonable but it will mean at least
> one other table will have the string as a foreign key. My postgres
> intro book has id columns all over the place but is it really that big
> an issue these days to have a 100 character primary key? Are there
> postgres-specific implications for either approach?
>
> Thanks
> Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-01 19:56 Chris Browne <cbbrowne@acm.org>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Chris Browne @ 2006-06-01 19:56 UTC (permalink / raw)
To: pgsql-sql
"codeWarrior" <gpatnude@hotmail.com> writes:
> I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my
> PKEY's -- as an absolute rule -- I guess I am a purist... Everything else
> (the other columns) can have unique constraints, etcetera and be FOREIGN
> KEYS, etc...
>
> Try INSERTING your 100 character "natural" key into a table with 10M++ rows
> only to find out there there is already a duplicate.... talk about a
> performance hit.... or SELECT -- you end up using way too much RAM and
> bandwidth -- unecessarily...
Celko is decidedly *NOT* promoting the notion that you should use a
100 byte long "natural key."
Jamie's comments of "Orthodox versus Reform" seem reasonably
appropriate in outlining something of the difference between the
positions.
Just because Celko is "Orthodox" doesn't mean he's *stupid*; he makes
all sorts of noises about using some ISO standard (11179) such that
your primary key values are actually described in some authoritative
manner.
<http://metadata-standards.org/11179/;
If you go about using ISO-11179, then, in principle, you could
register your scheme for describing IDs for the objects in your system
with some ISO people so that others might even become aware of the
official status of this.
I may not care for doing this; you may not either; a company that
builds auto parts that they want to sell into the automotive industry
may care about standardizing their part IDs quite a lot.
They're not interested in generating stupidly long identifiers; that's
inefficient in many ways...
> IMHO: You ought to use a numeric, auto-generated sequence (SERIAL)
> for you PKEY's ...
That's definitely the "Reform" position ;-), and it is a position that
can be rationally held.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/multiplexor.html
Rules of the Evil Overlord #185. "If I capture an enemy known for
escaping via ingenious and fantastic little gadgets, I will order a
full cavity search and confiscate all personal items before throwing
him in my dungeon." <http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-01 23:44 postgres@jal.org
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 12+ messages in thread
From: postgres@jal.org @ 2006-06-01 23:44 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
On Thu, 01 Jun 2006, Chris Browne wrote:
> Celko is decidedly *NOT* promoting the notion that you should use a
> 100 byte long "natural key."
>
> Jamie's comments of "Orthodox versus Reform" seem reasonably
> appropriate in outlining something of the difference between the
> positions.
Just to be clear, that was all I was trying to do. I probably should
have mentioned that any attempt to use such an attribute as a PK should
be met with a baseball bat or other shillelagh-ish implement, but was
interrupted several times during that email drafting.
> I may not care for doing this; you may not either; a company that
> builds auto parts that they want to sell into the automotive industry
> may care about standardizing their part IDs quite a lot.
This is another important point. In some situations, a rigid data model
can be a godsend to coders. If you happen to sit in such an enviable
position, I would encourage you to take advantage of it. (This doesn't
mean picking bad keys, of course.)
I liberally sprinkle surrogate keys around simply because most of the
projects I work on have transient requirements, so spontaneous rejiggery
and various pokery are both commonplace, and SKs provide "enough" data
integrity that the cost/benefit curve seems to peak there. Were I doing
projects that had longer release cycles, I'd re-evaluate that position,
and likely see a marginal reduction in bugs.
None of this should be taken as bashing Celko - he's a smart man and an
excellent source of advice.
-j
--
Jamie Lawrence jal@jal.org
When I talked to the president, he was loaded.
- Brent Scowcroft, Kissinger's assistant, 10/11/73
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Table design question
@ 2006-06-02 01:19 David Clarke <pigwin32@gmail.com>
parent: postgres@jal.org
0 siblings, 0 replies; 12+ messages in thread
From: David Clarke @ 2006-06-02 01:19 UTC (permalink / raw)
To: pgsql-sql
On 6/2/06, postgres@jal.org <postgres@jal.org> wrote:
> On Thu, 01 Jun 2006, Chris Browne wrote:
>
> > Celko is decidedly *NOT* promoting the notion that you should use a
> > 100 byte long "natural key."
> >
> > Jamie's comments of "Orthodox versus Reform" seem reasonably
> > appropriate in outlining something of the difference between the
> > positions.
>
> Just to be clear, that was all I was trying to do. I probably should
> have mentioned that any attempt to use such an attribute as a PK should
> be met with a baseball bat or other shillelagh-ish implement, but was
> interrupted several times during that email drafting.
>
> > I may not care for doing this; you may not either; a company that
> > builds auto parts that they want to sell into the automotive industry
> > may care about standardizing their part IDs quite a lot.
>
> This is another important point. In some situations, a rigid data model
> can be a godsend to coders. If you happen to sit in such an enviable
> position, I would encourage you to take advantage of it. (This doesn't
> mean picking bad keys, of course.)
>
> None of this should be taken as bashing Celko - he's a smart man and an
> excellent source of advice.
>
> -j
>
Thanks everyone who replied (and also for the insightful and measured
responses, not every news group is so lucky). I had progressed down
the path of the serial id column but re-reading Celko's book - he
spends some pages railing against "proprietary auto-numbering
features" - I wanted to feel confident I was making the right choice.
Thanks again
Dave
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2006-06-02 01:19 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-06 09:17 Table design question... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2002-08-06 10:35 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2002-08-06 12:59 ` Richard Huxton <dev@archonet.com>
2002-08-06 15:24 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2006-06-01 11:05 Table design question David Clarke <pigwin32@gmail.com>
2006-06-01 13:41 ` Re: Table design question Rod Taylor <pg@rbt.ca>
2006-06-01 15:40 ` Re: Table design question Greg Stark <gsstark@mit.edu>
2006-06-01 16:44 ` Re: Table design question postgres@jal.org
2006-06-01 17:45 ` Re: Table design question codeWarrior <gpatnude@hotmail.com>
2006-06-01 19:56 ` Re: Table design question Chris Browne <cbbrowne@acm.org>
2006-06-01 23:44 ` Re: Table design question postgres@jal.org
2006-06-02 01:19 ` Re: Table design question David Clarke <pigwin32@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox