From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: Records exactly the same.
Date: Mon, 23 Jul 2001 16:59:18 +0100
Message-ID: <003801c11390$6efd58c0$1001a8c0@archonet.com> (raw)
"Fons Rave" <fonzzz@xs4all.nl> wrote in message
news:9jbrpj$r67$1@news1.xs4all.nl...
> > Well, there isn't an easy answer for you ... because you've designed
> > your database wrong. Records should *never* be the same. That is, ni
> > fact, one of the cardinal rules of Relational Database Design.
>
> Well, I started with "I'm a beginner". But I'm sure there's no reason NOT
to
> accept two records that are exactly the same. In the example I gave, it is
clear
> that the information I want to store can contain two records that are
exactly
> the same; doing the same thing, on the same day, for the same amount of
time. In
> this case it is the technical structure that doesn't want it like that. So
I
> have to change it to make it work.
OK - so you enter two records, one hour in the morning and one hour in the
afternoon. You then realise you *didn't* work in the afternoon and you want
to delete one of the records. You can't because you can't build a query that
specifies the one without the other. You can't delete only one, you can't
update only one and you can't select only one. This is because the only way
to pick out a piece of data in SQL is by it's values - there is no "file
position" or "record number" (well there's OID but you're best off leaving
that).
If you want to store two separate pieces of information, make sure they
differ in some way (timestamp, serial-number, whatever). If you can't think
of anything useful to distinguish between records add a SERIAL column called
"id".
If you don't *want* two records, but just want the total hours worked that
day, then restrict your system to one record per person (or job or whatever
it was) per day. You can either do this in your application, or (better but
harder) do it with triggers in PostgreSQL itself.
If you can't quite see where the problem is at the present time, just add an
"id" or "seq" field of type SERIAL and get on with your project. The reason
people on the list are shouting is because we got burnt at some time with
exactly this thing and we're trying to stop that happening to you.
Oh - check the glossary at techdocs.postgresql.org and look at "Normal
Forms" (numbered 1st NF, 2nd NF etc). Check out a book on RDBMS theory too
if you get a chance.
HTH
- Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com
Subject: Re: Re: Records exactly the same.
In-Reply-To: <003801c11390$6efd58c0$1001a8c0@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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