Re: trigger question

Hi Sybrand,

You are absolutely right. Although the example I gave was a very simplified example. I agree with you that in 99% of the cases such models are not correct in terms of performance and maintenance. However I saw set-ups that used this solution in very elegant and robust ways. Therefore my opinion is that fully conforming to third level normalisation is not a given fact, but is something that will have to be decided from case to case. We don't live in a black and white world.

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:965334709.4064.0.pluto.d4ee154e_at_news.demon.nl...
> 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 Sun Aug 06 2000 - 00:00:00 CDT