 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / May 2005Normalized Table
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... > I have a "BookingPayments" Table that I wish to redesign to be > "normalized". Each booking has it's own reference by autonumber [quoted text clipped - 3 lines] > "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 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.... > Hi I hope someone can help.... > I have a "BookingPayments" Table that I wish to redesign to be "normalized". [quoted text clipped - 11 lines] > > Many Thanks in Anticipation... > =?Utf-8?B?TGVl?= <Lee@discussions.microsoft.com> wrote in Hello again >> This is a standard many-to-many relationship: >> [quoted text clipped - 4 lines] >> PaymentsMade(BookingNumber, PmtCode, Amount, DatePaid, Method,...) >> Primary Key (BookingNumber, PmtCode) > 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 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! > > =?Utf-8?B?TGVl?= <Lee@discussions.microsoft.com> wrote in > [quoted text clipped - 36 lines] > > Tim F =?Utf-8?B?TGVl?= <Lee@discussions.microsoft.com> wrote in news:E588FE90- BF0E-4822-AC79-2B88633BE7B2@microsoft.com: > PaymentID(PK) > BookingReference > Payment > Date > Thanks again for helping! Glad to help... Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.