Assign case to diffreddnt user and track

[Sweetu]

Dears need a bit hint or assistance ,

What would be the structure for assigning case to another user & revert back to same and so on while keeping the track ,
such as jeorge created a case which saved in case table & case table have one FK with case to identify the user assigned,
but how we will arrange the form to keep track the current case with whom & the case with whom?

 

[Mark_]

Use a linking table to link Users to Cases. For each instance, you create a record with BOTH User ID and Case ID. I'd also put in a date field to track when it was assigned. This way you look for the latest entry to show who currently is assigned to a case.

 

[Sweetu]

Use a linking table to link Users to Cases. For each instance, you create a record with BOTH User ID and Case ID. I'd also put in a date field to track when it was assigned. This way you look for the latest entry to show who currently is assigned to a case.

in a separate table ?

 

[Mark_]

TblCase would hold Case information
TblUser would hold User information
TblCaseUser would link the two. You are looking at a many to many relationship.

One user can be assigned to many cases over time.
One case can be assigned to many users over time.

How you wish to display this to end users and how you want end users to create and update these links depends on how your program needs to work though.

 

[Mark_]

@Pat,

Any real advantage to keeping the assigned to and start date in both the case file AND the history table? For myself, it seems as though it would be easier to simply drive it off of one table without having to move data from one table to the next. Avoids the whole "Forgot to add a history record on change" issue if someone else works on the database.

 

[The_Doc_Man]

Avoids the whole "Forgot to add a history record on change" issue if someone else works on the database.

If this can happen, you have either a design flaw or an implementation flaw. Your users should always go through forms. If that is set up correctly, then when you perform a case reassignment, the FORM would ALWAYS remember to make the history entry transparently to alleviate the user from having to remember to do anything.

As to keeping the assign-to and assign-date (or start-date) in two places, that is at least a case of double-entry bookkeeping, which makes extra work, and is also very likely a case of improper normalization. Normalized tables are designed to prevent either of these things from happening.

 

[Mark_]

@ Doc,

Wasn't concerned with end users missing it, more if someone else works on the database.

 

[The_Doc_Man]

@Mark_,

Wasn't concerned with end users either. More concerned about a design that left something to chance for a new maintainer to not realize that reassignment needed work.