CODEWITHSUNDEEP
.netdatabasedatabase-design
Muhammad Maqsoodur Rehman
Muhammad Maqsoodur Rehman

Reputation: 34277

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 like to have a separate field for each table named CODE. This field will store the system-generated code for each table. But this system-generated-code has to be customizable by the end user. The end user must have the option to make up his/her own code as per his/her discretion.

How can i make this possible? Any clue?

Upvotes: 2

Views: 50

Answers (1)

Thomas W
Thomas W

Reputation: 14164

If you want a user-assignable CODE identifier, make it additional & separate to your ID primary-key field.

This gives you a reliable "internal identifier" on which to build the application, while preserving the ability for the business to assign some "business identifier".

Joining an integer ID "internal identifier" is also more efficient than a VARCHAR, which is what you'd need to give flexibility for the "business identifier".

create table CUSTOMER (
    ID          integer not null,
    CODE        varchar(32),
    -- other fields
    primary key (ID)
);

To be honest, you probably can't decide for the business how they wish to allocate CODEs -- so trying to use CODE as a primary key would render it impossible for your application to add rows to the table, because the rules to allocate these are too complex/ unknown/ not delegatable to code in the application.

Really, CODEs should be for the business to decide -- not for you to try & second-guess, which would only ever result in your being wrong. PROD001, PROD002 sounds fine.. but then they'll decide to change the system for some different category of product. You will never be able to correctly guess PAPERPROD0084/12/a, which the business will decide is what's correct, and so you will inevitable end up being wrong.

Also, this lets you build a real application while the business scratches it's head & wastes time figuring out how the "business ID" system is going to work -- or changes it's mind.

One final tip: allow CODE to be null, so people can fill it in later. Better to have it blank than incorrect.

Upvotes: 1

Related Questions