Database Design

[asp_learner]

Hi,

I am trying to normalize a collection database that has 10 years worth of data.

In Table 1, I have data on client that owes money, plus the following fields, collection agent, date sent to agent, amount sent to agent.
In Table 2, I have
Payment Number
Payment Date
Payment Amount
I want to create a third table for Collection Agent and delete that from the first table.

The way the database is set up now is that when a case gets returned by a collection agent, it gets changed to none and then if it gets assigned to another agent they changed it to the new name.

I figured out by normalizing the tables I will solve a lot of problems with this messy database.

What I am debating is should collection agent be also featured in the payment table to make it easier to see who collected whichh payments?

Thanks,
Eva

 

[pcs]

if i understand your proposed design, the answer if yes. the collection agent should be included as this will provide an audit trail that is more rigorous and conforms to standard accounting practices.

al

 

[Pat Hartman]

You should add the third table that contains only the CollectionAgentId and name information. You should replace the collectionAgentName in table1 with the CollectionAgentId (use a combobox for selection on the client maintenance form). Then you should also store the CollectionAgentId in the payment table. Use a combobox here also. Default to the assigned agent but allow the value to be overriden.

The reason for storing the CollectionAgentId in the payment table is that the payment table holds "point in time" data and you want to track the CollectionAgent who was responsible at the "point in time" when the payment was made rather than the agent currently assigned to the client.

 

[asp_learner]

thanks for the answer. Big help.

I also want to double check. Can I put Date sent to Agent and Amount Sent to Agent in the Agent Table.

I understand the use of using id number for agents but can I get away with just using the names. Right now I only have two agents that have been used since the beginning of time on this database.

Thanks,
Eva

 

[Pat Hartman]

Using names rather than IDs is acceptable. However, storing summary information is not recommended. You can use a query to summarize the amounts when you need them.

 

[asp_learner]

I know to normalize the database I really should break it down into several tables. I took over a very messy database and there will be making their own queries in the database who do not understand normalization.

To do the database right, I should set it this way:

Table 1
CaseNumber
Information about the person who owes money

Table 2 - Collection Agent Table
Agent Number
Agent Name

Table 3- Case
Agent Number
Case Number
Amount Sent to Agent
Date Sent to Agent
Date Returned from Agent

Table 4 - Payment Table
Payment Number (autonumber)
Case Number
Date Of Payment
Amount of Payment
Agent Number

Would it be okay for me to do this in 3 tables instead of 4.

I'd like to combine Table 2 and Table 4

Pat also thanks for helping me.

Thanks,
Eva

 

[Pat Hartman]

Why would you want to merge 2 and 4 but not 2 and 3?

Rather than doing that, you can make things a little easier for user who make their own queries. Open table 3 (or 4) in design view. Click on the AgentId column and then choose the LookUp tab. You can change the display control to a combobox.
a. Bind column 1 which should be the AgentID.
b. Set the column count to 2.
c. Set the column widths to 0";2" That way when they look at the data, they'll see the Agent's name rather than his ID.

I actually find working with tables set up this way annoying because it causes other problems. But, I think in your case it will work out OK. The alternative method is to create a query to join table 3 and 2 and another to join table 4 and 2. The users would use these pre-defined queries rather than the tables themselves.