Table Design - Handling field with a mix of null, fixed and varaiablevalues

[samah]

In my existing employees table, I need to add field to store data about
the Housing Allowance employees are receiving from the Company.

Not all the employees are entitled for this particular type of allowance
and of the entitled, some are getting a fixed amount while the rest are
paid a fixed percentage of their current monthly salary.

So in the empHouAllowance field, I have to accommodate nulls, a fixed
value (remain fixed for one whole year - MAY be reviewed at end of every
year) and a variable value based on another field (empBasicSalary) in
the same table. This value is variable because most of the employees
salaries revised yearly based on the date they were hired. So this is a
calculated value.How do I handle this situation?

Thanks in advance.

-samah

 

[Jeff Boyce]

If your underlying data is either a fixed amount (or nothing), or a
percentage (or nothing), it sounds like you have two fields, not one. A
standard premise of normalization is to use only one fact per field, so two
facts ("FixedAmount"; "Percentage") would require two fields. You could use
the fact that there are no values in either field to "calculate" that there
is no allowance.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[David F Cox]

You appear to be confusing yourself. Just about every field in a table holds
a variable value. Wheter it gets varied is down to the designers and the
users.
If the field is not relevant to a group of employess then NULL is a good
choice to represent that.
Otherwise you just put the value that you believe is right into that field.
It does not matter if that value is a result of an infrequent and
established calculation. Hours worked last year is an acceptable calculated
field. Hours worked so far today is a calculation that should be done with
a query.

Simply a field called HousingAllowance, or your choice of name, will do what
you want. You might want a another field to show HousingAllowanceCategory,
or similar, so that you can tell what that field represents.

 

[samah]

Thank you for both Jeff and David.

I am thinking of adding a boolean field 'entitledForHA' to my employees
table, creating a sub table 'empFixedHA' with just 2 fields empID and
FixedHA (only a few employees are falling under this category) and then
through a query, pull the employees who are entitled for HA from the
employees table, remove the ones who are already existing in the
'empFixedHA' table and to the results adding a field with a calculated
varaiable HA. ( I have to learn how to translate this into SQL still).

Will it be an acceptable solution? If it is agreeable, can be you help me
with the SQL part? I am still at the table design stage but it will be
helpful when I move onto the queries.

Thank you in advance.

-samah

 

[Jeff Boyce]

On first thought, that sounds workable. When you get ready to work on the
queries/SQL, consider posting to the queries newsgroup.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[samah]

Thank you Jeff. I will be back when I am ready.

-samah

 

[David F Cox]

Knowing the way databases go, two choices expands to three, to 4 ...
I would start with a lookup field even though it is overkill at the moment.
It could save you work later on.

 

[samah]

Thank you, David.

-samah.

Knowing the way databases go, two choices expands to three, to 4 ...
I would start with a lookup field even though it is overkill at the
moment. It could save you work later on.

 

[samah]

David,

Can you briefly explain to me how can I do it with a lookup table?

Thanks again.

-samah.

 

[David F Cox]

Not that briefly, and not tonight. Later. Sorry.

David,

Can you briefly explain to me how can I do it with a lookup table?

Thanks again.

-samah.