Ok here is all the business information I know about this part of the database:
The company will first buy a site (Large piece of land for construction), so they will need to create a site and store its information in tblSite. Once a certain site is created, it gets divided into a number of phases. Each phase is the size of a Street. So Phase is pretty much Street. They give each phase a number to be able to track it.
The phases are then divided into plots, each plot represents a portion of a phase (street) where a house will be constructed. But before starting the construction of houses, the company needs to get permissions from an authority to make roads (RCC)...etc. This authority will then send the permission for some of the work to go ahead. This is defined in the number of phases a certain permission will cover. For example the authority will send one permission document written on it "You now have permission to build Roads from phase number 1 to phase number 20". So, the company will build roads for phase (Street) number 1, 2, 3, 4 ... and 20 then stop and wait until the next permission is received.
I hope this explains the business logic thoroughly.
Design:
Initially, I had two designs in mind:
First:
tblSite
SiteID
tblPhase (linked to tblSite through SiteID, tblPhase is the many side)
PhaseID
SiteID
tblRCC (linked to tblPhase through PhaseID, tblRCC is the many side)
PhaseID
PhaseStart
PhaseEnd
Now, the way i thought of making the forms is as follows:
I created a master form that will show Site name and phase number at the top left corner. This information is what the customer needs to identify what Site/Phase they are dealing with. Then, there are subforms for Site, Phase, RCC, Roads...etc. Each one of them is in a tab for visual aid.
The user has to browse to the right Site and Phase, then just click the tab that applies to whatever they want to view, update.
Supposedly, the user created a site called 'Site1' (added one record in tblSite)and created three phases for 'Site1' - 'phase1', ' phase2', 'phase3' (added three records in tblPhase that have Site1 PK written in the FK fields of tblPhase). Then the user had RCC permission for Site1 starting at phase1 and finishing at phase2 (made one record in tblRCC and in the phaseStart/PhaseEnd fields the user wrote 1 in phaseStart and 2 in phaseEnd)
The problem with this is that the RCC will only appear for one phase when a query is run even though it cover phase1 and phase2.
Second:
Is the current design -
tblSite
SiteID
tblPhase (linked to tblSite through SiteID, tblPhase is the many side)
PhaseID
SiteID
PhaseID
tblRCC (linked to tblPhase through PhaseID, tblRCC is the ONE side and Phase is the MANY side)
PhaseStart
PhaseEnd
Now you are saying this is a problem. What did you mean by
you have created a "black hole" of relationships. Everything "sinks" into the phases table and can't get out.
Is it impossible because tblPhase will be queried by three different tables at the same time? I don't understand what's exactly wrong with it - is one to many/many to one not possible in Access where the junction table has more fields than just FK of the tables that it joins? Can you please be more specific in describing what's wrong and why it's wrong/impossible?