If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Should I split table
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 |
#2
|
|||
|
|||
"Gwen" wrote in news:283c01c4a73b
: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table design additions after split DB | jk | Database Design | 3 | September 29th, 2004 06:17 PM |
Split a Table in Files | Armando | General Discussion | 1 | July 23rd, 2004 10:49 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
Cannot join 1:M table into M:M tables | Tom | Database Design | 4 | May 19th, 2004 10:16 PM |