Relationship Help - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Relationship Help

Lars Nilsson

4 years ago

Hi
I am working on a database for a wedding photography business. I have (among others) tables for "customers", "bookings" and "invoices". From the table "invoices" I have created a relation to "customers", and then a relation from "customers" to "bookings" (all between the fields "customerID"). But I also want to create a relation between "customerID" in "invoices" and "bookings", because the wedding guests are also able to buy certain prints and downloads from a wedding, and I want to track how much sales comes from each booking, not only from the main customer. I feel like that last relation may cause issues, but I don't know how to do otherwise. Any suggestions please?

I hope this question makes sense, as english is not my first language 🙂.

	Richard Rost @Reply 4 years ago
Sounds like customers have bookings and bookings turn into invoices. The questions you have to ask are: can a booking turn into multiple invoices? Will an invoice have multiple bookings? If you answered NO to both of those, then that's easy. Put a BookingID on each invoice, and an InvoiceID on each booking. If there can be multiples, then you'll need a many to many relationship.

	Lars Nilsson OP @Reply 4 years ago
Yes, there can be multiple invoices related to one booking, since not only the main customers can buy photos, but the guests as well. So I guess that I might need a joint table like "JointCustomerBookingsT". I will have another look at the many to many relations videos. Thanks

	Scott Axton @Reply 4 years ago
You may also want to check out the Imaging Seminar or at a bare minimum the Images video. That will give you information how to store the wedding images for your database using links - not actually in the database.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View