.net - How to make table fields customizable for the end user -

i have 4 tables or entities:

1. customer

2. item

3. contact

4. transaction

i have separate field each table named code. field store system-generated code each table. system-generated-code has customizable end user. end user must have option make his/her own code per his/her discretion.

how can make possible? clue?

if want user-assignable code identifier, make additional & separate id primary-key field.

this gives reliable "internal identifier" on build application, while preserving ability business assign "business identifier".

joining integer id "internal identifier" more efficient varchar, you'd need give flexibility "business identifier".

create table customer (     id          integer not null,     code        varchar(32),     -- other fields     primary key (id) ); 

to honest, can't decide business how wish allocate codes -- trying use code primary key render impossible application add rows table, because rules allocate these complex/ unknown/ not delegatable code in application.

really, codes should business decide -- not try & second-guess, ever result in being wrong. prod001, prod002 sounds fine.. they'll decide change system different category of product. you never able correctly guess paperprod0084/12/a, business decide what's correct, , inevitable end being wrong.

also, lets build real application while business scratches it's head & wastes time figuring out how "business id" system going work -- or changes it's mind.

one final tip: allow code null, people can fill in later. better have blank incorrect.