Re: trigger question

Hi Bastiaan,

I'm not too sure about this. I have been working with a similar application were such an apparently 'simple' solution resulted in major performance problems.
Generally speaking I am very strict about adhering to 3NF. The only reason to divert from 3NF is performance.
Right now I'm working with a design where for some mysterious reason many 'master' tables have a column where the number of detail records is maintained. The select to calculate these adhoc, when you *really* need them, is quite simple. Right now a series of triggers keeps those columns in sync. This is such a gordian knot, you are amazed this hasn't evolved into a never-ending loop.
For me this is again an example were using the formal approach in data-design would have resulted in a better application.

Cheers,

Sybrand Bakker, Oracle DBA

"Bastiaan Schaap" <bschaap_at_desyde.nl> wrote in message news:newscache$84cpyf$5kj$1_at_lnews.actcom.co.il...
> Hi Sybrand,
>
> I'd like to comment on your comment that if you have two tables with a 1-1
> relationship, it's a case of bad table design. Sometimes it is necesary
 for
> data integrity.. For example if I have a table with some basic personal
> info. This will contain e.g. first name, last name and address. Now let's
> say the address as a whole is optional, but if you decide to fill the
> address columns, the address line 1 and zip are mandatory. How do you
 think
> to implement this on table level? You could do it with a trigger, but it
 is
> relatively a lot of work for such a simple table. It's easier to create
 two
> tables, one for personal info, with a foreign key to the address table.
 Make
> the foreign key column optional, and in the address table make the address
> line 1 and zip mandatory and voila! Integrity is ensured. Looks much
> easier.. Yes, the insert operation is a bit more complicated, but
 personally
> I always like it when the datamodel itself applies as much rules as
> possible.
>
> I agree with you that in most cases two tables with a 1-1 relationship is
> bad design, but still I think that sometimes you may wish to choose to do
> so. What do you think?
>
> Cheers,
>
> Bastiaan Schaap
> Oracle web development,
> Desyde BV - Baarn
> http://www.desyde.nl/
> tel. +31355411711
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message
> news:965242751.28509.0.pluto.d4ee154e_at_news.demon.nl...
> > No, the syntax I provided you with is correct. Either trust me or read
 the
> > manual, and you will see I am correct.
> > Also AFAIK a column can not act as a primary key and a foreign key at
 the
> > same time, or you have a case of bad table design. If there is a truly
> > 1-on-1 relationship between table1 and table2, one of the two tables
> > shouldn't have existed at all.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> >
> > "luio" <lctNOlcSPAM_at_hotmail.com.invalid> wrote in message
> > news:1cc42a50.73022d87_at_usw-ex0104-028.remarq.com...
> > > alter table table2 add constraint <whatever name> foreign key
> > > (id) references table1 *on delete cascade*
> > > (provided table1.sid is the primary key of that table)
> > >
> > > there is on exception. how about the table2.id is the primary
> > > key of table2. but sid the primary key of table?
> > > then
> > > I use
> > > alter table table1 add constraint <whatever name> foreign key
> > > (id) references table2 *on delete cascade*
> > > to implement the delete on table 2 whenever I delete sth from
> > > table1?
> > >
> > >
> > >
> > > thanks
> > >
> > >
> > >
> > >
> > > The solution I posted previously should also work, but it is
> > > more cumbersome.
> > >
> > > Hth,
> > >
> > >
> > >
> > >
> > > -----------------------------------------------------------
> > >
> > > Got questions? Get answers over the phone at Keen.com.
> > > Up to 100 minutes free!
> > > http://www.keen.com
> > >
> >
> >
>
>
Received on Thu Aug 03 2000 - 00:00:00 CDT