general question about db structure

[sfullman]

Hi All,

This is for getting an idea of what some of you might do in a similar situation.

Heres' the scenario: state of Connecticut has a probation and corrections procedure. Offenders on parole need to meet with parole officers each week. There may be administrators who may schedule the parole officers for meetings with the offenders. The db also needs to track friends and girlfriends of the offenders, attorneys, etc.

So, you have essentially two options on the db structure:

1. have separate tables for offenders, contacts, parole officers and administrators, maybe a few others. Advantage: you can customize fields and you can count on the safety that we won't mistake a parole officer for a violent felon :-) Disadvantage: not much except in exporting and perhaps contacting, and the fact that an essentially homogeneous entity (a person - i.e. we all have gender, age, name, address and phone numbers) and I think data may not be normalized as far as it can go.

2. have one consolidated contact table with common information, with a category for type. Probably one-to-many will work for the category in this case (parole officers normally aren't offenders also). Another thing for very tight security would be a one-to-one id table for the staff for example having just the primary key of staff and a status field, with cascade delete - if the staff id is not in that table, they don't exist as staff - make sure all queries for staff join on this table. Advantage: relationships are a bit easier, easier to check for duplicate names and so forth, simpler structure. Disadvantage: just that we have to maintain tight controls on knowing and filtering the data presented to users of the database.

I'm also looking at this becoming a huge database perhaps in the future so that has to be a consideration in any suggestions. One final note, any offender at least can list multiple addresses and periods valid, so there's a 1-many relationship between contacts and addresses.

Your suggestions plus real-life experiences with what you decided would be very nice and very helpful.

Sincerely,
Samuel Fullman

[bastien]

I do something similar (note this is just off the top of my head)

tables

personal
-id
-person_type (PO, con etc)
-first name
-middle name
-last name
-dob
-ssn
-physical characterstics(height weight etc)

personal_addresses
-id
-personal_id
-address_1
-address_2
-city
-state
-zip
-country

personal_relationship
-id
-subject_id (subject, parolee)
-related_to_id (other ie PO, girlfriend, mom etc)
-relationship (PO, Parent, SO, friend etc) (actual relationship to subject)

appointments
-id
-po_id (PO)
-subject_id
-date
-time
-location
-subject_showed (boolean Y/N)

personal_employment
-id
-emp_name
-boss
-address_1
-address_2
-city
-state
-zip
-shift
-job_desc

[cbj4074]

You might also consider looking into PostgreSQL if this has the potential to become a massive database; PostgreSQL is better equipped to handle the job.

[Sxooter]

cbj4074 wrote:

You might also consider looking into PostgreSQL if this has the potential to become a massive database; PostgreSQL is better equipped to handle the job.

Basically, the more complex the queries are, and the larger the dataset, and the more parallel writers you'll have, the better PostgreSQL generally is for the job over MySQL.

Also, it is generally far easier to migrate queries from PostgreSQL to MySQL, due to PostgreSQL's stricter syntax checking.

We see people on the postgresql lists all the time trying to make some crazy, non-deterministic query that worked in MySQL work in PostgreSQL, and everyone on the list is scratching their heads trying to figure out what the query actually does.

How many people are gonna be hitting this application at the same time?