To hard-code or not for filtering out inactive records

[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

[=?Utf-8?B?SHVzYW0gQWwtQScnYXJhag==?=]

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
"Ronald S. Cook" wrote:

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

[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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Ronald S. Cook" <rc***@westinis.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

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