Input on design decision

[lespaul]

I am still working on my database redesign and want to get input on the best way to store this information.

When a person is arrested they may post a bond to be released. There are two kinds of bonds, a CASH bond or a SURETY bond. When a CASH bond is posted we record the name of the person who posted the bond so that when it is released, the money is returned to the person it originally belong to (instead of the criminal who may just take someone else's money, they are (alleged) criminals you know!!!). However, the money may be flagged to allow the defendant to use the bond money to pay fines and fees imposed by the court.

Ok, now we have surety bonds. This is when you get a bonding company to post the bond for you. However, an individual may also post a surety bond (like your house or mobile home or property). When a person posts a surety bond, we collect additional information about the "real property" that is being posted.

So, Cash Bonds I need to be able to record:
Name and address of the Person posting the bond and whether or not the bond can be applied to fines and fees.

Surety bonds placed by Bonding company need to record the bonding company

Surety bonds placed by individuals I need to record the individuals name and address as well as the property information.

Any design ideas?

Thanks!!!

Leslie

 

[johnherman]

This looks like a classic subtype situation. You have certain generic information associated with bonds in general and then have a subtype (1:1 relationship) table which contains the information for each subtype (CASH, Surety) and then another subtype for Surety bonds, Surety Individual and Surety Company. That is the classic logical model for subtypes.

For performance, some subtypes may be brought back into the parent (supertype) with null in columns where the information is not applicable to that type. In this example, a bond type and or surety bond holder flag might be needed in the physical model to easily segregate those types during querying.


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[lespaul]

so what would be the correct way to model this? I'm creating an Entity Relationship Diagram and I'm not sure on the best way to structure these relationships.

tblBonds
BondID
BondType
BondAmount
PostedDate

if bondtype = 'C' then join to this table:

tblCashBondDetails
BondID
BondIssuerFName
BondIssureLName


tblSuretyBondDetails
BondID
PostedBy - if BondCompany then have BondCompanyID
- if Person then have another table?

Thanks for any help!!

Leslie

 

[johnherman]

Logical or Physical model?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[lespaul]

Logical, I'm using Dezign for Databases and what I develop in the model, it will generate the SQL for to create all the entities I have modeled (at least that's the theory!! I guess we'll see if it actually works!)


Leslie

 

[johnherman]

Sorry. I went on vacation. PostedBy can be a single table with a PostedByType to indicate individual or company (subtype again).

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright