Is this a good database design?

[Someday]

I am creating a database for work to keep track of employee incentives. I am having a hard time trying to figure out the best structure for it. I think I have a many-to-many relationship between the participants of the program and the jobs, and I need to be able to query them according to the year they are participating and the region. I thought about using a composite key of the Emp Number and PYear, and also having a PYear table but it seems both approaches need the participants and information to be entered each year and would make a new record each year. This doesn't seem like the correct approach. Is there a way to do it without making a new record for each participant for each year? What is the best approach?

This is what I was going to do. Your ideas will be greatly appreciated.
Participants Table PYear Table Job Table LinkingTable Metrics Table (Incentive Metrics)
Emp ID PK PYear PK JobID PK EmpID FK 1-to-Many with Job Table
JobID FK MetricID PK
PYear FK

[Someday]

Everything ran together. Here is the table structure so far.
Participants Table
EmpID PK
PYear Table

PYear PK
Job Table
JobID PK

LinkingTable
EmpID FK
JobID FK
PYear FK

Metrics Table (Incentive Metrics)
1-to-Many with Job Table

[Rod]

Hi there.

Your first dilemma seems to be whether to have a table defining Participation Years (a calendar) or not. As you foresee, such a table proliferates the compound keys (more about this later) without adding to the 'real' data but does make the DB interrogation more straightforward. Either scheme, with or without a calendar table, is valid and I suggest the choice should be made according to the following.

If a Participation Year has attributes that depend on and only on the year then use a calendar table with the year as the primary key. Otherwise investigate alternate schemes that do not use a calendar table.

What do I mean by this? Perhaps a couple of examples will help. Accounting systems often have a calendar table because each accounting period may be open or closed (etc.) and may have derived opening and closing balances (howls of protest from the anti derived data lobby ). Such systems however do not often implement all the relationships with other data; in fact the calendar table often exists in isolation as a look-up table with no relationships (remember you can always join tables at SQL time). In your situation I could imagine that the need for a Participation Year table may occur if there is a committee associated with each year and you wish to capture this information; or maybe the years are not regular and you wish to capture the start and end dates of each year.

So how could your situation be handled without a calendar table? Each participant needs two further attributes: a start year and an end year (the end year is null for current participants). This assumes each person's participation in the scheme is contiguous and starts/ends on year boundaries. (Well not quite true but handling mid-term dates makes it a little more complicated.) If a person's participation is fragmented then you will be forced into a separate table of Participation Terms.

To determine whether a person is current for, say, the year 2012 the following syntax (or similar) may be used:

WHERE NZ(PYearStart, 9999) <= 2012 AND NZ(PYearEnd , 9999) >= 2012

The first comparison sets the year to be 9999 if the start year is null (i.e. the person has not joined) and the second comparison sets the year to 9999 if the end year is null (i.e. the person is still current).

OK, what about those compund keys? My advice is, avoid compound keys; they seem like a good idea at the beginning but turn round and bite later on when coding. For tables that resolve many-to-many relationships, use a meaningless primary key (autonumber) and have each component key as a separate foreign key. Also consider whether you really need to slavishly include every possible relationship in your DB, remembering that joins may be created at SQL execution time.

Hope this helps. I'm going off-line for a few days but get back to me - or anyone else - for more advice.

[Someday]

Thank you for the good information, Rod. A couple questions.

WHERE NZ(PYearStart, 9999) <= 2012 AND NZ(PYearEnd , 9999) >= 2012

What is NZ? Could something like this be used to query the start and end date of the jobs if they are in the many side of the relationship?

[Rod]

The best I can do for you is reproduce the explanation from the horse's mouth.

Application.Nz Method

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.Syntax
expression.Nz(Value, ValueIfNull)
expression A variable that represents an Application object.
Parameters

Name	Required/Optional	Data Type	Description
Value	Required	Variant	A variable of data type Variant.
ValueIfNull	Optional	Variant	Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. Note If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

Return Value
Variant
Remarks

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the Nz function should always include the valueifnull argument.
If the value of variant isn't Null, then the Nz function returns the value of variant.
The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.
For example, the expression 2 + varX will always return a Null value when the VariantvarX is Null. However, 2 + Nz(varX) returns 2.
You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

Visual Basic for Applications
varTemp = IIf(IsNull(varFreight), 0, varFreight)varResult = IIf(varTemp > 50, "High", "Low")

In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.

Visual Basic for Applications
varResult = IIf(Nz(varFreight) > 50, "High", "Low")

If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. By including this optional argument, you may be able to avoid the use of an expression containing the IIf function. For example, the following expression uses the IIf function to return a string if the value of varFreight is Null.

Visual Basic for Applications
varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)

In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.

Visual Basic for Applications
varResult = Nz(varFreight, "No Freight Charge")

Example

The following example evaluates a control on a form and returns one of two strings based on the control's value. If the value of the control is Null, the procedure uses the Nz function to convert a Null value to a zero-length string.

Public Sub CheckValue() 
   
Dim frm As Form    
Dim ctl As Control    
Dim varResult As Variant 

   ' Return Form object variable pointing to Orders form. 
 Set frm = Forms!Orders

    ' Return Control object variable pointing to ShipRegion.    
Set ctl = frm!ShipRegion

    ' Choose result based on value of control. 
varResult = IIf(Nz(ctl.Value) = vbNullString, _        
    "No value.", "Value is " & ctl.Value & ".")

    ' Display result.    
MsgBox varResult, vbExclamation

End Sub