I'm in the process of converting a very large flat file MySQL database into one that is relational in nature to ease edits and having to hard code ever changing lookup values in the front end scripts.
The application could not be simpler and I've done this numerous times, but for some reason I'm chasing my tail ...
So here is an example reduced to the relevant parts:
- Lead tracking application
- 3 tables: Territory, SalesRep, Leads
- A SalesRep can be associated with many territories and many leads
One could have tables such as:
TerritoryID
TerritoryLocation
SalesRepID
SalesRepID
SalesRepCompany
LeadID
LeadCompany
TerritoryID **
- On the territory table, I've joined the master SalesRep table SalesRepID to the detail Territory table SalesRepID. The SalesRep list has a "territory drill down" showing all locations associated with that Rep. Perfect.
- However, rather than storing the SalesRepID in the Lead table (which I know would make things simpler), I'm storing the TerritoryID. I've done this as the SalesReps are constantly changing (while the territory associated with a lead stays constant). When a change to the SalesRep is needed, my thought is that one change is made in the Territory table instead of having to search/replace the ID values in the Lead table.
So the issue is figuring out how to define the master/detail relationship between the Lead table and SalesRep table. I'd like to create a similar scenario to #1 where, a "lead drill down" is available from the SalesRep list. If I join the master SalesRep table SalesRepID to the detail Leads table TerritoryID the drill down works, but does only returns the first lead (i.e. not all records) for the sales rep.
Its time to ask for help ... I've been looking at this for too long and I'm sure the answer is right in front of me!
Thanks -