Should I use lookup field ?

[John]

A question for "best practice" in database design

I use two tables for Jobcard info in my database. {tblJobCard} and
{tblJobDetails}.
tblJobDetails is used to store product info used on each job and uses a
field [fkTaxRateID ]to lookup one of two tax rates in {tblTaxRates}
applicable for the products used.

tblJobCard stores the labour cost for the job, which is taxed at the low
rate. This rate has remained unchanged for several years, but has changed in
the past and may change sometime in the future.

My question is, in order to allow for future changes should I insert a
field in the JobCard table to lookup the tax rate in tblTaxRate, even though
it is always the same rate.
Or is it ok to use the current rate for calculations and just adjust the
fields if and when necessary.

Thanks for any ideas on this one.

John

 

[MacDermott]

If there's any chance of it changing, you should not rely on a lookup.
Even though it denormalizes the data, include a field in tblJobDetails for
TaxRate - not TaxRateID.
(although you may want to use a separate field to tell you the type of tax
rate used.

The reason:
If the tax rate changes, you'll want your historical data to retain the old
rates, while new data will use the new ones. You can't do that with a
lookup.

Also:
If you do decide to go with a lookup, put it on your form, not in the
table itself.
Lookups in tables seem attractive to newcomers, but have caused so much
trouble that few experienced professionals use them.

HTH
- Turtle

 

[John Vinson]

A question for "best practice" in database design

I use two tables for Jobcard info in my database. {tblJobCard} and
{tblJobDetails}.
tblJobDetails is used to store product info used on each job and uses a
field [fkTaxRateID ]to lookup one of two tax rates in {tblTaxRates}
applicable for the products used.

tblJobCard stores the labour cost for the job, which is taxed at the low
rate. This rate has remained unchanged for several years, but has changed in
the past and may change sometime in the future.

My question is, in order to allow for future changes should I insert a
field in the JobCard table to lookup the tax rate in tblTaxRate, even though
it is always the same rate.
Or is it ok to use the current rate for calculations and just adjust the
fields if and when necessary.

You should probably do BOTH: have a tax rate in the JobCard table to
record the tax rate in effect at the time the job was actually done,
and have the current (editable as laws change) tax rate in
tblTaxRates. If you don't do this, then it becomes impossible to
determine what was actually done in the past, since the current tax
rate will be used regardless of what tax was applicable then.

You can "push" the value from the tax rate table into tblJobCards in
the BeforeInsert event of your form (and yes, you should/must use a
form not a table datasheet for this purpose).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John]

Thanks guys

Apologies, maybe I didn't make myself clear. I wasn't talking about the
inbuilt lookup tables with Access.
I have a historical record of the tax applicable to each job.

tblTaxRates has three fields

pkTaxRateID
TaxRate
TaxRateDate.

There are currently three records 13.5%, 21% and 0%,
pkTaxRateID 1, 2, and 3 respectively. If the tax changes, I just enter
pkTaxRateID 4 as the new tax rate and implementation date.

The fkTaxRateID field in each JobDetails record points to the tax rate
applicable for that record.
Is this an efficient way to do this. If I use this method for the JobCard
record, every fkTaxRate field will contain pkTaxRateID 1, as all jobs are
taxed at this rate.

What is the usual method to store data like this.

Thanks
John

A question for "best practice" in database design

I use two tables for Jobcard info in my database. {tblJobCard} and
{tblJobDetails}.
tblJobDetails is used to store product info used on each job and uses a
field [fkTaxRateID ]to lookup one of two tax rates in {tblTaxRates}
applicable for the products used.

tblJobCard stores the labour cost for the job, which is taxed at the low
rate. This rate has remained unchanged for several years, but has changed in
the past and may change sometime in the future.

My question is, in order to allow for future changes should I insert a
field in the JobCard table to lookup the tax rate in tblTaxRate, even though
it is always the same rate.
Or is it ok to use the current rate for calculations and just adjust the
fields if and when necessary.

You should probably do BOTH: have a tax rate in the JobCard table to
record the tax rate in effect at the time the job was actually done,
and have the current (editable as laws change) tax rate in
tblTaxRates. If you don't do this, then it becomes impossible to
determine what was actually done in the past, since the current tax
rate will be used regardless of what tax was applicable then.

You can "push" the value from the tax rate table into tblJobCards in
the BeforeInsert event of your form (and yes, you should/must use a
form not a table datasheet for this purpose).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Apologies, maybe I didn't make myself clear. I wasn't talking about the
inbuilt lookup tables with Access.
I have a historical record of the tax applicable to each job.

tblTaxRates has three fields

pkTaxRateID
TaxRate
TaxRateDate.

There are currently three records 13.5%, 21% and 0%,
pkTaxRateID 1, 2, and 3 respectively. If the tax changes, I just enter
pkTaxRateID 4 as the new tax rate and implementation date.

The fkTaxRateID field in each JobDetails record points to the tax rate
applicable for that record.
Is this an efficient way to do this.

I'd suggest, rather than joining on the ID, that you join on a Non-Equi Join
linking to the effective date.

John W. Vinson [MVP]

 

[Guest]

Sorry about the previous message - I couldn't read your reply on my usual
newsreader, so I came to the Webpage; I'll repost it now so I can reply
tomorrow on the newsgroup. My "non equi join" solution is clearly unclear and
a partial answer at best!

John W. Vinson [MVP]

Thanks guys

Apologies, maybe I didn't make myself clear. I wasn't talking about the
inbuilt lookup tables with Access.
I have a historical record of the tax applicable to each job.

tblTaxRates has three fields

pkTaxRateID
TaxRate
TaxRateDate.

There are currently three records 13.5%, 21% and 0%,
pkTaxRateID 1, 2, and 3 respectively. If the tax changes, I just enter
pkTaxRateID 4 as the new tax rate and implementation date.

The fkTaxRateID field in each JobDetails record points to the tax rate
applicable for that record.
Is this an efficient way to do this. If I use this method for the JobCard
record, every fkTaxRate field will contain pkTaxRateID 1, as all jobs are
taxed at this rate.

What is the usual method to store data like this.

Thanks
John

A question for "best practice" in database design

I use two tables for Jobcard info in my database. {tblJobCard} and
{tblJobDetails}.
tblJobDetails is used to store product info used on each job and uses a
field [fkTaxRateID ]to lookup one of two tax rates in {tblTaxRates}
applicable for the products used.

tblJobCard stores the labour cost for the job, which is taxed at the low
rate. This rate has remained unchanged for several years, but has changed in
the past and may change sometime in the future.

My question is, in order to allow for future changes should I insert a
field in the JobCard table to lookup the tax rate in tblTaxRate, even though
it is always the same rate.
Or is it ok to use the current rate for calculations and just adjust the
fields if and when necessary.

You should probably do BOTH: have a tax rate in the JobCard table to
record the tax rate in effect at the time the job was actually done,
and have the current (editable as laws change) tax rate in
tblTaxRates. If you don't do this, then it becomes impossible to
determine what was actually done in the past, since the current tax
rate will be used regardless of what tax was applicable then.

You can "push" the value from the tax rate table into tblJobCards in
the BeforeInsert event of your form (and yes, you should/must use a
form not a table datasheet for this purpose).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps