Hi all, I've been working on a database for an ag consulting firm and have been asked to revise my database design due to a misunderstanding on my part. I would appreciate opinions on how I should proceed given the following scenario:
The database has (at the moment) three (3) related tables: Livestock Sites ('Sites'), Livestock Operations ('Operations'), and Manure Storage Sites ('Storage'). I initially thought the relationship was from Sites-->Operations-->Storage, where each subsequent table would be nested within the higher-order category (i.e., table). However, it turns out that for a given Site, there can be multiple Storage sites which accommodate the excrement from potentially many Operations, with each Operation potentially associated with more than one Storage. For example, on Site 1, there may be Storages A and B, and Operation I filling Storage A, Operation II filling Storage B, and Operation III filling Storages A and B. Ultimately, data need to be summarized according to Storages for a given Site, so in my mind, it makes sense to organize the tables: Sites-->Storage-->Operations. Does that make sense? Are there any problems that can be foreseen from this scenario?
Thanks as always for the input.