Referential Integrity Problem

cordevil95

New Member
Joined
Apr 27, 2006
Messages
33
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

New Member
Joined
Apr 27, 2006
Messages
33
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,136,705
Messages
5,677,300
Members
419,684
Latest member
BOB101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top