[quote=“SpacePhoenix, post:20, topic:197440, full:true”]@r937 from an indexing point of view, do searches based on numeric or text (string) keys search faster, which indexes better?
[/quote]a false dichotomy
look up clustering index – the index which determines the placement of new rows on disk
if you want all territories for a given consultant, it would be nice if all those rows were physically close together, so that they can all be read with one physical read operation
alternatively, if you wanted all consultants in a given territory, it would be nice if all those rows were physically close together, so that they can all be read with one physical read operation
with an auto_increment PK, rows are stored in creation sequence, which screws up both of the above objectives
and let’s not forget another factor in the false dichotomy – having retrieved the junction rows, inefficiently or not, now you have the issue of whether there’s still another table you have to join to
Don’t mix business data with the technology requirement of uniquely identifying a row. That is, don’t use natural keys. It’s much easier to adapt to unpredictable changes when the data that’s changing hasn’t been used as primary and foreign keys throughout your database.
It sounds like you want to avoid the join for performance reasons. Obligatory root of all evil quote:
Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.
So, program first and foremost for correctness, robustness, clarity, and maintainability. Then profile your application to find the bottlenecks – to find the 3% of your program that is genuinely performance critical – and optimize only in those places.