that's a good point, ref integrity and 'deleted' items. I'll have to take a look at that as I make my next design. I'm
surpirsedthat I didn't think of it. But I probably would have experienced it soon, as I am getting ready to put data in
thedesign I'm on now.
One way I know that makes it all easier, is to use surrogate integer keys on all tables, i.e. sequences, as the primary
key.
Nigel J. Andrews wrote:
> On Thu, 19 Jun 2003, Matthew Nuzum wrote:
>
>
>>Regarding backup history:
>>
>>I have an application designed for novices. Apparently it's easy to hit the
>>"Delete" button, and then say yes to the "Are you sure you want to delete
>>this?" question even when they don't want to. Therefore I simply mark a
>>record as deleted. For example,
>>UPDATE table SET deleted='t' WHERE something=true;
>>
>>Then my application logic pretends it doesn't really exist until two days
>>later the user decides they want it back.
>>
>>It works very well for me.
>>
>
>
> But are you also taking care of the referential integrity issues, i.e. only
> disallowing tuples with a deleted = true from being referenced to and ensuring
> nothing references them at the time they are marked as deleted.
>
> It is a useful idea but as I know from a current project it requires
> reimplementing foreign key functionality. In this case the middleware only uses
> functions, one per statement, and nothing else, so I have been able to do much
> of this in those functions but it's still a pain. I even wrote a utility to
> take some of the leg work out of generating and maintaining quite a few
> functions but if I'd had time [and thought about these basically being foreign
> key constraints] I'd have looked at the existing foreign key code and seen if I
> could copy and amend it or just amend it in place.
>
>
> --
> Nigel Andrews
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
