WORKERS COMP COMMON RATES

[Anne]

I have a table TblBurden, which lists 7 different workers compensation
codes, with their respective rates.

A second table (TblWcAdj) has adjustments which apply to each workers
compensation code.

For example WcCode 5221 rate is 15.05 per 1000, WcCode 5606 rate is 4.57 per
1000, etc.

TblWcAdj has a safety credit, drug free credit, experience modifier, premium
discount, etc. Those rates apply to each WC Code, but are the same no matter
what the workers comp rate is.

The simple answer is to add field names for each Wc Adjustment to the table
burden, but than when the adjustments change, I have to make changes to each
field for each workers comp item. It seems simpler to put the adjustments
into one table and make one change.

Just can't figure out how to link the two tables.
Anne

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps it would be better to set up the tables like this:

A "lookup" table to hold the name values of the different adjustment
codes:

CREATE TABLE WcAdjCodes (
WcAdjCodeID COUNTER NOT NULL ,
WcAdjName TEXT(50) NOT NULL PRIMARY KEY
)

A unique index so we can refer to the column WcAdjCodeID as a Foreign
Key (FK) in other tables:

CREATE UNIQUE INDEX udx_WcAdjCodeID ON WcAdjCodes (WcAdjCodeID)

The table WcAdjCodes would have data like this:

WcAdjCodeID WcAdjName
---------------------------------
1 Safety Credit
2 Drug Free Credit
3 Experience Modifier
4 Premium Discount
.... etc. ...

Now you can add new WcAdjNames as you need, without adding new columns
to tblBurden or tblWcAdj.

The table that would hold the adjustments for each WcCode:

CREATE TABLE tblWcAdj (
WcCode INTEGER NOT NULL REFERENCES tblBurden ,
WcAdjCodeID INTEGER NOT NULL REFERENCES WcADjCodes
)

The table tblWcAdj would have data like this:

WcCode WcAdjCodeID (not in the table, just descriptions)
------------------------
5221 1 -- Safety Credit
5221 2 -- Drug Free Credit
5606 1 -- Safety Credit
5606 3 -- Experience Modifier
... etc. ...

This means you can have as many WcAdjCodes as you want for each WcCode.

Read some articles/books on table normalizations for more information.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe7CH4echKqOuFEgEQKL+wCgyUVTi4bKJP1bgOSfou/u6LiH3dkAoPNV
Z01lVhtl4X81DrxpSwBIME6m
=DMwF
-----END PGP SIGNATURE-----

 

[Anne]

Of course, that is the answer, I just drew a complete blank when I looked at
the problem.
Thank you very much,
Anne