Should I split table

[Gwen]

I have what I thought was a straightforward Plan table.

Planid
Planname
Distribution type
Cost center code
Plan year
Vesting rule
Vesting date

There is a vesting rule field that can change for each
plan year sometimes during the year
Each plan record has to be maintained with different
years because indiv payments are calculated based on the
vesting rule and vesting effective date.
I am thinking it would be good table design to have this
info in a separate table.


planid
year
vesting rule
vesting date


1 1999 25% jun 1999
1 2000 15 jan 2000
1 1999 10% dec 1999

Hopefully, this is not confusing. I pray that one day, I
will be good at this.

Please advise.

Kindest regards,

Gwen

 

[Tim Ferguson]

There is a vesting rule field that can change for each
plan year sometimes during the year

Okay, so you have many VestingRules for each Plan - a classic case of
one-to-many...

Each plan record has to be maintained with different
years because indiv payments are calculated based on the
vesting rule and vesting effective date.
I am thinking it would be good table design to have this
info in a separate table.

Exactly so

planid
year
vesting rule
vesting date

Yesbut... if there is more than one VestingRule per year, as you seem to
indicate above, then using Year will not be enough to identify a single
record. I think I'd drop the VestingRules.Year field, and use the
DateActive field as the identifier

1) Using words like Year and Date as field names is bad because they are
reserved words in SQL and VBA and can cause some hard-to-find bugs later
one. Use descriptive things like StartingYear or DateActive instead.

2) The VestingRule table will need to have its PK set to the combination
of (PlanID, DateActive) -- do you need advice on how to set that up?

3) Remember to remove the VestingRule and VestingDate fields from the
Plans table -- you should just be looking up the most recent record in
the VestingRules table to get the current rule, probably by using a Query
joining the two tables.

Hope that helps


Tim F