I have seen this answered in a few ways but not quite the way i am working it so i thought i would ask to see if someone can point me in the right direction or tell me if i am going about it all the wrong way.
So here is my scenario: I have 8 tables but for the purpose of this lets look at the main 3
- Datasets
- Gauges
- Reports
Datasets columns: DatasetID (Primary Key) Name Sources Description
Gauges columns: GaugeID (Primary Key) Name URL_Link Description FK_DatasetID(Foreign Key to Datasets Table)
Reports columns: ReportID (Primary Key) Name Description URL_Link Schedule_Rate FK_Gauges(Foreign Key to gauges Table) FK_Recipients(Foreign Key to Recipients Table)
All Primary keys are unique obviously and are auto increment.
I have populated the Datasets table and they all have their own unique number now but then i went to populate the Gauges table but immediately ran into the problem where gauges are attached to multiple Datasets. This will get worse later as each report has multiple gauges which again will then be attached to multiple datasets via Gauges. I hope that makes sense.
I first went to add multiple foreign keys as the max amount of datasets is about 7 per gauge, but when i thought about how the Reports table will attach onto Gauges later on, i could see this would be a problem as each report could easily contain 25+ gauges and i didnt think 25+ foreign keys would be an efficient way of working this.
Now i am here.... and this is the Database Diagram:
any words of wisdom, links to go read and constructive criticism are all very welcome :)
Many Thanks
Paul
