The detail, if we speak of the same thing, is all the rows of documents
that act upon those items. The rows of movements have a foreign key to
that two field item primary key.

We started by working with surogates keys everywhere.
Though I liked the concept (because I came from the hierarchical/network
database model world then moved on to the SQL game), we abandonned it.
Programmers were doing too much side queries to exploit the surogates
keys in common requests.

Is is sometimes better to accept the inherent redundancy of some
information in "classical" foreign keys. The added cost to let the system
cascade down (occasional) updates to the item keys is nothing compared to
the extra ease in programming and extra security in rebuilding a damaged
database.

Olivier Mascia,  om@...,  Senior Software Engineer
T.I.P. Group S.A.,  www.tipgroup.com,  Director


-----Original Message-----
From: Jörg Schiemann <schimmi@...>
To: <ib-support@yahoogroups.com>
Date: Wed, 7 Feb 2001 16:52:05 +0100
Subject: Re: [ib-support] Question about koncept

> Hi Olivier,
>
> that's a very good idea, but I think you don't have any detail
> table(s).
>
> Best regards,
> Jörg Schiemann
>
> ----- Original Message -----
> From: "Olivier Mascia" <om@...>
> To: <ib-support@yahoogroups.com>
> Sent: Wednesday, February 07, 2001 4:29 PM
> Subject: Re: [ib-support] Question about koncept
>
>
> > I use a similar scheme for items (inventory control).
> > I use a SMALLINT whose value is 0 for a normal item and something > 0
> for
> > a deleted item. My primary key on the item table is the item num
> itself
> > followed by the SMALLINT.
> >
> > Works pleasantly until now.
> >
> > Olivier Mascia, om@..., Senior Software Engineer
> > T.I.P. Group S.A., www.tipgroup.com, Director
> >
> >
> > -----Original Message-----
> > From: Jörg Schiemann <schimmi@...>
> > To: "IB-Support" <ib-support@yahoogroups.com>
> > Date: Wed, 7 Feb 2001 12:48:12 +0100
> > Subject: [ib-support] Question about koncept
> >
> > > Hi,
> > >
> > > maybe it's a silly question, but in that case I'll take it.
> > >
> > > I want to be able to undelete and to see who deletes the post.
> > > After deleting a customer the customer_no should be available
> again.
> > > Which koncept schould I use?
> > >
> > > I have a customer table
> > >
> > > CREATE TABLE CUSTOMER
> > > (
> > > CUSTOMER_NO NUMERIC(18,0) NOT NULL,
> > > NAME VARCHAR(50),
> > > CREATED_BY D_USERNAME,
> > > CREATE_DATE D_NOW,
> > > CHANGED_BY D_USERNAME,
> > > CHANGE_DATE D_NOW,
> > > CONSTRAINT PK_CUSTOMER_NO PRIMARY KEY (CUSTOMER_NO)
> > > );
> > > CREATE INDEX IDXCUSTOMER_NAME ON CUSTOMER (NAME);
> > >
> > > Now, I don't want that the user should be able to delete a
> customer,
> > > therefore I added a field DELETED.
> > >
> > > CREATE TABLE CUSTOMER
> > > (
> > > CUSTOMER_NO NUMERIC(18,0) NOT NULL,
> > > NAME VARCHAR(50),
> > > DELETED D_BOOLEAN,
> > > CREATED_BY D_USERNAME,
> > > CREATE_DATE D_NOW,
> > > CHANGED_BY D_USERNAME,
> > > CHANGE_DATE D_NOW,
> > > CONSTRAINT PK_CUSTOMER_NO PRIMARY KEY (CUSTOMER_NO)
> > > );
> > > CREATE INDEX IDXCUSTOMER_NAME ON CUSTOMER (NAME);
> > >
> > > If the user now 'deletes' a customer the field DELETED changes to
> true
> > > and
> > > the customer is no longer visible in the view.
> > > The only problem is that the customer_no the deleted customer had
> is
> > > no
> > > longer available and my customer, who I'm programming for, wants to
> be
> > > able
> > > to use the customer_no to a new customer.
> > > First I thouht about to create 2 new fields CUSTOMER_ID and
> > > CUSTOMER_NO_OLD
> > > like
> > >
> > > CREATE TABLE CUSTOMER
> > > (
> > > CUSTOMER_ID NUMERIC(18,0) NOT NULL,
> > > CUSTOMER_NO NUMERIC(18,0) NOT NULL,
> > > CUSTOMER_NO_OLD NUMERIC(18,0) DEFAULT 0,
> > > NAME VARCHAR(50),
> > > DELETED D_BOOLEAN,
> > > CREATED_BY D_USERNAME,
> > > CREATE_DATE D_NOW,
> > > CHANGED_BY D_USERNAME,
> > > CHANGE_DATE D_NOW,
> > > CONSTRAINT PK_CUSTOMER_ID PRIMARY KEY (CUSTOMER_ID),
> > > UNIQUE (KUNDEN_NR)
> > > );
> > > CREATE UNIQUE INDEX UIDX_CUSTOMER_NO ON CUSTOMER (CUSTOMER_NO);
> > > CREATE INDEX IDXCUSTOMER_NAME ON CUSTOMER (NAME);
> > >
> > > And foreign keys from other table points to CUSTOMER_ID
> > > If the user now deletes a customer DELTED becomes True,
> CUSTOMER_NO_OLD
> > > becomes CUSTOMER_NO and CUSTOMER_NO becomes a minus value.
> > > See the trigger I use.
> > >
> > > SET TERM !! ;
> > > CREATE TRIGGER TG_BU_AUDIT_DEL_CUSTOMER FOR CUSTOMER BEFORE UPDATE
> > > POSITION
> > > 99 AS
> > > BEGIN
> > > IF ( new.DELETED = 1 )
> > > THEN BEGIN
> > > new.CUSTOMER_NO_OLD = old.CUSTOMER_NO;
> > > new.CUSTOMER_NO = GEN_ID(GEN_CUSTOMER_NO_DEL, 1) * -1;
> > > End
> > > END !!
> > > SET TERM ; !!
> > >
> > > But do I actually need CUSTOMER_ID?
> > > The foreign keys in other tables do cascade update (ON UPDATE
> CASCADE).
> > >
> > > The advantage I see with CUSTOMER_ID is, that the database don't
> need
> > > to a
> > > cascade update to all involved tables.
> > >
> > > Q1. The Question is, how much work is that for the database to do a
> > > cascade
> > > update, is it worth it to skip it with CUSTOMER_ID ?
> > >
> > > Q2. Is there a better way?
> > >
> > > Q3. How do you handle deleting?
> > >
> > > Q4. Is that all nonsense?
> > >
> > > Thanks in advance for your reply.
> > >
> > > Best regards
> > >
> > > Jörg Schiemann
> > >
> > >
> > > ------------------------ Yahoo! Groups Sponsor
> > > ---------------------~-~>
> > > eGroups is now Yahoo! Groups
> > > Click here for more details
> > > http://click.egroups.com/1/11231/0/_/_/_/981546427/
> > >
> ---------------------------------------------------------------------_-
> > > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > ib-support-unsubscribe@egroups.com
> > >
> > >
> >
> >
> >
> >
> > To unsubscribe from this group, send an email to:
> > ib-support-unsubscribe@egroups.com
> >
> >
> >
> >
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-~>
> eGroups is now Yahoo! Groups
> Click here for more details
> http://click.egroups.com/1/11231/0/_/_/_/981561005/
> ---------------------------------------------------------------------_-
> >
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>