I am in need of some help laying out my database.
I have the following tables that I need to relate to each other but I can't think of the correct way to do it
1. Grocery Chain Headquarters - contains information on address, contact information etc. (PK - ChainID)
2. Store Locations - contains information on each Store in the Chain, address, contact etc. (PK - StoreID)
3. Incedent - contains information about an incedent that could effect 1 or more Stores ( power outage, tornado, equipment failure etc ).(PK - IncedentID)
4. Items - Items within the store that have to be written off or marked down due to the incedent.(PK - UPC)
1st Level is the Headquarters Table
4thLevel is the Items Table
Now the problem is the 2nd and 3rd level
The Headquarters has many Locations just as a Location may have many Incedents
The Headquarter also has many Incedents and those Incedents may have many Locations
If I have the Location as the 2nd Level, then I have to have mutiple Incedent records if the Incedent effects more than one Location and in reverse
If I have the Incedent as the 2nd Level, then I have to have mutiple Location records - 1 for each Incedent.
I sure hope someone can fix my brain fart, because I am currentlly stuck.