Advice on Primary Keys

[Anthony G.]

Hello to All...

The company I work for is thinking of upgrading to a new web based system that will give us the ability to track all business at virtually every level and thus erplacing our old system(s) Ultimately, revenues are generated by our sales force, which is how the business gets tracked. Sales Reps have their own territories: some single/some multiple and each territory has a unique primary key (rep codes) associated with it; therefore if a rep left the company, the rep code would never change. So when we attempt to gather countless amounts of data from multiple systems, everything is linked to those rep codes.. That’s all about to change, which is why I’m so concerned ….This new system does not have the ability to generate a unique number for a territory, so the plan is to create territory names and include the name of the sales rep and not a unique number – furthermore – if a rep shares a state with someone else, the territory assignment will be based upon the zip codes and/or counties within those territories. I’m not a programmer but I see a serious flaw in this methodology. I’d really like to approach upper management with a logical, technical explanation of why a primary key is vital to any system that links data from multiple sources….that’s if, unless, I’m overlooking something.

Any advice would be greatly appreciated.

Thank you,

Anthony

 

[Anthony G.]

My apologies Aladin, but I'm not quite sure what you're saying.

 

[Aladin Akyurek]

My apologies Aladin, but I'm not quite sure what you're saying.

Those are tables in standard notation. REPS stand for a table housing sales reps. TERRITORIES house the relevant territories. REP-TERRITORIES houses all territories a given sales rep is associated with. They are in 3NF.

 

[Abulafia]

Aladin has given you the structure you require to simulate a many-to-many relationship as one rep can have many regions and one region can have many reps.