"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
