Table/Normalization help please

[monkeytunes]

Hey buddays,

I have a contract-tracking database that has been in use for about a year. It used to be that every contract had the same number of periodical "applications" with it - the companies we worked with would send in 4 applications over time.

So, I had this set up:

tblCompanies (the companies with whom we work)
- CompanyID (Primary Key autonumber)
- CompanyName
- Address, contact info, etc.

tblContracts (the contracts we issue)
- ContractID (Primary Key autonumber)
- CompanyID (foreign Key)
- DateIssued

tblApplications (things companies send back to us over time)
- AppID (Primary Key autonumber)
- AppDesc (description of the application - something like "Application 1", "Application 2" , etc.

tblAppsRecvd (Join table/log of apps received)
- ContractID (foreign key to tblContracts, so we know which contract this applies to) (also a composite primary key, so we don't duplicate an app to a contract)
- AppID (foreign key to tblApplications, so we know which application we rcvd) (also a composite primary key, so we don't duplicate an app to a contract)
- DateRcvd

Pretty self explanatory. Everything was keen, and I was reaping the rewards of a job well done.

But now we've expanded out contract offerings. He have introduced 2 new types of contracts, and each contract has a different amount of applications associated with it.

Contract 1 has 4 apps.
Contract 2 has 2 apps.
Contract 3 can have 1 to 8 apps. (The last app before a contract closure is always considered "Final" - so if we get 5 apps, it would be apps 1, 2, 3, 4, and Final.)

I've tried implementing a couple different schemes, but none seem to be fitting the bill. The type of contract will dictate how many apps we receive - i.e. If we are using Contract 1, then we KNOW we'll recieve 4 apps over time. So, is this as simple as adding a "ContractTypeID" to the tblContracts with an appropriate tblContractType table, and expanding tblApplication to more apps, or is there a smarter scheme?

Thanks, buddays!

 

[Pat Hartman]

Your schema seems to be properly normalized so yes, adding a ContractTypeID field and lookup table should be the only schema changes necessary.

 

[monkeytunes]

Thanks for the reply, Pat. Your normalization insight is always appreciated!


Let me ask you this - what if there are particulars in the applications that are being tracked? For instance, our contractors send us photos - those photos always come in as part of the last application, regardless of contract type. So, in Contract 1, we'd get the photos as part of Application 4. In Contract 2, we'd get them as part of Application 2. So, I have a "tblComponents" - the records can be very varied - loan documentation, lein documentation, legal docs, etc., so I'll just call them Component 1, Component 2, etc.

I used to have this "tblComponents" linked to each App; since all Contracts used to be the same, then it was the Application that dictated which Components were relelvent. The Join Table was tblComponentContracts - it contained the ContractID and ComponentID, and then would look back to tblComponents for the description, and there was a foreign key to tblApplication which would say which Application Group it fell into (App1 through App4).

But now the Contract dictates the number of Applications, and it also dictates which Components will arrive with which Applications. This means App2-Contract1 is very different from App2-Contract2; they both contain very different components. Any ideas on how this might fit in?

Thanks again!

 

[monkeytunes]

It's been about a day, and I'm still stumped on implementing the "Components" idea into the Applications and Contracts that I outlined above. So, just a lil' bump in case someone can help.

 

[Pat Hartman]

I don't really have enough information to offer a definitive solution. Start by finding a white board and bring along a pad or two or three of postits. Define entities and attributes and distribute them on the white board. Draw relationship lines between them that say things like "belongs to", "contains", "has", etc to define the relationship. Once you think you have it, create some tables and put data in them. Try queries to see if you can store the information you have and also retrieve it.