Data required changes - basically a Normalization question...

[SaraMegan]

I'm working on a contract tracking database for our Legal section, made up mostly of dates. And I'm still trying to fully comprehend Normalization. (I want to be good at this!! )

The tracking changes a little bit based on the type of contract. (ie they all have to go certain places, but some have extra steps and some may have fewer steps...)

So far, I have a central table, tblContracts, which holds:

ContractID (PK)
ContractName
Type (Foreign Key to tblTypes)
Amount (Foreign Key to tblAmounts)
30 date fields tracking where ALL contracts need to go.

Then I have tblTypes:

TypeID (PK, a number for option grouping in form)
TypeLetter (The letter used in Legal to refer to the type)
Type (Type ie - Computer, Lease, Licenses)

And finally, tblAmounts. The amount also has a bearing on where the contracts go. If it's under $2500, it has a shorter process. (doesn't have to go through the Governor and Council.)

AmountID (PK - numbers for option grouping in form)
Amount (ie Under $2500, Over $2500, Over $5000)

Basically, my questions are:

1) I've heard severalt imes that having huge tables is bad... Do I have too many fields in tblContracts? If I do, where do I put them all?

2) For the fields I have not yet added which are sensitive to the type of contract - how do I add those to the DB? Do I add them to tblContracts and just make that bigger? Or do I somehow make another table which attaches to the type it corresponds somehow? What if there is one stop that two different types of contracts have to make in the process?

3) Is it necessary to have a table for the different amounts for the contracts, or should I make that some kind of combo box with a limited list of stuff? Because, if the amounts were to change... for example, suddenly contracts over $2000 have to go to Governor and Council, that changes things... but changing the data in the table could cause much inaccuracy.

I apologize for my long-winded post. Just trying to be clear. Please ask me a question if I'm confusing. Thanks in advance for any and all help!

--Sara

 

[Mile-O]

30 date fields tracking where ALL contracts need to go.

Can you explain the need for these in greater detail as there will be an easier way.

 

[SaraMegan]

Surely!

Each contract needs to go through several steps before it's completed and signed and all that.

The contracts, for the most part, circulate as follows (with some slight variance depending on type and amount of contract):

Upon Receipt of Contract:

Prepare Three Originals
Acquire Certificate of Good Standing from Provider
Acquire Certificate of Liability Insurance
Acquire Certificate of Corporate Vote
Acquire Confidentiality Agreement
Acquire Criminal Records Release

If the contract is under $2500, the contract basically goes to our Deputy Commissioner for approval and it's done. We hardly ever get these, as they can be taken care of before they get to us. (We just work on the harder stuff.)

After that's all put together:

Acquire Draft of Submission Letter from the Fiscal Unit
Request Photocopies of Contract
Send to Attorney General's office

When it's returned from AG's office:

Send Signed Original with all Documents to Fiscal Unit
Upon Receipt back, send all to Administrative Services, to be submitted to Governor and Council
Date of meeting with Governor and Council
Date approved by Governor and Council

Upon approval from Governor and Council:

Date Returned from Governor and Council
Date original sent to vendor
Date second original sent to appropriate department head
Date copies sent to Personnel

So, these people are lawyers and they want to know when something is requseted as well as when it is finished. So for each action above, there is a request date and a received date, except where I specified "Date." ("Date original sent to vendor," etc.)

It's nuts.

They're lawyers.

I hope that was helpful in answering your question...

Thanks again!

--Sara

 

[RichMorrison]

Sara,

1)
It sounds like different contracts have different steps/dates based on some kind of contract "type".
Right ?
If yes, then you might want a table of standard steps/dates. The primary key would be ContractType and StepID.

2)
In tblContracts, does each and every contract have a value for 30 date fields ?
If the answer is no, then you probably want a separate junction table for ContractDate with a primary key of ContractID and StepID.

3) When you add a new contract you would select a "type". Then you would use the standard step/dates for the type and generate a set of ContractDate rows.

Make sense ?

RichM

 

[SaraMegan]

Thanks for the reply, RichM! I hope you're doing well! Long time, no see, as it were

in response:

1) It sounds like different contracts have different steps/dates based on some kind of contract "type".
Right ?
If yes, then you might want a table of standard steps/dates. The primary key would be ContractType and StepID.

2) In tblContracts, does each and every contract have a value for 30 date fields ?
If the answer is no, then you probably want a separate junction table for ContractDate with a primary key of ContractID and StepID.

There are certain steps, which I posted above, probably while you were typing your reply, that all the contracts go through, with the rare exception of contracts less than $2500. So they ALL go through about 6 of the EXACT same steps, then contracts over $2500 go through the EXACT same following 12 or so steps. But inserterted within those steps are some added bits depending on the type of contract.

So are you suggesting a many-to-many with a:

tblContracts which holds the ContractID, Name, Type, etc.

tblSteps which holds the different steps (dates)

Junction table which holds the... nope, I think I'm confused. Sorry.

3) When you add a new contract you would select a "type". Then you would use the standard step/dates for the type and generate a set of ContractDate rows.

This sounds like what I want to come out of it! But I think I'm confused about how you're describing going about it. I wish there was some type of picture I could look at. (I think better with pictures than words... i have drawings everywhere!)

Thanks again...

 

[RichMorrison]

Sara,

Yes, you have tblContracts and tblSteps and tblContractSteps.

For tblSteps, I'm thinking about some sort of "template" table based on contract type. This is a permanent table. In this table you would have a non-unique key of ContractType and many records for each ContractType. Each record for a type would represent one step. Sounds like there are 6 common steps for all contract types and 12 additional steps for contracts over $2500.

Summary:

1) tblContracts
-------------------
PKey is ContractID,
various fields that describe the contract including
ContractType

2) tblSteps
--------------
PKey is ContractType, (FKey of tblContracts)
StepNumber.
data fields might be StepDescription

3) tblContractsSteps
--------------------------
PKey is ContractID,
ContractType,
StepNumber.
data fields might be StepDone (Y/N) and StepDoneDate

Since tblContractSteps is a "junction" table, its primary key is the combination of the PKeys of tblContracts and tblSteps.

Here is an overview:
1) You add a new row to tblContracts and select a Type. When a new row is saved, some code runs to insert new rows in tblContractSteps. The code selects all the rows from the "template" table (tblSteps) for the contract Type. This happens once for each contract.

2) As the steps are completed, some person updates the ContractStep table to say "this step is done". Maybe you add a done date too. This happens many time; sounds like 18 steps would be common.

HTH,
RichM

 

[SaraMegan]

Hi, Rich!

Thanks for your prompt reply. I've been a little hung up with other projects, but am now getting back to this one. I think I understand the basic table structure, but I have a couple questions about how this works:

1) You add a new row to tblContracts and select a Type. When a new row is saved, some code runs to insert new rows in tblContractSteps. The code selects all the rows from the "template" table (tblSteps) for the contract Type. This happens once for each contract.

When you select the type of contract, does this just select the necessary corrsponding fields from tblSteps? (you can really *do* that?!) I wouldn't have the first clue about coding that... but i can do a search and post back if I have troubles.

2) As the steps are completed, some person updates the ContractStep table to say "this step is done". Maybe you add a done date too. This happens many time; sounds like 18 steps would be common.

many steps have a request date and a complete date. Would I split these into two different steps? Also, would I have to have something separate to say "this step is done" other than the date the step was completed? Because if there's not date, it's not done, and if there is a date, that means it is done. Is there some kind of value to having that stored separately, because it seems to be redundant. The date is what they're looking for.

Thanks again, Rich. You're a trooper, as always. Hope the weather's nice down there!

 

[RichMorrison]

Sara,
<<
When you select the type of contract, does this just select the necessary corrsponding fields from tblSteps? (you can really *do* that?!) I wouldn't have the first clue about coding that... but i can do a search and post back if I have troubles.
>>

I imagine some kind of New Contract form. In this form you would have listbox of contract types. You would pick a type from the list. When all the info is done for a new contract, you would click a "Save" button. A sub runs for the "OnClick" event. In the sub you would run an append query. The query will use parms from the form - contract ID and contract Type - and select all records from tblSteps that have the same contract Type. The selected records are appended to tblContractSteps.


<<
many steps have a request date and a complete date. Would I split these into two different steps? Also, would I have to have something separate to say "this step is done" other than the date the step was completed? Because if there's not date, it's not done, and if there is a date, that means it is done. Is there some kind of value to having that stored separately, because it seems to be redundant. The date is what they're looking for.
>>

Two dates in one step record sounds good; request date and done date. If the done date is not null then the step is done. I also imagine some kind of form for "tracking" the steps of a contract. The form would contain a continuous subform with all the steps in order. So anyone could see the current status of a contract, fill in a done date when they complete a step, and fill in the request date for the next step.
--------------------------------------------------------

Weather has been fair in MN. Not much rain in April but some in May so the trees and such are starting to bloom. We will be in Conn around Memorial Day for our son's graduation. That should be a nice weekend. (another struggling artist joins the ranks )

RichM

 

[SaraMegan]

Thanks, Rich. I haven't had a chance to implement this totally - things are busy here, especially since I went to part-time a couple weeks ago. But I have worked a little with it on a smaller scale and it seems to work great!

---

I went to school in Hartford, CT for a year, but it wasn't for me so I transferred. I hope your son has had better luck. And good luck to him in his future.