I am developing a payroll app. I have an EmployeeMaster table which
contains field EmpID P.K, and EmpCode Unique. At present whatever
details I have, the EmpCode is a long numeric value that the
department is managing internally.

I want to know whether a column like EmpCode must be numeric or
varchar. Since there might be a situation when I need to assign a
temporary code until confirmed code is issued.

Since Payroll Apps. are very common, so it is not my personal
preference. What you all suggest depending upon your experience.

On Aug 15, 12:45 pm, Rohit <rpk.gene... (AT) gmail (DOT) com> wrote:
What does the end user describe the type as?
It seems you already answered this question:
I would use a NUMERIC type. I see no reason for any other datatype for
that coding.

I would just add however that you should be careful in chosing such
attribuites (generic numeric ID) for a Primary key unless there are
some business reasons for it.

Ed

The reason for confusion is that there is no guarantee what happens in
future. I have taken EMP ID as P.K not EMPCODE. EMPCODE is unique.
Just in case I take EmpCode as varchar, what are the implications,
even if it stores numeric value. Since I am using EMPID as P.K.

1) Why do you uppercase data element names as if you are using punch
cards in the 21-st century? Do you really want the extra error rate
that this will cause? Did you read ANY research since 1960 on
uppercase program code?

2) A "_code" data element is not an identifier. A code is an industry
standard for an attribute required and defined by an external
authority -- example: "zip_code" (Authority: USPS). So it cannot be
unique.

3) An "EmployeeMaster" table would be a single row which contains the
data about a slave owner. Read ISO-11179 rules. Shouldn't you have a
"Personnel" table that holds data about the personnel in this data
model? Collective nouns for sets and all those rules for basic data
modeling ...

4) Why don't you know the difference between a field and a column? I
can guess the answer: the same reason that you use the term "master"
in a table name -- you are mimicking a mag tape file system in SQL!!
Master files and transaction files, and all that old 1950's COBOL
stuff.

5) If you want a key, they do not use VARCHAR(n) -- the length is a
constraint and you will want check digits on to of that. Do you know
how to design data encoding schemes?

6) I have a horrible feeling based on your other posting that "EmpID"
is an auto-increment property that will screw up all your data
integrity. That would be a design for disaster.