I am developing an application in Access and I am stuck with setting up relations between 3 tables. My question is more concerning database relations, rather than Access oriented.
Consider the following 3 tables:
1. PROJECT: ProjectNo, ClientNo, ProjectDescription
2. PROJECT MAIN AREA: ProjectNo, MainAreaID, MainAreaName
3. PROJECT SUB AREA: ProjectNo, MainAreaID, SubAreaID, SubAreaName.
As one can see from the structure, table 2 contains FK (Foreign Key) "ProjectNo" referenced from PK (Primary Key) in PROJECT table. However, table 3 also contains the same FK, along with the another FK "MainAreaID" reference from PK of table 2. My question is, is this a good solution? Should the FK "ProjectNo" in table 3 be referenced from table 1 or table 2? OR is there a better way to do it? I would appreciate all of your help on this question.
I would suggest to try to adjust the relations on the Entity Level rather then at table level i.e. (Projects, Locations and Clients) and then implement them via tables...
I mean see first see how projects, Clients and locations are inter-related and their type as 1-1, 1-Many or Many-Many and then setup your tables according to that as then it will be easy to see the clear picture.
(you may ask your self questions like
1. Will 1 client have many projects at different locations?
2. at 1 location will there be many projects by different clients?