Ok, so I am working on a database for my company. Beginning next week and over the next several years, we will be inspecting approximately 35 different intersections in OKC. Each intersection can have 3 or 4 corners (some of the intersections are T intersections). Each corner will be inspected and a rating given to each component. Then an overall rating given to the entire intersection.
The inspectors will be taking multiple photos to put with their inspections. The city then wants to be able to view (reports) the inspection data by intersection. So I began designing the attached database about 2 weeks ago. Now that it's time to start putting together the reports (which is something I always seem to do last), I've come upon some issues that I had overlooked during the table design time.
The relationships in the database are as follows:
tblStates.StateID (PK) - one2many - tblCities.State (FK)
tblCities.CityID (PK) - one2many - tblStreets.CityID (FK)
tblCities.CityID (PK) - one2many - tblIntersections.IntersectionCity (FK)
tblStreets.StreetID (PK) - many2many - tblIntersections.IntersectionNS (FK)
tblStreets.StreetID (PK) - many2many - tblIntersections.IntersectionEW (FK)
tblInspectionImages.InspectionID (FK) - many2one - tblInspections.InspectionID (PK)
tblIntersections.IntersectionID (PK) - one2many - tblInspections.IntersectionID (FK)
tblInspectors.InspectorID (PK) - one2many - tblInspections.InspectorID (FK)
tblCorners.CornerID (PK) - one2many - tblInspections.CornerID (FK)
tblCorners is a "static" data table that will never change. It is literally only there for the sake of lookup and I'm now thinking is kind of dumb. Literally 4 entries "NE", "NW", "SE", "SW".
Also, each inspection is going to end up being 4 (or maybe 3 if it's a T intersection) records one record for each corner. All with the same InspectionNumber, InspectorID, InspectionDateTime, and IntersectionID.
I'm now thinking that is flawed. I maybe should have created two tables, one as tblIntersectionInspections and one as tblCornerInspections? With a relationship like: tblIntersectionInspections.InspectionID (PK) - one2many - tblCornerInspections.InspectionID (FK).
Is there anyone who can take a look at what I've done and let me know what they think about this? And maybe point me in the right direction?
It just keeps saying upload failed when I try and upload a .zip of the project.
This is the .zip I uploaded to OneDrive and enabled sharing via link.
https://1drv.ms/u/s!AtD5mbQ07lu7geBcYoq8UpHItZPV9w
The .PDF is what these Inspector's will be filling out and turning in.
NOTE: The .PDF is what these Inspector's will be filling out. (The 2 Page Intersection Inspection Report)
NOTE: When you try and open frm_InspectionInformation you will get a pop asking you to re-link the images. The images are stored in the data->Images folder.