Repost: Can someone help with this?

[mi...@oltronics.com]

I've posted this issue in the past, so I apologize for reposting or
otherwise cluttering up the message board...

The questions i'm looking for an answer to:
1) What is the best way to provide multi-level cascading updates to
tables when Paradox won't allow it through ref. integrity?
2) Should I link on a field that i know will not be edited so that
ref. integrity won't be an issue?

My previous post was as follows:

Because Paradox (5.0, 8.0) doesn't support multilevel cascading
updates,
what is the best way to provide this function? The logic for my data
model
is similar to the following (i'm tracking data for many different
buildings):

There is a building that contains many floors. Therefore, I have two
tables
linked 1-to-many.
Buildings.db --->---> Floors.db

Each floor has many different rooms. I would have a 1-to-many link
here,
too.
Floors.db --->---> Rooms.db

Each of these rooms has many different pieces of equipment. So another
1-to-many link...
Rooms.db --->---> Equipment.db

So basically this data model looks like this:
Buildings.db --->---> Floors.db --->---> Rooms.db --->--->
Equipment.db

What is the best way to index and link the tables in this sort of data
model? If the key value in Floors.db were to change (say everything
moved
to a new floor), how can i make this change cascade down through the
data
model so the data in Floors.db remains associated with the correct
data in
the Rooms.db and Equipment.db tables? Paradox handles this cascading
update
only on single level relationships.

[Mike Irwin]

Mike,

as you point out, Paradox doesn't offer cascading alterations or
deletions. Thus if in your example you have a set of tables and keys:

Building: BuildingCode
Floor: BuildingCode.FloorCode
Room: BuildingCode.FloorCode.RoomCode
Equipment: BuildingCode.FloorCode.RoomCode.EquipmentCode

then you'll have to write code to do your floor change with tCursors or
queries. Personally, I've found tCursors easier to get to work, 'cos I
always end up hunting for that last lock on a table <g>. Just remember to
do it when everyone's gone home for a long weekend, and make a backup or
two first !

Mike

[Michael Juul Hansen]

Suggestion

You have a room on a floor in a building
Why not keep only three tables Building.db, Rooms.db and Equipment.db?

BUILDING.DB
BuildingID Number*
BuildingComments A30
etc
ROOMS.DB
RoomID Number*
RoomDescription A30
BuildingID Number, RefInt
FloorID SmallInt
etc.
EQUIPMENT.DB
EquipID Number*
EquipType
EquipDescrip
RoomID Number, RefInt
etc

--
Michael

Remove NOSPAM to reply

[Bob Albrecht]

Couple of questions:
1)How are floors being identified? Composite key: BuildingId, FloorID?
Seems to me it would have to be this way, since each building would have
floor 1, floor 2, etc.
2)How are rooms identified? 101, 102.... 201, 202....? If this is how
you're numbering, then Rooms.db would also need a composite key:
BuildingID, RoomNumber. (Kinda makes Floors.db irrelevant). If not, then
key would look something like:BuildingId,FloorID,RoomID.
3)Does each and every piece of equipment have a unique ID? If so, then
this would be it's key. The table would also contain the
Building/Floor/Room to which it's currently assigned.

Now, when you'd like to move stuff, seems to me you can only move
equipment, not rooms, floors, or buildings. So cascading updates
wouldn't apply. I'd recommend using either a ChangeTo query or a TCursor
in a scan loop to change the data in Equipment.db, which is the only
place data needs to be changed.

Bob

[mi...@oltronics.com]

On Sat, 04 Apr 1998 21:59:05 GMT, mi...@oltronics.com wrote:

Thanks for all the responses!! I at least know now that i'm on the
right track with this data model.

One other question: What would be the pros and cons of just using
timestamp fields to link all these tables? Every table would have 2
timestamp fields -- a primary key and a secondary index for the
one-to-many links. It seems by doing this, ref. integrity wouldn't
be an issue because the key values would never change. It also seems
like it would be easier to restructure the tables if and when it's
necessary (not to mention the overhead of maintaining many composite
indexes!) Would any of you use this type of indexing and linking?

Thanks!
Mike