db design help - challenge? - PHPBuilder Forums

db design help - challenge?

reeljustice

I would appreciate your suggestions as to the design of a database I am working on. It is for a law practice.

One of my problems is a design which allows some rather deep assiciations.

I am looking at the following tables:

FILE
This would be a master identifier. Each file coming into the firm would have a file number.

Each file would be of a particular type. For purposes of this post, I'll focus on a loan closing. Perhaps FILE_TYPE.

Each closing will be of a particular type loan which may be identified in the FILE_TYPE table.

Each closing has quite a few people involved in it. There will be the pre_closing inhouse staff, post_closing inhouse staff, closer, managing attorney, supervising attorney, borrower (can be more than one borrowers), may be a seller(s), a lender, the lender may have multiple contact people for different tasks, a mortgage broker which may also have multiple people invovled at different stages of the process, there may also be different branches of the lender and broker, tax search person, title search person and maybe more.

Each loan has quite a few tasks. I need to list the tasks for each loan. Each tasks will need to assigned to a person and have an assigned completion date. We will also need to see the status of each task. Each task will also need to be associated with notes about the task. I had initially thought to place these tasks in the LOAN table but then wasn't sure how I would go about associating the assigned person, status, notes, completion date to each task with the LOAN. Unless, I did something like

task_1
task_1_assigned_staff
task_1_assigned_atty
task_1_assigned_competion_date
task_1_status
task_1_notes

That seemed a bit overwhelming since there will probably be 20+ tasks, leading to a table with over 100 fields. Additionally, we would be typing information in about the same people 100s of times. All of these people should be in the CONTACTS table.

Does anyone have any ideas? I can expound more on the end requirements if you think that would help. I've looked at several CRMs including OpenCRM and Anteil. These suggest a seemingly good way to deal with CONTACTS and ORGANIZATIONS. But, they don't deal with a project which will ALWAYS have certain tasks assigned to it.

I've been reading a lot about object oriented PHP programming but am still at a loss as to where to begin with this structure.

To further deepen the task, I would like to make this a fully integrated contact relationship management system. For example,

a contact may have relations many contact-types and many company

a company may have relations to many events

a contact may have relations to many events

an event will have a description and times and tasks

an event may have relations to many companies and many contacts

a task will have a description

a task may have relations to an event to a contact and to a many times

Your help would be appreciated in this challenge.

Thanks,
Joel

ultraslacker

I would suggest that you not worry about php at the moment - focus on database design first. One word - normalization. If you are not familiar with this, I would suggest that you bone up on it, as your database will require a normalized design for you and your developers to remain sane.

Here's a basic schema with tasks + other odds and ends. I'm assuming that each loan is of a specific type, referenced from file_type.

tasks
task_id
loan_id
task_status
task_completion

loans
loan_id
file_type_id (? did you mean LOAN_TYPE ?)

tasks_contacts
task_id
contact_id
position (Att/Staff)

contacts
contact_id
contact_name

company
company_id
company_name

Depending on how you wish to handle the company-contact relation, you would either place company_id in contacts or have an additional table for a n-to-n relation.

I'm not sure exactly about the problem posed by "a project which will ALWAYS have certain tasks assigned to it". Do you mean triggers?

BTW, this sounds like a large project, is it you by your lonesome?

reeljustice

Is it you by your lonesome?

Yes, it is me by my lonesome. And, yes, it is quite a large project.

I'm not sure exactly about the problem posed by "a project which will ALWAYS have certain tasks assigned to it". Do you mean triggers?

Each loan WILL ALWAYS HAVE the following people associated with it:

pre_closing_staff

managing_atty

closer_atty

post_closer_staff

tax_search_oh (out of house person)

title_search_oh

borrower (s) --- could be more than one

mortgage_broker_lofc

mortgage_broker_unwr

lender

payoff_entity (s)

debt_pay_entity (s)

clerk_recording (s) ---- could be multiple instruments which need to be recorded and could be in different offices

My biggest question was how to associate this many DIFFERENT people with the single file_id. The same applies to the loan closing tasks. Each loan will have AT LEAST the following tasks associated with it. Each task will need to be associated with at least two (2) people -- the in-house person responsible for completing the task and the person who will be the recipient of the task.

title_ins_order -- precloser will order a title search from the out-house title search person. There may be outstanding deeds to secure debt, outstanding liens, other issues which must be resolved before a clear title can be obtained.

tax_search --- precloser will order a tax search from the out-house tax search person. There may be city taxes, county taxes, school taxes, state income taxes, federal income taxes

hazard_insurance --- precloser will confirm hazard insurance and get a copy for the file as well as list the carrier and amount of insurance

ETC

Each of these tasks will be associated with someone on staff. Each will have a target completion date. Each will have notes associated with them to explain any problems or efforts being made to resolve the problems.

I felt like I would handle the contact to organization relationship issue with a member table and member type table as they have done in OpenCRM.

One of the major things I want to accomplish is to be able to have the inhouse people, when they log in to the application, the first thing they will see is a list of OPEN files and the status of those files. If a task is within 2 or 3 days of deadline within a file, there will be an alarm and perhaps even an email notification. By clicking on those files with an alarm flag, the user will be able to see what must be done NOW.

Ultimately, various documents would be generated from the information in the database. For example, the precloser would use the initial intake information to generate a letter to the tax search person requesting the tax search, generate a letter to the title search person requesting the title search, etc.

I hope this helps to make the project a little clearer and I really appreciate your efforts to help me get the database design in shape. I am familiar with normalization. This is a rather large project for me and database design is clearly critical to ultimate success.

Sincerely,

Joel

ultraslacker

My biggest question was how to associate this many DIFFERENT people with the single file_id.

It depends on the data - one task_people table, or a table for each duty. As you do not know how many will be assigned to a task (but at least two), then it should be external to the task table.

BTW, I think php is unwieldy for this, especially for the report generation. And php has limited OOP support. I'm developing a political candidate information database and am using java for the interface.

What dbms are you planning to use?

reeljustice

It depends on the data - one task_people table, or a table for each duty. As you do not know how many will be assigned to a task (but at least two), then it should be external to the task table.

BTW, I think php is unwieldy for this, especially for the report generation. And php has limited OOP support. I'm developing a political candidate information database and am using java for the interface.

What dbms are you planning to use?

As for the dbms, MySQL. I don't disagree with you regarding the unwieldyness of PHP for this project. However, it is the language with which I have the most familiarity. It is my intention to begin learning Java some time this year.

I am working on an initial schema now. I will post it as soon as it is complete.

Thank you for your assistance.

Joel

reeljustice

OK guys, I think I am following you 🙂 The following examples should tell if I am on the right path.

But, before that, here is a link to Anteil's version of OpenCRM http://www.everywon.com/php/index.php If you create an account and plunder around, you will see what I was hoping to base this operation on --- loosely. This is Anteil's main site http://www.anteil.com/about.php I have placed documentation for the Anteil arcitecture here http://www.coastaloutdoors.com/anteil/design/docs/anteil_documentation.htm . The programming style guide can be found here http://www.coastaloutdoors.com/anteil/design/OpenCRMStyleGuide.html

Another example is the original OpenCRM which I have installed on my FQ account http://www.coastaloutdoors.com/opencrm/html/index.php Login using
Login: guest
Password: guest

Now, for the examples:

CONTACT_TYPE	
C_T_id	C_T_d
1	precloser
2	closing_atty
3	postcloser
4	managing_atty
5	borrower1
6	borrower2
7	seller1
8	seller2
9	tax_searcher
10	title_searcher
11	haz_insurer
12	flood_insurer
13	broker_co
14	broker_loan_ofc
15	broker_unwriter
16	lender_co
17	lender_processor

STATUS	
stat_id	Stat_d
1	Open
2	Complete
3	Closed
4	Paid
5	Resolved
6	Requested
7	Received
8	NotAppl
9	UnKnown

TASK_TYPE	
T_T_id	T_T_d
1	tax_order
2	tax_county
3	tax_city
4	tax_school
5	tax_st_inc
6	tax_fed_inc
7	tax_other_1
8	tax_other_2
9	title_search_order
10	title_iss_open_dsd
25	closing

NOTE: I have abbreviated the field names here to get them to fit in this example. C_T_id would be Contact_Type_id, C_T_d would be Contact_Type_id, T_T_id would be Task_Type_id, T_T_d would be Task_Type_descrip, etc.

Here is an example of what I think would be the "look-up" grid.

grid   Loan Loan_Type  Contact   Contact_Type            Task_Type    Status
1   A3-00001      1	      1	         1	             1	       7
2   A3-00001      1			                     2         4
3   A3-00001      1	      1	         1	             3	       1
4   A3-00001      1	      1	         1	             4	       4
5   A3-00001      1	      1	         1	             5	       4
6   A3-00001      1	      1	         1	             6	       4
7   A3-00001      1			                     7         8
8   A3-00001      1			                     8         8
9   A3-00001      1			                     9         6
10  A3-00001      1			                    10         9
11  A3-00001      1	    24	         2	            25	       1

Darn it, on a preview of the above, seems my numbers have gotten out of their correct columns. I hope you can make the correct reference. I'll try to get it straight tomorrow night.

In this instance, "Loan" would be the unique file number of the loan for the office. "Loan Type" could be refi-fha, refi-conv-ins, refi-conv-unins, sale, heloc, etc. Contact includes the id of the person from the Contacts table. "Contact_Type_ includes the id from the Contact_Types table such as precloser, closer, lender,etc.

The various types of people, the tasks and tasks status are all associated with the A3-00001 loan which happens to be the "Williams" loan. "Williams" would be identified in the "Contacts" table. Also, the various people with the "Contact_Type" would be identified from the Contacts table.

I am thinking the file intake form must contain a script or function call which checks the "Contacts" table to see if the person(s) exist in the Contacts table, and, if not, inserts there data in the table. The script or function call would also have to check the various other tables to see if the Organization (Company) exists, if not populate that table. The script or function call would also have to create default entries in the grid table for ALL the tasks normally associated with a loan. The intake staff person would make staff and attorney assisgnments and the script or a function call would assign that person's default function to the grid table.

This may not make as much sense as it did an hour or so ago. It's getting awfully late for me.

If I have followed you, great. If I haven't please try to steer me in the correct direction. If there is better way, by all means, don't worry about hurting my feelings with constructive criticism.

Thanks for you efforts. I know it takes considerable time.

Sincerely,

Joel

ultraslacker

Looks good. I'm a little confused about the grid section - is it a table or a result of a query? If it is a table, then I think it's almost right, but has some redundancy. Isn't loan_type an attribute of loan? Will this change in the loan's lifetime?

You could go further and address the redundancy of the first 6 rows where only task and status differ. Status is special as this will be frequently updated whereas other values will not be. You mentioned a notes field also... By associating loan + contact + contact_type in a separate table, your updates on status and inserts of new tasks would not have the repetition of the current grid. For instance, what if the precloser changes - in you current design you would need to update 6 rows. Explore the what-ifs to polish off the design.

It would be better to have data integrity checks and defaults supported in the data tier. Mysql does support defaults and foreign keys + transactions can be achieved through innodb tables. For this level of complexity though I would choose postgresql - it has better transactions, cascading deletes, views and stored procedures, which all can be put to good use in this type of project and save a considerable amount of application code.

reeljustice

I have cleaned up the grid table a little so that it may be easier to understand.

Originally posted by ultraslacker
Looks good. I'm a little confused about the grid section - is it a table or a result of a query? If it is a table, then I think it's almost right, but has some redundancy. Isn't loan_type an attribute of loan? Will this change in the loan's lifetime?

This is a grid. It is basically a "look-up" table joining the tables which have values that can be reused. The "grid" column is the unique id field for the grid table.

"Loan" is actually the firm's file number.

You could go further and address the redundancy of the first 6 rows where only task and status differ.

I'm not sure about this. For example, with respect to Loan file number A3-00001, which is a refi-fha loan, Contact 1 would be Judy and Judy is our precloser. We would also have Contact 24 who is our managing attorney assigned to oversee the preclosing process and deal with issues the non-attorney precloser Judy can't deal with. In our example, Judy would, by default, be assigned the 1st 6 task_types with respect to Loan No A3-00001.

Each loan would actually have over 75 or so tasks assigned to it by default as there are that many contact - task associations involved in the typical loan closing. Each task would have at least the non-attorney staff person, the managing attorney assigned to it. Some of the tasks, Task_Type 1 would also have an out-of-house person assigned to it which my be Contact 43, the tax search person.

I am wondering whether this is the most efficient and logical route to take. This grid "look-up" table would likely have 40,000 to 50,000 records in it during a typical year. Also, how difficult would the insert and update statements be for such a design? Remember, one of the UIs calls for a listing of loans having an open status with a flag for tasks which are nearly overdue and for those tasks which are overdue as well as a projected closing date.

Status is special as this will be frequently updated whereas other values will not be. You mentioned a notes field also... By associating loan + contact + contact_type in a separate table, your updates on status and inserts of new tasks would not have the repetition of the current grid. For instance, what if the precloser changes - in you current design you would need to update 6 rows. Explore the what-ifs to polish off the design.

Would that still be feasible in light of my explanation above?

It would be better to have data integrity checks and defaults supported in the data tier. Mysql does support defaults and foreign keys + transactions can be achieved through innodb tables. For this level of complexity though I would choose postgresql - it has better transactions, cascading deletes, views and stored procedures, which all can be put to good use in this type of project and save a considerable amount of application code.

I am in a shared hosting LAMP environment. My host does not offer Postgresql at this time. Also, I am completely unfamiliar with Postgresql. I am afraid I don't have the time to learn a new language or a new database at this point in time. My plate overflows.

BTW, a few of the guys over at my hosting site are helping also. The thread is at http://www.aota.net/forums/showthread.php?s=&postid=81601#post81601 . Some pretty sharp guys. I think all of you would benefit from each others' qualitative meeting. It is hard to find minds such as yours, particularly willing to help.

Given the above, what do you think and what is your recommendation? That is other than RUN FOR THE HILLS 🙂

Thanks,
Joel