Prev: query
Next: Display image from sql database
From: Cralis on 31 Jul 2010 10:58
Hi guys,

I am in the process of designing the table structure for some work
we're doing, and am looking for advice on table design with regards a
particular requirement. As I am contracted to a govornment
organisation, I am giving examples, and not the real details.

Requirement 1 has been covered: A person can have a Court Appearance.
A court Appearance can have a number of Charges heard for that person
at that appearance. A judge can Order the person to some form of
sentance for a particular charge.

So, We have a Person table, an Appearance table, which has a PersonID.
We have a Charge table, which has an AppearanceId. And then we have an
Order, which would have a ChargeId.

That normal flow of requirements has been done. In phase 2 of the
project, we have been told that an Order can be linked directly to a
person! So, no appearance... no charge... just a direct link from an
Order to a Person.

The only way I can think of doing this is to simply add a PersonId to
the Order table. That means the PersonId would be nullable (As this
link (Order to Person) doesn't happen often. But because of this, I'd
need to make ChargeId nullable too. So both foreign key links can be
null.

If the order is linked to a Charge, then ChargeId has the value of the
PK of the Charge, and PersonId would be NULL. If the Order is linked
direct to the person, then ChargeId is NULL and the PersonId has the
PK of the Person table.

Is this a valid design, or is there a better technique to accomplish
these two requirements?
From: Tom Cooper on 31 Jul 2010 13:19
That sounds fine to me, but add a constraint that enforces that PersonID and
ChargeID cannot both be NULL and also they cannot both contain a value -
that is exactly one of them is NULL. Something like

Create Table FooOrders (OrderID int primary key,
ChargeID int Null,
PersonID int Null,
Constraint PersonOrChargeMustBeNullButNotBoth
Check (Case When ChargeID Is Null Then 1 Else 0 End +
Case When PersonID Is Null Then 1 Else 0 End = 1)) ;
-- this should work
Insert FooOrders (OrderID, ChargeID, PersonID) Values (1, Null, 2);
go
-- this should work
Insert FooOrders (OrderID, ChargeID, PersonID) Values (3, 4, Null);
go
-- this should not work
Insert FooOrders (OrderID, ChargeID, PersonID) Values (5, 6, 7);
go
-- this should not work
Insert FooOrders (OrderID, ChargeID, PersonID) Values (18, Null, Null);
go
Select * From FooOrders;
go
-- Cleanup
Drop Table FooOrders;

Tom
"Cralis" <craiglister74(a)gmail.com> wrote in message
news:7658b4c3-8793-427d-9cb5-1816482795ff(a)p11g2000prf.googlegroups.com...
> Hi guys,
>
> I am in the process of designing the table structure for some work
> we're doing, and am looking for advice on table design with regards a
> particular requirement. As I am contracted to a govornment
> organisation, I am giving examples, and not the real details.
>
> Requirement 1 has been covered: A person can have a Court Appearance.
> A court Appearance can have a number of Charges heard for that person
> at that appearance. A judge can Order the person to some form of
> sentance for a particular charge.
>
> So, We have a Person table, an Appearance table, which has a PersonID.
> We have a Charge table, which has an AppearanceId. And then we have an
> Order, which would have a ChargeId.
>
> That normal flow of requirements has been done. In phase 2 of the
> project, we have been told that an Order can be linked directly to a
> person! So, no appearance... no charge... just a direct link from an
> Order to a Person.
>
> The only way I can think of doing this is to simply add a PersonId to
> the Order table. That means the PersonId would be nullable (As this
> link (Order to Person) doesn't happen often. But because of this, I'd
> need to make ChargeId nullable too. So both foreign key links can be
> null.
>
> If the order is linked to a Charge, then ChargeId has the value of the
> PK of the Charge, and PersonId would be NULL. If the Order is linked
> direct to the person, then ChargeId is NULL and the PersonId has the
> PK of the Person table.
>
> Is this a valid design, or is there a better technique to accomplish
> these two requirements?

From: Cralis on 31 Jul 2010 19:04
Thanks Tom...
Adding the check constraint is the way I'll go then.
I was worried I was breaking some database design rule.

Fantastic, thanks.
From: --CELKO-- on 1 Aug 2010 13:03
>> Is this a valid design, or is there a better technique to accomplish these two requirements? <<

I am more inclined toward having an "outcome" code which has
sentencing as a subset and other outcomes such as "dismissal",
"dropped charges", "governor's pardon", "transfer to another court",
etc. as options. Be exact and avoid having NULLs play so many roles
since there are many ways for a case to be decided other than a
sentencing.

I would save NULL for "still in the slow wheels of justice, grinding
along" until you can get an outcome. Make it a true missing value in
the schema.

Having worked criminal justice, I am also looking for a docket number
and charges against multiple persons or legal entities. I would guess
you are a Civil or Domestic court.

Also, a charge is a code or something other than an identifier. Think
about it; identifier belong to entities like the VIN on a car.
Charges are drawn from a legal code; they are not unique creatures in
the Universe.

Get a copy of my SQL PROGRAMMING STYLE for a discussion of data
element names as a part of design.

From: BruceL on 2 Aug 2010 09:46
Actually for the Orders table, I would think the PersonID would always
be required.
The Appearance/Charge may or may not exist, but you cannot have an
Order without a Person it applies too.

 |  Next  |  Last
Pages: 1 2
Prev: query
Next: Display image from sql database