Normalized Table

[Lee]

Hi I hope someone can help....
I have a "BookingPayments" Table that I wish to redesign to be "normalized".
Each booking has it's own reference by autonumber created in a "Bookings"
Table. For each booking there could be up to 10 payments made on 10 different
dates. To be user friendly I felt I had to have these as seperate fields. But
obviously this is not then a "normalized" table, and queries pulled from this
appear nigh on impossible.
Everything else in my database is running great but I need to be able to
pull information by date from the "BookingPayments" table. I have created a
relationship between the fields to apply the date to the payment but it still
doesn't feel right to be using it this way in the first place. Could someone
please advise on a new design for this table so that it's "normalized" and so
queries by date can be used from it?

Many Thanks in Anticipation...

 

[Tim Ferguson]

I have a "BookingPayments" Table that I wish to redesign to be
"normalized". Each booking has it's own reference by autonumber
created in a "Bookings" Table. For each booking there could be up to
10 payments made on 10 different dates. To be user friendly I felt I
had to have these as seperate fields. But obviously this is not then a
"normalized" table, and queries pulled from this appear nigh on
impossible.

This is a standard many-to-many relationship:

Bookings(BookingNumber, DateOfFlight, CustomerID, etc...)

PaymentType(PmtCode, Description, etc..)

PaymentsMade(BookingNumber, PmtCode, Amount, DatePaid, Method,...)
Primary Key (BookingNumber, PmtCode)


The primary key of PaymentsMade ensures that a customer can only make one
payment of each Type in relation to each Booking. If you can take two
payments for the same deposit, for example, then you will need a
different PK.

Hope that helps


Tim F

 

[Lee]

Thanks for your reply, but I don't think I understand fully...I need the
table to allow anything up to 10 payments on 10 different dates per booking.
This is why I'm having the problem of a non-normalized table because if I set
it out this way there will be too many similar fields. As it stands the
payments table is related to the booking table by booking reference but
surely I shouldn't have to have a seperate table for each payment. I really
am stuck on this one because I seem blind to the answer. Are there any
samples I could look at anywhere that would allow for multiple payments to be
entered per ID?

Sorry if I'm trying your patience, but I am new to this and want to give a
good design.
Many Thanks again....

 

[Tim Ferguson]

Hello again

if I set it out this way there will be too many similar
fields.

No: at the moment you have too many similar fields. The idea is to move
all these repeated columns into repeated rows in a tall thin table.

As it stands the payments table is related to the booking
table by booking reference but surely I shouldn't have to have a
seperate table for each payment.

No, not a separate table: it's a separate record for each payment, all in
one table.

I really am stuck on this one because
I seem blind to the answer. Are there any samples I could look at
anywhere that would allow for multiple payments to be entered per ID?

This really is just a plain, straightforward many-to-many relationship.
Don't get too hung up on the "there are ten payments" argument because
within weeks of going into production, someone will want an eleventh one.

Have a look at the Northwind database to see how many-to-many
relationships work.

All the best


Tim F

 

[Lee]

Hi again!
Thanks for being patient with me! It suddenly came to me how to do it! I
feel stupid that I wasn't seeing it before but now my table is as follows:

PaymentID(PK)
BookingReference
Payment
Date

This is now a subform in a BookingPayments Form so the BookingReference
relate to each other and allows multiple payments.
Thanks again for helping!

 

[Tim Ferguson]

PaymentID(PK)
BookingReference
Payment
Date

Thanks again for helping!

Glad to help...


Tim F