Referential Integrity Problem

[cordevil95]

I have two tables: PLANS and LOTS. Here are some of the fields:

PLANS
Model
Subdivision
SquareFeet
LinearFeet

LOTS
Subdivision
Lot
Block
Model

In the table PLANS "Model" and "Subdivision" are a combined primary key (since the same model can be used in multiple subdivisions but I don't want the same model twice in one subdivision). In LOTS the primary keys are "Subdivision", "Lot", and "Block"

I want to find a way to have the "Model" in the LOTS table change it's name if it is changed in the PLANS table. I can't use Referential Integrity because "Model" in PLANS is not a unique index.

Any thoughts?

 

[giacomo]

you should switch to a model_id, sub_id, lot_id etc. and then have a table with the names for each item so that when a name changes you only have to change it once.

hth,
Giacomo

 

[cordevil95]

I don't see how that would solve my problem. Sure, I could make a ModelID the primary key (unigue index) in my PLANS table and I could make a foreign key in my LOTS table but it still wouldn't update the "Model" name if it changed in the PLANS table.

 

[giacomo]

you're not following me. I'm not saying to change your primary keys. All I'm saying is that you need a new table in your database that associates a model_id with a Name. Then you can replace the model names in PLANS and LOTS with the model_id. When the name changes you would simply have to change the name in the NAME table.

hth,
Giacomo

 

[Andrew Fergus]

Hi

Isn't this a many-to-many relationship? In other words, one model can be in many subdivisions and one subdivision can have many models. The way to set up a m-2-m relationship is to set up an intermediate table, such that your structure might look like this :

PLANS
Model_ID {primary key}
Model
SquareFeet
LinearFeet

LOTS
Lot_ID {primary key}
Subdivision / Subdivision_ID {foreign key?}
Lot
Block

INT_TABLE
Model_ID {joint primary key}
Subdivision / Subdivision_ID {joint primary key}
Lot_ID

The joint primary key in the INT_TABLE prevents the same model from being in the same subdivision more than once. The link to the Lot_ID stipulates which lot the model is on. Once the models and lots are set up, you assign models to lots via the intermediate table - you could use VBA to auto-populate the subdivision_ID based on the lot_ID, rather than key it (given it alrteady exists in the LOTS table). I haven't actually tested this set-up or given it too much thought - it's just an idea at this point in time.

If you search this forum for many to many relationships you should find a few threads on this subject. I could be wrong but I also suspect your database has not been normalised - check out the link in giacomo's signature for a quick read on the subject of normalisation.

HTH, Andrew