Simulate relationships - Updating and deleting

[scousethemoose]

Hi hope someone can help me with this:

I have 3 tables, 2 of which are basically the same.

tblAppliancePAT:
PATno - Primary Key
AppDescription - Key

tblReqPAT:
PATno - Primary Key
AssetID - Key

I'm trying to link these table to another table PATesting: It has these fields

TestNo - Primary Key
PATno
Description
TestDate
RecheckDate
TestedBy

I can have intermediate links but i want to force referential integrity so that when i delete a record form my assets table it deletles the related record in tblPATesting.

I want to have two one tables for a many table. Is this possible?

I also have the problem that if i schedule a PAT for reocords in either table i cant unless a related record is is in the other.

I've a feeling i could combine the two tables, the easy solution but i'd like to keep them seperate.

Any ideas?

Thanks

 

[Darrylles]

Hiya,

Why 'simulate' the relationships? Why not produce 'actual' relationships?

If you simulate them, then you'll have to manually handle ref. integrity in code.

Why not put App_PAT_fk and Req_PAT_fk fields in the PATesting table and link them to the PK's of the other 2 - enforcing referential integrity at the same time?

You've probably got a reason, but you haven't mentioned it.

Regards,

Darrylle

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[scousethemoose]

Hi thanks for yor respone.

Maybe i can be a bit more clearer. If i update/delete a record in the reqReqPAT table i want all related record to be updated/delete. I can get this to work if i just link the reqPAT table to PATesting because I'm able to enforce referential entegrity. My problems arise when i have two tables linked to the the many end table PATesting.

An asset can have a unique patno and this can have many pat tests

An appliance can have a unique patno and can have many pat tests.

I want the tests for both assets and appliance to be in the same table and allow referential integrity to exist

I coded so that the two tables cannot contains the same patno.

Any ideas now? thanks again

 

[DonQuichote]

> I also have the problem that if i schedule a PAT
> for reocords in either table i cant unless a
> related record is is in the other.

It may not be a very good choice to use PATno as a primary key here: a primary key is a value that identifies the record. This means that a primary key value must never change. A better solution would be:

tblPAT
  PATID (autonumber and primary key)
  PATno (the label)
  (... any other data)

And point to a record in tblPAT instead of giving the PATno. This allows you to create a record which can be filled later. In general, one should always use meaningless primary keys (meaning that the key value says nothing about the contents of the record it identifies).

If you want the split-table situation to remain, you could alter the above table:

tblAppliance:
ApplianceId (autonumber and primary key)
AppDescription - Key

tblReqPAT:
ReqId (autonumber and primary key)
AssetID - Key

tblPAT:
  PATID (autonumber and primary key)
  PATno (the label)
  ApplianceId
  ReqId
  (... any other data)

Now, either ApplianceId or ReqId is empty in tblPAT (do not forget to remove the default value of 0 for these fields!)

Hope this helps or amuses

 

[scousethemoose]

Thanks for the input but I got round my problem of the many to many relationship.

What i did was inorder to simulate referential integrity and allow cascade update and delete i modified my code to run a query based upon the old value of the PATno in tblPATesting.

I use the same form for modifying patno and had been using a dlookup on the form load to detrmine which record set to open. This gave me an idea, i already use a dlookup to check if a new patno entered exists in either table for data validation.

If one exists i set a boolean to true indicating an error occured and so dont run the update query. If the modified patno doesnt exist in either table then the boolean is set to false so run the query.

I then use the old value of the patno and find matching records in tblPATesting and update these to the updated patno.

It works great, it came to me while i was driving to work this morning.

 

[Darrylles]

Scouse,

Glad to hear you worked round the natural, built-in relational database feature which is quite simple to use (albeit in a more complicated fashion).

Regards, (confused)

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."