A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Many to many to many relationship



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2010, 05:18 PM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old May 25th, 2010, 05:52 PM posted to microsoft.public.access
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default 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  
Old May 25th, 2010, 06:05 PM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old May 25th, 2010, 09:01 PM posted to microsoft.public.access
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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  
Old May 27th, 2010, 09:37 PM posted to microsoft.public.access
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.