If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many to many to many relationship
Hi
I have been asked to create a new database for use with 2012 bookings. As we take bookings up to 13 months in advance this gives me about 6 months. Been sitting here all day scribbling on bits of paper as I think I will change the relationships on the new DB. On our current DB which I wrote 3 years ago a client has a booking that is linked to an event. Client - Booking - Event So many clients can have many bookings. Many events can have many bookings. Standard many to many. But :-) as all clients are in a group (that may be any number from to 250) it seems to me to be better to have a many to many to many So Many clients can be in a group. Each client can be in different groups (at the same time) Each group can make a booking onto many events Each Event can have many bookings/groups Does anyone have anything to suggest with regards to problems with this ??? Am not even going to touch the keyboard for a few days until all my bits of paper make at least a little more sense. This is when being in a large IT dept would be helpful - but just me, so .......:-) I will look at other “stuff” later -- Wayne Manchester, England. |
#2
|
|||
|
|||
Many to many to many relationship
Wayne -
The answer will depend on your definition of a booking and your business needs. Can clients have bookings, or only groups? That is, does a client have to belong to a group to book an event? If clients can make bookings, then you may want to consider keeping your current booking relationships, but you can still add a group table, and the clients - group relationship. Instead of making a new group - booking relationship, add the group as an attribute to the booking. Can Clients book an event without being in a group? If so, you will still need the client - booking relationship. If on the other hand, only a group can book an event, then you have a start, but need to have an additional relationship that allows clients to be related to a group booking. Other considerations to take into mind: * What is the business need for adding the new tables / relationships? (Maybe you have new reporting requirements by Group and you need some Group attributes for the reports that you don't currently have.) What design best supports the business needs? * What new questions can be answered with the new design that could not be answered before? (Maybe you used to know that Client A was booked for Event B, but since Client A is in multiple groups that had bookings for Event B, you didn't know which Group the Client A booking was for.) * What additional maintenance will be required? (Do you really want to maintain which clients are in which groups? - is this feasible?) Will you need to track when clients join or leave groups? Will you want to maintain that history? * Is the new information important for your business? (Do you need to know which groups the clients are in, or can this be added as an attribute to the booking? * What impact will this have on looking at historical records? (Do you need to add information to historical data so it can be viewed in the 'new' forms, or do you have a cut-off date where old data is stored differently than new data? What will that do to historical reporting? -- Daryl S "Wayne-I-M" wrote: Hi I have been asked to create a new database for use with 2012 bookings. As we take bookings up to 13 months in advance this gives me about 6 months. Been sitting here all day scribbling on bits of paper as I think I will change the relationships on the new DB. On our current DB which I wrote 3 years ago a client has a booking that is linked to an event. Client - Booking - Event So many clients can have many bookings. Many events can have many bookings. Standard many to many. But :-) as all clients are in a group (that may be any number from to 250) it seems to me to be better to have a many to many to many So Many clients can be in a group. Each client can be in different groups (at the same time) Each group can make a booking onto many events Each Event can have many bookings/groups Does anyone have anything to suggest with regards to problems with this ??? Am not even going to touch the keyboard for a few days until all my bits of paper make at least a little more sense. This is when being in a large IT dept would be helpful - but just me, so ......:-) I will look at other “stuff” later -- Wayne Manchester, England. |
#3
|
|||
|
|||
Many to many to many relationship
The basics are that if a client is on their own they are a group of 1 person.
So it is group that makes the booking - a group can be up to 250 (the only reason for that limit is that is the largest plane we can charter). At the moment the bookings table (link between client and event) has a group ID and clients have a relationship with that ID. So reporting is run from a query on the group ID. The main difference with the new DB is that people will be able to view their own record on-line (and change some items if nessessary). Thanks for the input - I have read your answer a few times to take in all the points -- Wayne Manchester, England. "Daryl S" wrote: Wayne - The answer will depend on your definition of a booking and your business needs. Can clients have bookings, or only groups? That is, does a client have to belong to a group to book an event? If clients can make bookings, then you may want to consider keeping your current booking relationships, but you can still add a group table, and the clients - group relationship. Instead of making a new group - booking relationship, add the group as an attribute to the booking. Can Clients book an event without being in a group? If so, you will still need the client - booking relationship. If on the other hand, only a group can book an event, then you have a start, but need to have an additional relationship that allows clients to be related to a group booking. Other considerations to take into mind: * What is the business need for adding the new tables / relationships? (Maybe you have new reporting requirements by Group and you need some Group attributes for the reports that you don't currently have.) What design best supports the business needs? * What new questions can be answered with the new design that could not be answered before? (Maybe you used to know that Client A was booked for Event B, but since Client A is in multiple groups that had bookings for Event B, you didn't know which Group the Client A booking was for.) * What additional maintenance will be required? (Do you really want to maintain which clients are in which groups? - is this feasible?) Will you need to track when clients join or leave groups? Will you want to maintain that history? * Is the new information important for your business? (Do you need to know which groups the clients are in, or can this be added as an attribute to the booking? * What impact will this have on looking at historical records? (Do you need to add information to historical data so it can be viewed in the 'new' forms, or do you have a cut-off date where old data is stored differently than new data? What will that do to historical reporting? -- Daryl S "Wayne-I-M" wrote: Hi I have been asked to create a new database for use with 2012 bookings. As we take bookings up to 13 months in advance this gives me about 6 months. Been sitting here all day scribbling on bits of paper as I think I will change the relationships on the new DB. On our current DB which I wrote 3 years ago a client has a booking that is linked to an event. Client - Booking - Event So many clients can have many bookings. Many events can have many bookings. Standard many to many. But :-) as all clients are in a group (that may be any number from to 250) it seems to me to be better to have a many to many to many So Many clients can be in a group. Each client can be in different groups (at the same time) Each group can make a booking onto many events Each Event can have many bookings/groups Does anyone have anything to suggest with regards to problems with this ??? Am not even going to touch the keyboard for a few days until all my bits of paper make at least a little more sense. This is when being in a large IT dept would be helpful - but just me, so ......:-) I will look at other “stuff” later -- Wayne Manchester, England. |
#4
|
|||
|
|||
Many to many to many relationship
Wayne,
You should ask Albert Kallal about RIDES... (see his website). If I'm not mistaken, he has already done this, so you could borrow a lot of ideas from him. He writes about it on his site... http://members.shaw.ca/albertkallal/Rides/Rides.html -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
#5
|
|||
|
|||
Many to many to many relationship
Hi Pieter
Yes I have seen this is Allbert's site boefor but I don't need to get someone else to write a DB for us. I think (like most Co's) that we have very specific needs that can never be really matched by any off the shelf application. Don't miss-understand I think that Allbert's app is really good for the market for which it is intended it's just that this and other apps will simple not the tour operation that run. Also the UI is not what our admin team are used to in the last few years (again this is not a negative). I don't like that items should as click to move between lists as drag and drop has been available in access for years. There are other problems with getting an off thr shelf app not least (in our case and I should think most Co's) is that everyone wants some input. Accounts want reporting to excel, current and historical with airline/governemnt taxes breaking out, etc. Admin want push button reporting to clients (eg, letters, confirmations, ticketing) plus reporting to logisitc strands (eg, API anti terrorism report to airports, hotel lists - with drag and drop at run time). Admin also want some UI functions such as text sizing by users (as we have some visually imparied), etc. But, of course, the main requirement is that it should be stable and have an easy UI Sorry but you're never going to keep everyone happy - I gave up trying years ago, best to try and give everyone what you can within the time frame available. -- Wayne Manchester, England. "PieterLinden via AccessMonster.com" wrote: Wayne, You should ask Albert Kallal about RIDES... (see his website). If I'm not mistaken, he has already done this, so you could borrow a lot of ideas from him. He writes about it on his site... http://members.shaw.ca/albertkallal/Rides/Rides.html -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 . |
Thread Tools | |
Display Modes | |
|
|