Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Database design question
> Running Oracle 8.0.5.2 on Solaris 2.7.
> I would like to solicit people's opinions regarding a design
aspect of
> one of our production databases.
>
> There are fields within various tables which establish
"relationships"
> based upon data within different columns in other tables --
not
> established by keys, and updated by triggers contantanating
data and
> using sequences.
>
> Example: Table one has, say, an owner column which would
have data
> within it, and based upon that data, it would have a "quasi
-
> relationship" to a different column in another table.
> So, for instance Table one would have rows with company123 in
it's owner
> column, and say a "quasi-relationship" to Table two would be
attempted
> to be established by that table having company123 in say an
Account
> column. The Primary Key Data for these tables would then
need to match
> as well. This type of "relationship" building is done
throughout the
> database.
>
> These columns are updated by triggers which concatanate data
and use
> sequences, which are cached.which This seems to me like it
could cause
> trouble down the road if there are rollbacks, imports etc..
>
> If this description makes any sense, opinions are welcome.
>
> Thanks in advance
>
>
>
I get the picture. some time you may need to keep this type of
relation ship, especially when you have important business
component (account no, company id etc.,) in one table and
further relation ship to it is kept in multiple tables. You may
not want to keep the business component as the key in satellite
table and instead have running seq. no as key in those
satellite table.
I donot think you will have problems in triggers, if all the
triggers are executed before commit. If rollback happens, all
of them would get rolled back.
However, as you have pointed, there is likely hood of problems
with sequences, as they are not exported/imported. Hence they
are likely to retain old values after import. This might result
in duplicate key problem. One solution could be go in for
tables for these sequence numbers, if your application allows
that.
>
==========MODIFIED=========
1/5/00 12:47 AM
-- This answer is courtesy of QuestionExchange.com http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=9525&cus_id=USENET&qtn_id=11526Received on Fri Jan 07 2000 - 00:00:00 CST