Help with relationships

  • Thread starter Thread starter C Tate
  • Start date Start date
C

C Tate

I am a bit confused by my relationships and keys. Perhaps someone could help!

My database contains information about sites. The sites will contain houses.
I need to record information on the site, including who will develop it and
the name of the landowner.

Each site will probably just have one developer and one landowner. I want to
record name, address and contact for both.

I suppose it is possible that each developer could be responsible for
several sites (but most likely just one). I also suspect the same landowner
may own several sites, though not necessarily. Does this mean the site should
appear on the many side and developer and landowner on the one side? It is
feasible that a site could have more than one developer, though it is not
likely there will be more than one landowner.

I put the developerID in the site table as a foreign key. Do I also need to
put the site id in the developer table? I did the same thing for the
landowner (ie, put landowner id in the site table as a foreign key). But not
sure if the site id should also go in the landowner table.

It is important for me to get these correct. So far I don’t think they are.
And it is creating problems when I try to make a query for data entry.

Any advice gratefully received.
 
I am a bit confused by my relationships and keys. Perhaps someone could help!

My database contains information about sites. The sites will contain houses.
I need to record information on the site, including who will develop it and
the name of the landowner.
Click to expand...

Well, then you should certainly have *AT LEAST* the following tables: Sites;
Houses; Developers; Owners.
Each site will probably just have one developer and one landowner. I want to
record name, address and contact for both.
Click to expand...

"Probably" is a slippery word. If it is POSSIBLE for a site to have two
developers or two owners, you don't want your application to break when that
situation arises. That's really up to you.
I suppose it is possible that each developer could be responsible for
several sites (but most likely just one). I also suspect the same landowner
may own several sites, though not necessarily. Does this mean the site should
appear on the many side and developer and landowner on the one side? It is
feasible that a site could have more than one developer, though it is not
likely there will be more than one landowner.
Click to expand...

Well... my brother and I jointly own a lot in New Mexico. It happens.
I put the developerID in the site table as a foreign key. Do I also need to
put the site id in the developer table?
Click to expand...

No. The foreign key goes in the "many" side table. Putting the site ID in the
developer table would make it impossible for a developer to work on two sites,
and it's not necessary for the functioning of your application. If it's a one
to many relationship then put the DeveloperID into the Sites table and stop
there.
I did the same thing for the
landowner (ie, put landowner id in the site table as a foreign key). But not
sure if the site id should also go in the landowner table.
Click to expand...

Ditto. The foreign key goes in the many side table: the OwnerID goes in the
Site table but the site ID does NOT go into the Owner table.
It is important for me to get these correct. So far I don’t think they are.
And it is creating problems when I try to make a query for data entry.
Click to expand...

IF you want to allow for many to many relationships - i.e. each site has one
OR MORE developers, and each developer can work on one OR MORE sites, then you
need an additional table, SiteDevelopers let's say. This would have fields for
the SiteID and the DeveloperID; and the Sites table would not have anything
about Developers, and the Developer table would not have anything about Sites.
 
Many thanks. Everything you say seems to make perfect sense. I think my mind
has been going round in circles so long it's in knots!

One more question. Assuming I go with this sitedevelopers table too, does
this also mean I would need a siteowners table?

Your help is so much appreciated. I am quite a beginner with Access.
 
Many thanks. Everything you say seems to make perfect sense. I think my mind
has been going round in circles so long it's in knots!

One more question. Assuming I go with this sitedevelopers table too, does
this also mean I would need a siteowners table?

Your help is so much appreciated. I am quite a beginner with Access.
Click to expand...

The same logic would apply. If you have a many to many relationship from sites
to owners then yes, you need a SiteOwners table with fields SiteID and OwnerID
(and perhaps other fields with information about *this* owner's relationship
to *this* property, e.g. 50% owner, cosigner, whatever).

For some tutorials to get you started up the rather steep and rocky learning
slope to Access check out:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Back
Top