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

 

[Norie]

I always think that the best solution for primary keys is to use an arbitrary unique number . i.e. one that is not based on the data

If you base a primary key on data your are liable to end up with duplicates.

It is also a performance factor - trying to match a number against another number is far more efficient than matching text or dates or something.

 

[Anthony G.]

But what if an arbitrary number cannot be generated?

 

[Norie]

Surely just use something like Autonumber or it's equivalent.

Is something like that available?

 

[Anthony G.]

Unfortunately not. A rep that covers a territory in a particualr state can have multiple territory names and no unique identifier associated with that territory; therefore, tracking data from multiple locations seems impossible....agree?

 

[Norie]

Could you explain a bit more?

Can/Do the individual reps have a unique ID?

Can/Do each territory have a unique ID?

 

[Anthony G.]

Our old system allowed for the rep/territory to have a unique ID....for example: John Smith in Texas who covered Plano and Garland counties had a unique ID of 100. Therefore, any activity that occurred in those counties where credited to ID 100 - who happened to be John Smith - if John leaves and Jane comes in...ID 100 never changes.

The new system is going to set up the territoy by name, thereby placing John Smith somewhere in the string of text for the definition, but now here comes the complicated part....John might share Garland County with another rep, therefore John is going to have two Territory names: one to distinguish those counties he covers and another to capture only those zip codes for his counties AND the new system does not have the capability to generate a unique number for the area(s) that John covers.

Make sense??

Anthony

 

[Norie]

What are you using?

 

[Anthony G.]

The new system is an Oracle based Web Application.

 

[Aladin Akyurek]

REPS(Rep#,RepName,RepMobile,...)

TERRITORIES(Ter#,...)

REP-TERRITORIES(Rep#,Ter#,...)