Many to many to many relationship

[Wayne-I-M]

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

 

[Daryl S]

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?

 

[Wayne-I-M]

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

 

[Roger Carlson]

I try to write all my relationships in pairs:

Each Client can be in one or more Groups
Each Group can have one or more Clients
(standard M:M)

Each Group can have be booked to one or more Event
Each Event can be booked by one or more Groups
(question, is this true? if so, another M:M)

More questions:
Can a client be booked individually to an Event or must they be in a Group?
If a client can be in multiple groups and and an event can have multiple
groups, how do you propose to eliminate possible double bookings of the same
client?
On an abstract level, who (or what) actually gets booked? Do groups get
booked or do individuals (who happen to belong to a group) get booked?

It makes a difference. If groups are booked, the design would be:

Client -> ClientGroup <- Group -> Booking <- Event

If individuals get booked, it would be:

Group -> ClientGroup <- Client -> Booking <- Event

It depends who (or what) has the real relationship with the event: Groups or
Clients.

 

[Wayne-I-M]

The real world situation is ski holidays to the Italian Dolomites.

So
Mr Smith can book Holiday A with his family - making a group of 4
The same Mr Smith books another holiday (B) on his own
and again Mr Smith books with his workmates onto Holiday C in a group of 8

This would be a really simple M:M:M
As Mr Smith is in tblClients linked to tblGroups linked to tblEvents
I call holiday events as some of the provisions of all my databases is that
any bookings should be able to accomodation events outside the holidays
dates, ie. Mr Smith wants a night in a hotel at the airport prior to
departure, etc

You can see why it must be a M:M:M and not M:M as what if Mr Smith and his
wife Mrs Smith book onto Holiday D and E and Mrs Smith books Holiday F
without Mr Smith - who just happens to want to stay at the arport with his
wife prior to her departure (he may be driving her to the airport).
Therefore Mr Smith needs an "event" but not a holiday and an M:M would not be
right for this.

OK, now the other half (side)
Each event needs also the logisitic strands that go into either Mr Smith's
event or Mrs Smith's holidays - the strands are simpler as they really are
just M:M as event strand (ie, flight, transfer, hire session, etc) can be
only for a specific event. But this does add another relationship to the
other side of the chart

I "think" my bits of paper are starting to look "sort of" OK. Few more days
to go before the first tape of the keyboard.

It's OK, I ramble when doing stuff like this, makes me think better.

 

[Wayne-I-M]

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.