Table Design Question

[Ikkyu]

Hello everyone. I need help in the design of a database and any replies are appreciated.

I need to make a data base that records member info, member contract info, and monthly payments.

Each member will have personal info. Also each member will have many contracts, sometimes more than one per year. When one contract ends a new one is created and payments to each contract's account are payed monthly.

Below is my latest of about 20 table designs.

Table 1(Member Info)
Member Name
Phone no.
Address

Table 2(Contract Table)
Member Name
Start Date
End Date

Table 3(Payment Table)
Member Name
Month
Year
Payment amount

I know that I need to distinguish the contracts by a Contract ID, but I have tried to concatenate the member name and start date for the ID and couldn't get it to work.

I tried to use an autonumber field for contract ID but could not link it to payment table right. To get queries to work.


I need to be able to search by member name and show all contracts, search by contract and show payment info, etc.

Should I create a table for each month? If I don't, the payment table is "unqueryable" I think.


Sorry for the confusing post, I don't know how else to present the problem.

 

[speakers_86]

Something like this, methinks.

tblMembers
MemberID
MemberFirstName
MemberLastName
MemberAddress

tblContracts
ContractID
MemberID (FK)
ContractDate

tblPayments
PaymentID
ContractID (FK)
PaymentAmount
PaymentDate

 

[speakers_86]

Should I create a table for each month? If I don't, the payment table is "unqueryable" I think.

NOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!

As for the id fields in the tables, just use autonumbers. Look into database normalization.

 

[Ikkyu]

NOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!

As for the id fields in the tables, just use autonumbers. Look into database normalization.

Wow, how did you understand my post? I was more confused after reading what I had posted.

Thank you very much for your reply. I will try this, the only problem is instead of a payment date, I need to show a month and a year in my reports. BTW, what does (FK) stand for?
Edit: I realized that FK stands for foreign key.
Again, thank you very much

 

[speakers_86]

FK stands for Foreign Key. That is how Access knows that the two fields are related, thus relational database. Notice how the foreign keys can be linked back to a field in another table.

As for payment date, I would not use different fields for month and year. Use one field for Date. The report wizard has grouping and sorting that should be able to read the month or the year.

Definitely look into normalization. If you don't know how to normalize, you will never make a proper db.

 

[Ikkyu]

FK stands for Foreign Key. That is how Access knows that the two fields are related, thus relational database. Notice how the foreign keys can be linked back to a field in another table.

As for payment date, I would not use different fields for month and year. Use one field for Date. The report wizard has grouping and sorting that should be able to read the month or the year.

Definitely look into normalization. If you don't know how to normalize, you will never make a proper db.

Thank you for the tip on normalization. I just got finished reading an online tutorial about it. I do understand it a lot more.
About the payment date, the payments are actually made in advance and used throughout the month. For instance, the member pays $1500 at beginning of contract that has a start date of 8-8-09 and end date of 11-24-09. The $1500 is then split up to use during each month of the contract. Aug and Sept might have 250 dollars each allocated to them and Oct and Nov each might have 500 each. Then that contract ends and a new one starts with a new amount and allocation schedule.

I'm starting to think this might not be possible. I have reorganized the tables as you suggested. Everything works great except the payment thing.

Thanks

 

[Ikkyu]

I think I have my tables worked out. I have a book on access and I've looked online how to make forms, but I still can't figure this out. I made the tables as Speakers_86 suggested below:


tblMembers
MemberID
MemberFirstName
MemberLastName
MemberAddress

tblContracts
ContractID
MemberID (FK)
ContractDate

tblPayments
PaymentID
ContractID (FK)
PaymentAmount
PaymentDate

When I make the forms I have to enter the autonumber ID numbers into the forms that are linked to each master table. How can I make a form for tblContracts that will allow me to enter the Member's name instead of ID number?

 

[speakers_86]

For instance, the member pays $1500 at beginning of contract that has a start date of 8-8-09 and end date of 11-24-09. The $1500 is then split up to use during each month of the contract.

I believe you are talking about the accounting side of things here. Your initial receipt is unearned revenue, and each month some portion of that revenue is realized. I am not entirely sure whether that is significant to the design of your db. If so, a query or two may be of use.

Your accounting software should handle unearned revenue nicely, though.

As for your other question about forms, just use the combo box wizard. I think you may need to study Access much more before you delve too far into this. Find a beginners guide, study sample databases (MS provides these), youtube may help.

 

[Ikkyu]

I believe you are talking about the accounting side of things here. Your initial receipt is unearned revenue, and each month some portion of that revenue is realized. I am not entirely sure whether that is significant to the design of your db. If so, a query or two may be of use.

Your accounting software should handle unearned revenue nicely, though.

As for your other question about forms, just use the combo box wizard. I think you may need to study Access much more before you delve too far into this. Find a beginners guide, study sample databases (MS provides these), youtube may help.

Thank you Speakers_86, I'm goin to do more reading.

 

[speakers_86]

Because I am such a nice guy, here is a rough draft. It literally took me about 7 minutes to do this.

 

[Ikkyu]

Thank you speakers_86. That's just what I needed. I understand a whole lot more after some research.

 

[speakers_86]

Your welcome. I was wondering if you would ever look back at this thread again!