Prev: Many to many relationship and association table
Next: The column prefix 'c' does not match with a tablename or alias used in the query
From: Craig Lister on 5 Aug 2010 04:33
Hi guys,

I have a requirement to change a previous design we have for table
layouts.

At the moment, a Court Appearance can have multiple offences heard at
that appearance. We therefore have an Appearance table, which
represents an appearance a person had at court. We also have an
offence table, which holds the details about the offence.

Because a charge can be heard at multiple court appearances (Same
charge.. gets adjourned at the first appearance, maybe adjourned at
the 2nd appearance, and then finalised on the 3rd appearance), we have
a linking table, Appearance_Offence.

So, that's working well.

Scenario:

Court Appearance hears about a charge.
(Row in Appearance table, row in Offence table, and linking row in
Appearance_Offence table).

Hearing is adjourned with no result to another date, which is a new
appearance for the same charge.
(Row added to Appearance, row added to linking table to link the
charge to the new appearance).

Hearing is adjourned again with no result, to another date, which is a
new appearance for the same charge.
(Row added to Appearance, row added to linking table to link the
charge to the new appearance).

Final hearing, and the court gets an outcome.

That is working 100%
BUT... in phase 2, they have added a requirement.

We need to allow for a charge to be split into a different charge, or
more than one charge can because one different charge. For example...
in the 1st appearance, the charge is 'Theft'.

It gets adjourned, but the judge says that it's no longer just Theft,
because the person assulted the victim with a gun. So, in the 2nd
appearance, the charge has been split into 'Assault' and 'Armed
Robbery'.

Now, in the 1st appearance screen, when you view the charge, you will
see it as 'Theft', but under it, you should see that in the next
appearance, it's becomes 2 charges: Assault and Armed Robbery. And in
the 2nd appearance vew screen, when you select one of the charges, it
must show that in the previous appearance, it was 'Theft'.

My question is - how do I modify the table structure to handle this?

I was thinking of adding a 'was_a' and a 'because_a' column, to the
linking table. So, at the moment, the table has:

AppearanceId, OffenceId

I am thinking it could become:

AppearanceId, OffenceId, PrevOffenceId (Nullable), NextOffenceId
(Nullable)

So, in the first appearance, we have:
AppearanceId 1
OffenceId 1
PrevOffenceId NULL
NextOffenceId NULL

They then call for the 2nd Appearance, and split the offences. So, we
have a new row in the linking table:

Appearance 2
OffenceId 2
PrevOffenceId 1
NextOffenceId NULL

Appearance 2
OffenceId 3
PrevOffenceId 1
NextOffenceId NULL

We also update the 1st apparance link record to be:

AppearanceId 1
OffenceId 1
PrevOffenceId NULL
NextOffenceId 3

AND ADD ANOTHER link record:

AppearanceId 1
OffenceId 1
PrevOffenceId NULL
NextOffenceId 4

(So we have to have a new linking record, because the first link got
split).

I think this is very unclear and hard for you to follow.

Does it make any sence?



From: Eric Isaacs on 6 Aug 2010 11:43
I think you're making this a bit too hard.

What links all of the appearances and offenses together as a case? Is
it a case or docket id or something of the sort?

Appearances have multiple charges, and charges can be related to
multiple appearances. The appearances are probably related to a case/
docket or something of the sort and the case/docket is also related to
charges/offenses, I would expect and that case is also related to the
charges. The appearances may have nothing to do with some of the
charges, etc. Some charges may be dropped without appearances (I'm
guessing.)

I see this as 3 tables (Cases, Appearances, Charges) all being related
with many to many relationships (with cross reference tables between
all three, 6 tables in all.) When you have that type of relationship,
you can sometimes simplify it by taking the 3 cross reference tables
required to maintain those many to many relationships between those
other tables and combining them into a single table in between all the
other tables using all three primary keys from the three main tables.
I wish I could draw this for you. Here, look at slide 21 here:

http://www.google.com/url?sa=t&source=web&cd=3&ved=0CB8QFjAC&url=http%3A%2F%2Felearning.najah.edu%2FOldData%2Fpdfs%2Ftransforming%2520er%2520to%2520relational.ppt&ei=iShcTLbSMIT2swPktITgCg&usg=AFQjCNGHzrxwXdllpA7gQ7LyMxl8Yj5Udw&sig2=YWDelYthG0mutsuymy_zUQ

In that example, it shows how you can also model a many to many
ternary relationships between three entities. Whether you do it with
3 tables or 1 table between all three entities is a judgment call you
need to make. There are advantages and disadvantages to doing it
either way.

In this case, though, it seems to me that you might be better off with
just 3 tables between each of those entities to represent the many to
many relationships between each separately. But I wanted to point out
that the ternary relationship is still an option in this case since
you need to decide for yourself. Those three keys do not all need to
be required (unless you make them the primary key of that single
table.) But consider using a separate primary key if the
relationships are not required, for instance if you want to represent
case and charges, but no appearance. Or if you need to represent
multiple charges of the same type (3 assults.)

Off from the case, charges, and appearance table, you can have a
resolution table that lists possible resolutions to the charges. If
you use the three separate tables, I would imagine that the charges
would be resolved either between appearances and cases (in that cross
reference table) or between cases and charges (in that cross reference
table.) If you find that you need it in both places that's when the
case for the single table between all three entities becomes a better
fit because storing that duplicated information in two different
tables will make it difficult to keep in sync and error-prone.

I hope that helps.

-Eric Isaacs


 | 
Pages: 1
Prev: Many to many relationship and association table
Next: The column prefix 'c' does not match with a tablename or alias used in the query