Stubborn Question: Maintain Referential Integrity of Redundant data

[Sam]

Is there a way to maintain real Referential Integrity of Redundant
fields in two tables?

I have searched the newsgroup and it seems the general advice is to not
have redundant data at all. But sometimes space is less a concern than
computing power, so not having to join tables all the time to view data
is preffered.

Any comment is appreciated. Thank you.

-Sam

 

[Terry Kreft]

No, you have to code for it.

The problem with denormalised data is maintaining integrity, this relies on
competent programming.

 

[Allen Browne]

You are aware that storing redundant data violates the most basic
normalization rules?

If your reason for doing this is, "not having to join tables all the time,"
my guess is that you don't understand the amount of work you are taking on
to verify that these redundant copies are always managed with perfect
accuracy in every possible scenario. You could lean about all the pitfalls
by spending a few months falling into them all (if you ever did actually
find all of them that way), but it would be *much* more profitable for you
to spend your time understanding what normalization is, why these rules
exist, and how much work they save you in the long term.

Kinda like the ancient ship captain who had two chronometers, and therefore
never really knew which one was right.

 

[Sam]

Wow, strong responses. OK, I got it, I won't pursue this matter any
further. I asked because I want to know if there's any quick fix for
the problem. I guess not.

Thank you.

-Sam

 

[Craig Alexander Morrison]

It also relies on the data redundancy being managed by the database and not
the application(s).

Jet in this regard is of no use and one should be using DB2, Oracle or SQL
Server.

 

[Terry Kreft]

Expand and explain please as you appear to be wrong.

 

[Craig Alexander Morrison]

How do you manage data redundancy in Jet?

Remember Data Redundancy should be avoided, if it cannot it needs to be
managed by the DBMS.

Only the larger DBMSs can perform this management.

In Jet you can only do it at the application level and this is high risk by
comparison.

In general as I said above you should avoid Data Redundancy and Data
Interdependence but this cannot always be achieved and still maintain a
business system.

OLAP is a case in point, it is by its very nature storing redundant data but
it is managed by the DBMS.

 

[Terry Kreft]

I'm not sure what you mean by "Only the larger DBMSs can perform this
management." could you expand on that please.

 

[Douglas J. Steele]

I'm guessing, Terry, that Craig's talking about using triggers to manage the
redundant data. However, it would be nice if he answered.

 

[Terry Kreft]

Hi Doug,

Yep, That's exactly what I thought he was talking about and if so, was the
reason why I was going to say that essentially he was wrong.

 

[Craig Alexander Morrison]

No, I do not think so.

But tell me what exactly you think I am wrong about as you did not address
the question

You indicated to the OP that they could do "it" in code, however that could
only be at application level and this is easily subverted.

Or are you actually saying that I should calculate the balances of 10
million bank accounts to arrive at the customer deposits total in order to
have a pure relational database with no redundant data.

You know I always try to start with a pure design so that I know what
redundancy we need to manage.

BTW If you are using Jet then I would strongly recommend that you stay with
the "pure" design.

 

[Craig Alexander Morrison]

I may be Scottish but I am watching England in the World Cup and after that
the Formula One Grand Prix of Canada.

....I would love to know why Terry thinks I am wrong, I would wholeheartedly
agree I was wrong if I was using Jet and then expecting Access at the
application level to manage the redundancy.

I use DB2 as the backend and therefore all necessary redundancy is strictly
managed as you say via Triggers and Triggered Actions.

To quote from Chris Date "Incidentally, we do not mean to suggest that all
redundancy can or necessarily should be eliminated...However, we do mean to
suggest that any such redundancy should be carefully controlled-that is the
DBMS should be aware of it, if it exists and should assume responsibility
for "propagating updates"."

 

[Terry Kreft]

Well, you're obviously not going to actually say why you said what you did
so I'll go along with my assumption.

The reason why I believe that what you said was essentially wrong is that I
believe you are referring to using triggers to maintain RI between redundant
data in tables.

My reasoning goes like this:-
Stored procedures are not part of the inbuilt DRI of the database
Triggers are only a special sort of SPROC therefore they are not part of
the inbuilt DRI of the database.

To put it another way triggers have to be coded, they are a developed item
and are part of the application rather than the dbms.

The rest of your post seems to involve a fantasy conversation or a
conversation with someone other than myself.

 

[Terry Kreft]

Triggers are specialised SPROCS they do not form part of the inbuilt DRI,
that's why I say you're wrong. They _are_ part of the application

 

[Craig Alexander Morrison]

I understand now, and you would appear to be wrong.

 

[Craig Alexander Morrison]

The rest of your post seems to involve a fantasy conversation or a

conversation with someone other than myself.

Sent to Doug when he stated it would be nice if I answered.

Did you actually read my post or are you so positive that you are right and
I am wrong.

 

[Craig Alexander Morrison]

Excerpt from my message to Doug

To quote from Chris Date "Incidentally, we do not mean to suggest that all
redundancy can or necessarily should be eliminated...However, we do mean to
suggest that any such redundancy should be carefully controlled-that is the
DBMS should be aware of it, if it exists and should assume responsibility
for "propagating updates"."

 

[Terry Kreft]

You responded to my response to Doug.

Did you actually read this thread or are you still confused?

 

[Terry Kreft]

I fail to see the relevance of your response.

 

[Terry Kreft]

Patently, appearances can be deceptive.