Design Input

[lespaul]

I am currently re-designing our non-normalized database and need some input.

When a bond is posted for a person it can be either a Surety Bond or a Cash Bond.

A surety bond is property or other valuables pledged against the bond and a cash bond is cash deposited with the court.

A bond can be posted by either a person or a bonding company.

Bonds posted by the bonding companies are ALWAYS surety bonds. Bonds posted by people are USUALLY cash bonds.

I have a table (tblBondCompany) listing all the authorized bonding companies of the court. I am creating a table to store the bond information:

tblBonds
BondID (PK)
PersonID (FK)
BondType (C or S)
BondAmount
PostedDate

now I also need a PostedBy field and here's where the trouble starts. If it's a bonding company I can just reference the BondCompanyID, but if it's a person posting a Cash bond, I need to store their name and address and other information for the bond refund. Also if it's a person posting a surety bond then I need to be able to record information about the property or other item of value that has been pledged.

So, any input on how to accomplish this? I have two or three different ideas, but am not sure on which is the best and want to see what you would do!

Thanks!



Leslie

 

[johnherman]

Add a column, PostedByID to the Bond table. PostedByID would be the key on a table BondPostedBy which would contain keys to both individuals and companies. Companies would reference the BondCompany table and Individuals would reference the BondIndividual table, where separate different facts can be assembled for each different bond poster.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[johnherman]

In data modeling terminology, BondCompany and BondIndividual would be subtypes of BondPostedBy (or whatever you want to call it).

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[jrbarnett]

How about a mandatory field to store the extra information, perhaps "Supplemental_Info" or "Notes".
Yes, you won't be able to easily search on it, but if it is for internal information only, it should be fine.

John

 

[RiverGuy]

I more or less agree with johnherman. I'm not sure if you would want separate BondCompany and BondIndividual tables to be normalized. Although I am not the foremost expert about every single rule for normalization.

I would have an EntityType field. It would denote if it was a person or company. Then some fields might not apply to the other. Obviously, both people and companies have mailing address. But people wouldn't have Contact Name neccessarily.

If you use two tables, then your join would be conditional.

So the table might look something like this:

EntityType: 1 for Individual OR 2 for Business
Name: John Smith OR Susan's Bonds
Mailing Address: 100 Main Street
License Number: NULL for John Smith OR #999999 for Susan's Bonds

I assume it would be similar to a manufacturing business which sells goods to both retailers and individuals. Wal-Mart may be a customer as well as John Doe.

 

[lespaul]

Thanks for the input. I'll think about everyone's comments and see if I can figure out the best way!

I already have a tblBondCompany that has the Bonding Company Name, Address, phone, etc and bonding limit, so I don't want that information duplicated in tblBond. I was thinking about another table (tblBondPosters). So I would have both BondCompanyID and BondPosterID in tblBond but only one of them can be populated?

Leslie

 

[RiverGuy]

I think your tblBondPosters becomes your only table for that information. It can have the Address of either the person or the company.

Think about it this way:

If you have a separate table for BondCompany and BondPoster, this is what you have to do to get the address in pseudocode:

If it is a BondCompany, to look up the address, join on the BondCompany table.

If it is a BondPoster, to look up the address, join on the BondPoster table.

Keeping them in the same table makes your query quick. To see all BondCompanies, all you have to do is a "SELECT * FROM BondPosters WHERE Type = Company".

 

[lespaul]

Yes but I don't want to mix 10,000 individuals who may post ONE bond with the 30 eligible companies who post hundreds of bonds.

And trust me your example of what I have to do to get the 2 different choices here is NOTHING compared to what I have to do now.

For instance, here is ONE query that I use in a Case Inquiry program. All this query does is find all the hearing events that have been scheduled for this case:

SELECT CMPHERMF.HERTYP, CMPHERMF.HERTIM, CMPHERMF.HERNGDAT, CMPHERMF.CHGSEQ, JUDGCD, JMTSHT, CHGABV, REDABV, CMPHERMF.PLEACD, DISMTX, CONSDC, DSMSDC, CMPHERMF.TUSER, CMPHERMF.LUPDATE, CITNUM, NOHPRINTBY, NOHDATEPRT, SPDTRL, SFTRDATE, SFTTIM, ADVENDT, CATDS, SUBSTRING(DISMTX, 8, 1) AS PREJ, COMNTS 

FROM CMPHERMF
 
LEFT OUTER JOIN CMPCOMNT ON CMPHERMF.CASPRE = CMPCOMNT.CASPRE AND CMPHERMF.CASNUM = CMPCOMNT.CASNUM AND CMPHERMF.CHGSEQ = CMPCOMNT.CHGSEQ AND CMPHERMF.HERTYP = CMPCOMNT.HERTYP AND CMPHERMF.HERNGDAT = CMPCOMNT.HERNGDAT 

LEFT OUTER JOIN CMPCHGMF ON CMPHERMF.CASPRE = CMPCHGMF.CASPRE AND CMPHERMF.CASNUM = CMPCHGMF.CASNUM AND CMPHERMF.CHGSEQ = CMPCHGMF.CHGSEQ 

LEFT OUTER JOIN CMPCONTX ON LEFT(CMPHERMF.DISMTX, 7) = CMPCONTX.CONCOD 

LEFT OUTER JOIN CMPDISCD ON LEFT(CMPHERMF.DISMTX, 7) = CMPDISCD.DSMCOD 

LEFT OUTER JOIN CMPCATEG ON LEFT(CMPHERMF.DISMTX, 2)  = CMPCATEG.CATCD 

LEFT OUTER JOIN CMPJUDMT ON CMPHERMF.JUDGCD = CMPJUDMT.JMTCDE 

WHERE CMPHERMF.CASPRE = ' + QuotedStr(strProcessCasePre) + ' AND CMPHERMF.CASNUM = ' + strProcessCaseNum + ' AND CMPHERMF.HERTYP = ' + QuotedStr(strHearingType) + ' AND CMPHERMF.HERNGDAT = ' + strHearingDate + ' AND CMPHERMF.HERTIM = ' + strHearingTime + ' ORDER BY CMPHERMF.CHGSEQ

notice all the LEFT OUTER JOINS - that's because the data is SCREWY!

So even if I need to do ONE extra step in the query, that's a MAJOR improvement over having to write queries like the one above!!!!





Leslie