February 7, 2013 at 8:02 am
Help
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.
Mark Moss 🙂
February 7, 2013 at 8:28 am
This might not be the right answer for you but how about :
Location : 1 table including a field "HeadQuarters" which contains the ID of the location that is the headquarters - ie one record will have a null for headquarter as it is the headquarters.
Incident : Fields include "Location" - that will lead to headquarters & allows multiple incidents per location (hopefully not of course ;-)) or to enable incidents across multiple locations have a many to many table (IncidentLocation) which would just hold incidents and locations.
Item : your item details
IncidentItem : list of items by incident - potentially allows many to many but I would presume you want many items to one incident (& the incident can link to multiple locations).
Does that make sense?
February 7, 2013 at 9:12 am
SCC_Addicted
1. Their can be many HeadQuarters ( Safeway, Albertsons, KingSoopers, WalMart, etc. ) so a null in the HeadQuartersID field of the Location Table won't work.
Mark:-)
February 7, 2013 at 10:53 am
You need to keep this normalized and have a separate table for every relation. You could even create an additional table to control the chain/store incidents if needed.
Here's some sample table and columns -- of course other columns will be needed as well, as might other tables. But this is the base:
Chains
ChainID
Stores
StoreID PK
ChainID
Incidents
IncidentID PK
Description
Start datetime
End datetime
OriginationType 'C'=Chain;'S'=Store
OriginationID ChainID | StoreID
Incident_Chains
ChainID
IncidentID
(IncidentID,ChainID) PK <or> (ChainID,IncidentID)
Start datetime
End datetime
Incident_Stores
IncidentID
StoreID
(IncidentID,StoreID) PK <or> (StoreID,IncidentID)
Start datetime
End datetime
Indicent_Items
IncidentID
StoreID
ItemID
(IncidentID,StoreID,ItemID) PK <or> (StoreID,IndicentID,ItemID)
Start datetime
End datetime
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 7, 2013 at 4:57 pm
This isn't too different than what Scott offered but here's my contribution:
LOCATION
LocationID
LocationType
LocationParent (let's you build a hierarchy)
LocationName
(etc)
LOCATION_TYPE
LocationTypeID
LocationTypeDesc (content examples: HQ, Store, Warehouse, Fulfillment, etc)
CONTACT
ContactID
LocationID
ContactName
ContactAddress
ContactSort
(etc)
CONTACT_LOCATION
ContactID
LocationID
INCIDENT
IncidentID
ContactID
IncidentTitle
IncidentCategory
IncidentDesc
IncidentDate
IncidentPriority
IncidentDisposition
(etc)
INCIDENT_DETAIL (for adding multiple complaintant details for each incident)
IncidentDetailID
IncidentID
ComplaintantName
ComplaintantAddress
ComplaintantStatement
(etc)
INCIDENT_LOCATION
IncidentID
LocationID
February 7, 2013 at 5:31 pm
Yeah, reasonably close.
Probably the major difference is that the OP and myself don't want to "flatten" the Chain and Store into a single "location".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply