Some Advice on Normalization

[davefwelch]

About 3 months ago, I created a database in a hurry (I had just started working here) so that I could begin tracking drinking water infrastructure construction projects. Now, I have some time to rethink how I hurriedly put it together so I want to reconstruct it, and add some useful capabilities, especially since we will begin receiving applications for funding for 2010 projects soon and I want to be able to start entering the applications and scoring them.

I've attached my current diagram of relationships and I wonder if someone can give me some advice because I KNOW that the database is not normalized enough. Some explanations of what I need to do.

As you can see, the primary table in the database is the Projects table. Each project is funded with at least one loan, and many with two loans. Each loan portion is listed in the Loans table. By "loan portion," I mean that for example, if project 1 is funded by two loans, then loans 1A and 1B are separate loans in the table. The SUM of their LoanAmount would be the total budget for that project. The LoanID field is part of the primary key for this purpose. I've also design the new database to hold information for all projects going forward, with the ProjectYear being a part of the new primary key as well.

The 5 tables whose names begin with "MSACCESS" are read only tables linked from an external database, perhaps oracle but I'm not sure. These tables contain lots of information about each Public Water System (the entities borrowing the money in each loan) like contact information as well as information about violations to assist us in scoring the new applications.

I think that I am getting rid of the Borrowers table as it is, because it seems that most of the information in there is saved in the "MSACCESS" tables. Annual Water Bill and MHI (Median Household Income) will be imported annually from external sources and will update their own tables. The primary purpose for the borrowers table was to update a reporting database that I am required to post data to quarterly. I think I will make a query that contains this information.

Once a project begins design and/or construction, or actually ANY time after a loan closes for the project, the water system begins drawing down the loan funds via payment requisitions. They generally submit one of these for each month, if they have invoices to pay during that month. Each requisition has lines on it itemizing the expenditures in general terms like Construction, Engineering, Legal & Admin, etc. This is the purpose of the PayReqLines table.

WHEW, that was a lot to write!! Anyone have any advice for me?

 

[The_Doc_Man]

Anything we say will only be an opinion. You are the one dealing with reality.

Search this forum for topics on normalization and on database design. Look in the theory and design section for articles on entity identification (to identify what entities of your business model need to be tracked.) That is how you populate your models.

Let's talk loans. Can one loan fund two projects? (We know the converse is true from your description.) That would change things a lot.

When you talk about "drawing down" loan funds, sounds like you are tracking transactions. You'll need a table for that, with an FK to the project. If you have to track the loan in greater detail (i.e. know WHICH loan you just tapped for funds), you also need an FK to the loans table.

You get data from external tables, but I wouldn't link to them except for importation. (That's just me, though.) I always wonder whether the link on my end is talking to a table or a seamless JOIN query - which wouldn't necessarily be normalized. If you are concerned, import your data in order to be sure that it is normalized.


I'll be honest, when I looked at the diagram, it made no sense because it is hard to understand those things at first blush. It takes study. You are the one working with this project. Asking us "is this normalized" is almost impossible to answer without knowing the nuances of each table, and without knowing exact dataflow of the model. We would take a long time to know that. You would do better to study normalization and then come back with specific questions.

 

[davefwelch]

Thanks for your insightful reply. I posted this with an understanding of what you are saying. I am appreciative of the insight that you have provided me. I DID spend some time reading up about normalization, and I've also taken a class that covered normalization. But it's been a while since I've practiced, I guess .

The answer to your question is Yes, one loan can fund two projects. Can you elaborate on how this should change what I am doing? Or does the following paragraph cover that?

Thank you for giving another perspective regarding the "transaction" table. I had not thought about it that way. I have a table that contains that information, specifically the payreq (payment request) table contains each transaction, and the payreqlines table (I think I will change that to payreqdetail) contains the detail of the payment request. For example, each request will contain 1 to maybe 5 lines of detail containing a description and an amount. It seems that this table would also be where I put the FK to the loan table, to specify which loan each line is to come from.