Hi
I have a bit of a dilemma. I am creating a data model for a client management system. A client can request we perform a service for a third party on their behalf. We create a case for the third party who will usually be John or Jane Doe. We want to be able to link all of John / Jane Doe's cases together. We also want to be able to link John Doe to Joe Bloggs because they want a joint appointement.
So what Ive ended up with is:
case #123 for John Doe is related to case #345 for John Doe - same incident
case #123 for John Doe is related to case #555 for John Doe - same third party
case #123 for John Doe is related to case #890 for Joe Bloggs - joint appointment
I initially decided on this structure: Cross-referenceId, Case#1, Case#2, Cross-reference reason
but I found that in the scenario that John Doe has 10 cases things got a little complicated
My next idea was to use a Parent -> Child design e.g
Cross-referenceId, cross-reference reason, cross reference description
which would be in a 1:M relationship with
Cross-referenceChildId, cross-referenceId, case#
But Im not quite comfortable with the 2nd idea, can't put my finger on it, but I beleive there is a weakness in the design.
The advantage is I would be able to do this:
1, same incident, cases for john doe
1, 1, 123
2, 1, 345
3, 1, 900
4, 1, 901 etc
Life is a blast when you have a semi-automatic..
I have a bit of a dilemma. I am creating a data model for a client management system. A client can request we perform a service for a third party on their behalf. We create a case for the third party who will usually be John or Jane Doe. We want to be able to link all of John / Jane Doe's cases together. We also want to be able to link John Doe to Joe Bloggs because they want a joint appointement.
So what Ive ended up with is:
case #123 for John Doe is related to case #345 for John Doe - same incident
case #123 for John Doe is related to case #555 for John Doe - same third party
case #123 for John Doe is related to case #890 for Joe Bloggs - joint appointment
I initially decided on this structure: Cross-referenceId, Case#1, Case#2, Cross-reference reason
but I found that in the scenario that John Doe has 10 cases things got a little complicated
My next idea was to use a Parent -> Child design e.g
Cross-referenceId, cross-reference reason, cross reference description
which would be in a 1:M relationship with
Cross-referenceChildId, cross-referenceId, case#
But Im not quite comfortable with the 2nd idea, can't put my finger on it, but I beleive there is a weakness in the design.
The advantage is I would be able to do this:
1, same incident, cases for john doe
1, 1, 123
2, 1, 345
3, 1, 900
4, 1, 901 etc
Life is a blast when you have a semi-automatic..