Composite key table to history table

[blackduck]

tbl DeptItem
pk DeptId
pk ItemId
fk ScoreId

tbl ScoreHistory
pk ScoreId
?? DeptId
?? ItemId
TimeStamp
fk UserId

I need to keep a history of all scores that a deptItem has. My problem is I cant seem to build a form to show what the lastest score is but add a new score.

Is the structure correct to start with, or would I be better to go back to removing the scoreId from both tables and having composite key in Score History of DeptId,ItemId,TimeStamp?

eg.
tbl DeptItem
pk DeptId
pk ItemId

tbl ScoreHistory
pk DeptId
pk ItemId
pk TimeStamp
fk UserId

any help would be greatly appreciated.

 

[fneily]

It looks as though a Dept can have many Items. Can an Item belong to many Depts? I don't know, so I'll assume it can. So I would have four tables: tblDept, tblUser, tblScoreHist, and tblItem.
tblDept would just have as a primary key a department's abbreviation then description.
tblUser would have primary key UserID with user info fields.
tblItem would have ItemID with item info fields.
The tblScoreHist would be a so-called junction table. It would have it's own primary key, such as SHID then the primary keys of the other tables as REGULAR fields - DeptID, UserID, ItemID. It would also contain all COMMON fields such as Score and Date.
All the tables except the junction table can be pre-filled. Then using a form, tblScoreHist can be filled with the primary key entries as dropdowns. So the entry person only inputs Date(which could be defaulted) and Score.
Through queries, any info or analysis you want can be done.

 

[blackduck]

So far agreed - i have the dept, user, item tables as you said.

Depts have many items and items belong to many departments.

I will need to produce a list of all items that belong to a particular department.

Also, each dept item will have many scores by many users. I just want the most recent score, but I also need to keep a history of previous scores, date, and user that scored it.

So im still thinking i need that dept item junction table with composite key pkDept and pkItem to ensure they are tied down without duplicates?

what do you think

 

[fneily]

Yeah. That's my junction table tblScoreHist. The primary key to this table can be DeptID, ItemID, UserID. That way no user can take an item in a particular dept twice. Then, again, using queries, you can get your stats.

 

[JerryKlmns]

[/quote blackduck]each dept item will have many scores by many users

If User1 scores Dept1 on Item1 at Time1 and Time2, history table should have a time dimension (date) which should also be included in the composite pk. Unless this is not a possible combination.

 

[fneily]

Well, the junction table would include the common field date/time. It seems it doesn't have to be part of the key because it seems a user can't take the same "test" at the same time.

 

[blackduck]

I'm more likely to put the date as part of the pk instead of user.

Any user can score the item and them score it better again later. So I'm thinking the date as pk cause I will be more concerned with what the current score is in most cases. But as a backup they want to have a look at previous scores and who did them.

 

[fneily]

You may be right. I didn't think of repeating the same test. I guess it depends on if they allow that. Then your way would make sense.