To hard-code or not for filtering out inactive records

R

Ronald S. Cook

At first, it's easy to say (have a policy) that we'll hard-code for
statuscode='ACT' (or maybe instead statuscode <> 'INA') but I think that
pigeonholes us and would cause limitations.

Example: We want to develop an Employee search screen. Obviously, we don't
want inactive records to be displayed.

The tables look like this:

Employee
--------
EmployeeId
EmployeeLastName
StatusLookupId
TypeLookupId

Lookup
------
LookupId
LookupGroup
LookupCode
LookupValue


Assume the values for EmployeeStatus are: Active, OnLeave, Probation,
Retired, Terminated (i.e. not a straight forward Active or Inactive)
Assume the values for EmployeeType are: Intern, Co-op, Exempt, Non-Exempt

See how things can get tricky quickly if we're not careful? Maybe the
developer of the client would want Active and Probation employee records
returned. Maybe they want all types except for Retired or Terminated? The
latter is especially tricky if we later add a new status (e.g. Deceased).
Dead employees would then be returned (not good).

I'd be interested in the perspective of others.

Thanks,
Ron
 
G

Guest

Ronald,
in my own openion, the best way of having such program running well, is to
normalize your database; i am not a database archetict, but based on my
experience, when i face such problem, i play with the database structure, it
know its not simple.
let us think if your DB structure looks like :

tblEmployee
--------------
EmployeeId
EmployeeLastName
EmployeeStatusId
EmployeTypeId


tblEmployeeStatus
-----------------
EmployeeStatusId
EmployeeStatusDesc


tblEmployeeType
-----------------
EmployeeTypeId
EmployeeTypeDesc


with this, i think life will be much better.

its only a brain storming , you can take it or leave it dude :)


I hope i added a value here.

Thanks

Regards,
Husam Al-A'araj
www.aaraj.net
 
N

Nicholas Paldino [.NET/C# MVP]

Ronald,

In this situation, it would seem like you want some sort of dynamic
query generation. You would use the metadata about the tables/columns to
generate your strings, based on filter criteria (you would have to generate
some sort of general filter mechanism to capture this information). Then,
with the filter information, and the metadata about the data structures, you
can build your command and get your result set.
 
R

Ronald S. Cook

The database is currently normalized. Just because we group our status and
type values into a Lookup table does not make it not normalized.

We originally had a unique Status and Type table for most of our entities.
Since the structures are identical, it is a lot of unneeded complexity when
all can be grouped quite easily.
 
Top