Referential Integrity and Cascade Update

[knowshowrosegrows]

I have a database

tbl_Vendors = Vendor_ID (auto) PK
tbl_Sites = Site_ID (auto) PK, Vendor_ID (number) FK
tbl_SiteVisits = SV_ID (auto) PK, Site_ID (number) FK
tbl_Units = Unit_ID (auto) PK, SV_ID (number) FK
tbl_Bedrooms = Bedroom_ID (auto) PK, Unit_ID (number) FK

I have One to Many with RI and Cascading Update between Vendors to Sites to
SiteVisits. Then I also have One to Many with RI and Cascading Update
between Units and Bedrooms. I cannot get RI between SiteVisits and Units.

Can anyone make a suggestion?

 

[tina]

under your current setup, you can only track who is the current vendor
assigned to visit a site, no historical data - meaning that you can't track
which vendor made a specific site visit in the past. also, putting the site
visit pk into tblUnits means that you can only link each unit to its'
current (or most recent) site visit - again, no historical data about past
visits to each unit.

how about explaining the *real-world process* that you want to keep track
of - in real world terms, NOT "tables, primary keys" etc. once we know more,
probably someone can help you get on the right track.

and btw, when you're using the Autonumber as a table's primary key, you
don't need CascadeUpdates in the link between that pk and its' foreign key
in another table. you *do* want to enforce referential integrity, however.

hth

 

[knowshowrosegrows]

THANKS

Great info about the cascading updates.

I see the way you are imagining my database from my description.

The "Vendors" run many apartment buildings or "Sites." The sites have
apartments or "Units" that house clients that we subsidize while in
treatment. We conduct a "Site Visit" to check the condition of the Units,
how many rooms they are using as "Bedrooms" and how many "beds" or people
they are housing in each Unit.


So there can an infinite # of Sites per Vendor - an infinite # of Site
Visits per Site and at EACH Site Visit there may be determined to be an
infinite # of Units, Bedrooms and Beds for the Site DURING that Site Visit.

In 2007 the Site Visit may determine there are 3 Units at the site with 2
Bedrooms per Unit and 2 Beds per Bedroom. In 2008 the Vendor and Site will
be the same but the # of Units, Bedrooms and Beds may be different.

I had the tables and forms to working together to capture this stuff until I
tried to build a form for adding Vendors and/ or adding Sites to existing
Vendors. Then I realized I needed an autonumber to automatically ID the
Vendors and Sites and my world became sadder and more confusing from there.

 

[tina]

okay, that is much clearer. a couple questions, though:

a site may be run (and visited) by one vendor, but then at some point run
(and visited) by another vendor, correct? do you need to track which vendor
currently runs each site, and ALSO which vendor ran/visited a site during a
specific site visit?

In 2007 the Site Visit may determine there are 3 Units at the site with 2
Bedrooms per Unit and 2 Beds per Bedroom.

what if one of the 3 Units has only one bedroom, or 3 bedrooms? or one
bedroom in a unit has 1 bed, and another bedroom has 2, or 3 or 4? do you
see what i'm getting at: for each site, do you need a just a total count of
units, bedrooms, and beds? or do you need a specific count of beds, in each
bedroom, in each unit, at each site?

it's getting late here, so i won't check this thread again until Monday
evening at the earliest, but i have to tell you that it may be later in the
week before i get back to it. of course, somebody else may jump in with some
suggestions for you, before i do.

hth

 

[knowshowrosegrows]

No, the Site can only be run by one Vendor. I think of the Vendor as simply
the Owner of the two family house that is the Site that has Two apartments
that are the Units. We would think it is clear how many Bedrooms an
apartment has but sometimes, to make more money, the owner will turn the
basement or den or covered porch into a Bedroom and put Beds in it.

 

[tina]

you missed my second question: for each site, do you need a just a total
count of
units, bedrooms, and beds? or do you need a specific count of beds, in each
bedroom, in each unit, at each site?

hth