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

 

[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

 

[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