This question is focused on the relationships between 5 maybe 6 tables.
Consider the following list of tables
tblLocation
tblArea
tblRow
tblPlot
tblEvent
The tables have the following fields:
tblLocation
LocationID
LocationName
tblArea
AreaID
LocationID
AreaName
tblRow
RowID
AreaID
RowName
tblPlot
PlotID
RowID
PlotName
tblEvent
EventID
PlotID
Event
EventDate
You can see an image of those table and their relationships at the followings url
http://www.deanathor.com/relationships.jpg
The logic behind this structure is that each location has areas and each area has rows and each row has plots.
The purpose is to track events that occur in these places. It is important that an event might effect the entire location...but it also might only effect a single plot. Or maybe it effects 10 rows in one area and 3 in another...etc.
For reporting should an event occurs that effects the entire location I would need to be able to pull a report on a single plot and know that that event did in fact effect that plot.
So there is a logic to the structure...the problem I run into is that in a particularly large location their might be as many as 60000 plots.
Currently, if I chose to have an event occur that effected the entire location I would have 60000 new records entered into the event table. That would quickly grow the database to MAMOUTH proportions.
I need a way to relate everything so that I can still be specific (should I so choose) but I can also be general (should I so choose)
One way I was thinking of was to creat a join table (call it tblJoinLocationEvent) and have that table structured thus:
tblJoinJocationEvent
EventID
LocationID
AreaID
RowID
PlotID
Add a record for NA (which can easily be filtered out for reports) have the forms enter in the NA value for those records that dont require an entry.
For example
If an event (id 1) occured in rows 2 and 5 (all associated plots)
of area 1 then the record would have
EventID 1
LocationID NA
AreaID 1
RowID 2
PlotID NA
EventID 1
LocationID NA
AreaID 1
RowID 2
PlotID NA
This keeps the new records down to 2 instead of the 400 records that would otherwise be added by listing an event occurance for each plot in each of the rows. It should in theory allow me to run a report that would actually tell that plot 85 in row 2 of area 1 had that event occur to it by virtue of the plots relationship to the row and area as shown in the relationships graph linked above...
Is my thinking correct on this or am I totally WAAAYYYY off. Any advice or suggestions would be greatly apprecieted.
Thanks
Consider the following list of tables
tblLocation
tblArea
tblRow
tblPlot
tblEvent
The tables have the following fields:
tblLocation
LocationID
LocationName
tblArea
AreaID
LocationID
AreaName
tblRow
RowID
AreaID
RowName
tblPlot
PlotID
RowID
PlotName
tblEvent
EventID
PlotID
Event
EventDate
You can see an image of those table and their relationships at the followings url
http://www.deanathor.com/relationships.jpg
The logic behind this structure is that each location has areas and each area has rows and each row has plots.
The purpose is to track events that occur in these places. It is important that an event might effect the entire location...but it also might only effect a single plot. Or maybe it effects 10 rows in one area and 3 in another...etc.
For reporting should an event occurs that effects the entire location I would need to be able to pull a report on a single plot and know that that event did in fact effect that plot.
So there is a logic to the structure...the problem I run into is that in a particularly large location their might be as many as 60000 plots.
Currently, if I chose to have an event occur that effected the entire location I would have 60000 new records entered into the event table. That would quickly grow the database to MAMOUTH proportions.
I need a way to relate everything so that I can still be specific (should I so choose) but I can also be general (should I so choose)
One way I was thinking of was to creat a join table (call it tblJoinLocationEvent) and have that table structured thus:
tblJoinJocationEvent
EventID
LocationID
AreaID
RowID
PlotID
Add a record for NA (which can easily be filtered out for reports) have the forms enter in the NA value for those records that dont require an entry.
For example
If an event (id 1) occured in rows 2 and 5 (all associated plots)
of area 1 then the record would have
EventID 1
LocationID NA
AreaID 1
RowID 2
PlotID NA
EventID 1
LocationID NA
AreaID 1
RowID 2
PlotID NA
This keeps the new records down to 2 instead of the 400 records that would otherwise be added by listing an event occurance for each plot in each of the rows. It should in theory allow me to run a report that would actually tell that plot 85 in row 2 of area 1 had that event occur to it by virtue of the plots relationship to the row and area as shown in the relationships graph linked above...
Is my thinking correct on this or am I totally WAAAYYYY off. Any advice or suggestions would be greatly apprecieted.
Thanks