Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Relationship help

    I am writing a db for a travel agent, which records how many people went on different excursions, and the expenses and profit. The people are grouped by Guide, many Guides can go to each excursion (tblGuidesExcursion). Each Guide earns commission on the number of people they take to an excursion. Each guide has a different rate of commission for each excursion, and herein appears to lie the problem. I have made a table (ExcursionGuideCommission) which has fields:

    ID
    IDExcursionFK (this brings the excursion from tblExcursion)
    IDGuideFK (this brings the Guide ID from tbl Guide)
    Commission Rate (contains the number of commission rate, ie 5, for 5%)

    But how can I get the Commission Rate into the tblGuidesExcursion table or query? I have been following the train of thought that says it should be a one-to-one relationship from field [GuideExcursionID], which is the primary key in tblGuidesExcursion, to [IDExcursionGuideCommission], which is the primary key of ExcursionGuideCommission, but it won't enforce referential integrity.

    If I delete all the records (111) from ExcursionGuidesCommission then it will form the relationship, but that's not really taking me any further forward.

    I am really stuck, I have confused myself and I hope I have explained this clearly. Please help if you can!
    Attached Thumbnails Attached Thumbnails excursions relationships.jpg  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on the business logic

    Im assuming
    a company has a group of tours which run on mulitple times over the 'season'.

    only one guide will be assigned to any one event, mulitple guides can be assigned to any tours (eg you could have say a pool of 10 or more guides for any one tour, but only one guide can go on any one day when that event is being held, you could fudge it by having say multiple multiple entries for TorEvent (eg group1,2...groupn, or you could make the guideID part of the primary key of tourevent)

    there are agreed rates applicable to guides for specific numbers of people in the tour. guides have predetermined tours they can go on this is an intersection table between guides and rates.. it defines what rates have been agreed between the company and the guides based solely on numbers in the party. the valid from indicates when this rate came into force

    it doesn't (easily) handle the situation where for example guide A may agree a rate of X for groups of 10..15 people for say the pissup tour round the bay and charges Y for 10..15 people say for the 'foam' party... they may want to charge more to attend the foam party.

    a customer makes a booking for n in their party, where n is 1...x
    there will be mulitple bookings form differnet cusotmers in TourBookings for a specific instance of a tour event. the no in party refers to the number of people each customers is booking in as part of the party

    the problem with this model is that you will have to apply some form of logic check which makes certain that their is an appropriate rate for that specific guide. or it coudl default to the nearest available rate.

    OK so thats one idea of how you could model the problem.. I'm sure there are plenty of others

    HTH
    Attached Thumbnails Attached Thumbnails dBForumsLisaP.png  
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Hi Healdem.

    No, many guides can go to one excursion, taking their specific groups with them. Forget about sales totals, that part of the procedure is very simple and has little bearing on the problem, which is commission. Each guide earns a different rate of commission for each excursion, but that rate stays the same. If Emily takes 20 people to Knossos, then she will earn 5% on that excursion sales. The next day, she takes 15 people to Samaria, where she earns 6% on sales. Alex also takes people to Knossos, but earns 8%, etc etc.

    I have a table which lists all the different excursions, the guides and the rates of commission, is a autolookup query the way to go? But I can't 'do' the join!

  4. #4
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    I am not explaining this very well, so I thought I'd post again with a further explanation based on what ultimately I want the procedure to do.

    I want, a main form which is based on a table 'tblExcursionPax' (tblExcursionPax holds data about which excursion, on which date). Then I want a linked subform, datasheet view, which holds info on which guides attended that excursion, and how many pax (people) they brought. This would be based on table tblGuidesExcursion. On this subform I also want the rate of commission that each guide gets, a value which is brought in from another table, ExcursionGuideCommission. So when I make a new record in the subform, I select a guide, say 'Alex' from a combo box, then his commission rate automatically fills the 'commission rate' field.

    My problem is that I can't establish the relationship between tblGuidesExcursion and ExcursionGuideCommission. As far as I can see it should be one-to-one: a guide can go on one excursion at one time and earn one rate of commission. But this join won't enforce referential integrity, probably because there are 111 records in ExcursionGuideCommission and 13 in tblGuidesExcursion. But there HAS to be 111 records in ExcursionGuideCommission because each of those records is a unique commission rate for that guide on that excursion.

    Please help! Many thanks.

  5. #5
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    ... Or maybe a kind of monster 'Iif' statement, except it would have to have 99 possible outcomes which 'iif' can't handle...

    Please help if you can!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no deffo not IIF has its uses, and is a powerful tool when used appropriately, but even contemplating more than a few IIF's then that would indicate your basic data model is flawed.

    my interpretation of what you are saying is that there may be several guides on a specific trip, but each guide is only responsible for specific members of the overall trip. so in modelling terms making the guide part of a specific tour event makes sense. if the PK for that entity is the tour, a date and an allocated guide then you can run multiple sub trips each hosted by a different guide part of the tour for that day.

    I think the problem is how you tie back the rates to specific guides, and that is down to how you model that part of the problem

    you mention that each guide has a rate based on numbers in the party. but you also seem to have different rates for different trips. so does that mean the amount a guide gets paid is dependant on the trip and the number of people in the trips.

    fer instance each guide has an agreed schedule of rates for a trip and also a differnet rate based on numbers. or is is just numbers based.

    for now I wouldn't get to involved in your current table design, after all it doesn't seem to be meeting your business requirement. thae a step back and make sure the logic required is supported by the proposed data model. once the model is sirted out then the query may becoem a complex join but it logically should 'flow'

    so for now I'd suggest you revisit the fees structure. so that you have a clear understanding of the realtionship between a tour a guide the nuymbers on the tour and therefore how the rates can be derived. bear in m,ind that you will also need to account for how much each person on the tour pays. for example are differnt rate charegable for the trip basded on age or size of party, are different ratres applicable depending on when they apply for the trip.... is it like airline prices pay less if you book early enough, pay less if youbook more than one trip, pay less if you agree to go on the day, pay whatever yopur rep can squeeze out of you.

    as with most of these sort of problems there isn't necessarily any one right answer, and especially any one right answer from people who don't know the ins and outs of your business.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Firstly, thank you for your considered response. These kinds of questions require thinking about, and you have taken the time to do so and I appreciate that.

    [QUOTE]if the PK for that entity is the tour, a date and an allocated guide then you can run multiple sub trips each hosted by a different guide part of the tour for that day.
    [QUOTE]

    Yes, there is a table which brings together 1. the excursion (ie, Samaria), 2. the date, and 3. the guide. So that's working.

    Regarding the fees structure, basically the guide sells the excursion to their group for as much as they can get for it, prices can differ week to week. I have a salestotal field, in which I record that total amount, ie, 500 euros. Down the line a bit, I divide that total by the number of people that went on the excursion, which then, over a period of time gives us an average amount that that tour was sold for - this is for strategic rather than operational purposes. It is in the guide's interest to sell for the highest price they can, because they earn commission on the total amount of their sale. And here is the problem!

    Each guide earns a different rate of commission for each excursion. These rates are stored in a table, ExcursionGuideCommission, but I can't find a way for the PK referred to before (excursion/ date/ guide) to join to this unique commission rate - I can't find a way to 'bring in' the rate to the correct (excursion/ date/ guide).

    I think that my current design will do it, based on this further information, do you agree? I think that I am missing a vital piece of knowledge about how to 'bring in' a value into a query. I've looked into Case Select and Switch but they're not going to work either.

    It's kind of... 'join PK number 10 (tblGuideExcursion) to FK number 42 (tblGuideCommission) but how can access know that that's the FK I want?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I remain to be convinced about your one to one relationship in that model, and Im not certain the relevance of the other links.

    OK I'm still no further ahead in terms of how the rates are quoted.

    is there a flat rate for guide X, irrespective of the trip but varying purely on the number of people in the group.
    or is there a schedule of rates for each guide for each trip varying on the amount of people in the group

    eg
    is its
    LisaP 1..10 5%, 11..15 4.75%, 16..20 4.5%
    PetraL 1..10 3%, 11..15 2.75%, 16..20 2.5%
    or
    LisaP Knossos caves 1..10 5%, 11..15 4.75%, 16..20 4.5%
    LisaP Knossos castles 1..10 4%, 11..15 3.75%, 16..20 3.5%
    PetraL Knossos caves 1..10 3%, 11..15 2.75%, 16..20 2.5%
    PetraL Knossos castles 1..10 5%, 11..15 4.75%, 16..20 4.5%
    ...

    as said before I think you need to take a step back form any one design.. don't get committed to any model just yet, but validate what is being proposed and make certain it hangs together before designing forms or other stuff.

    if you are notr entirely clear about what normalisation involves then have a good long look at
    http:///www.r937.com/Relational.html
    or
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    There is a flat commission rate for each guide on each trip, with no relation to the number of people they take, or their ages or anything else. It doesn't matter if they take 5 people or 50 to Samaria, they will still get their, (say) 5% on the sales total. The commission rate is calculated on the sales total - it's that easy.

    There are a couple of zero-rated commissions.

    Have I explained better now? You see - it HAS to be one-to-one because there is only one possible outcome on both sides of the join! I think.
    Attached Thumbnails Attached Thumbnails GuideCommtable.jpg  
    Last edited by LisaP; 08-10-09 at 06:50. Reason: added attachment

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its very rare I come across one to one realtionships, rare but it does happen.

    I still think there is some work to bottom out your business requirement of selcting what rate is applicable for which tour.
    but heres a coupe of variants
    variant 1 is similar to the previous version, except the the guide ID is aprt of the customer booking

    variant 2 takes a different view, which allows a bookign to be made, but no guide is allocated until later. operationally that may make more sense if you allocate guides to groups once you knwo how many peopel have booked for a specific tour event.

    in version 2
    Customers, Tours, Guides & rates... self evident (I hope)
    TourEvent is a specific isntance of a tour, where the tour ID is associated with a specific date. eg "Round the Island tour on the 14th of August 2009
    TourBookings records a specific booking made by a custoemr for a specific instance of a tour
    Tourguides associates a guide with a specific tour, eg LisaP is registered to do guides for the following tours....
    AllocatedGuides associates a specific guide with a specific customer booking. this has the advanatge that the guide can be allocated after the booking is registered.
    guiderates associates a guide with a schedule of rates. note thois approach will not handle (tidily) the situation say where LisaP had one schedule of rates for Knossos and another for Corfu. yes you could do that by defining is as Knossos rates and another as Corfu rates.. but thats not really leveraging the power fo relational databases to maintain database integrity. note we have a valid from frlag as part of the PK so we can handle historic data, we know what rate is applicable for which guide over time.

    again its all down to interpretation as to what the business actually needs or wants
    Attached Thumbnails Attached Thumbnails lisap01.png   Lisap2.png  
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Guides go out to the hotels and sell excursions. A booking cannot exist without a guide specified at time of entry.

    Customers don't make bookings - the only recorded information about customers is that 20 of them went to Samaria last Wednesday with Alex, 15 went with Vicky, etc. No names, no ages, not what they paid, nothing. A customer cannot book an excursion without a guide, so we will not have a situation where we allocate guides later.

    Thank you.

  12. #12
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    The bit which is killing me is the bit you say ' the situation say where LisaP had one schedule of rates for Knossos and another for Corfu'.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So the first paragraph is now altered
    "Guide earns commission on the number of people they take to an excursion".

    OK so you have a tabel of rates.. is that table of rates specific to a tour or a type of tour
    eg
    you have various city tours all of which are rate X%
    or
    tours of town A N%, town B O% and so on....

    ok so going back to the first model
    then you can drop the guiderates table, as you can now identifythe rate applicable based on the association between guides and tours tourguides. I'd also suggest you consider defining the ValidFrom date in tours guides as part of the PK.. that way round you will be able to track rates over time.

    I suspect you don't need Customers and you possibly don't need TourBookings, that could be replaced with either a cash sum in TourEvent or a number and cash sum. if you have just the cash sum all you know is that rep X got Y amount of money, not that they got Y amount from Z customers at an average price of blah. its makes comparisons on a week by week basis or year by year basis harder. ie you don't necessarily know if rep X is performing better.. are they too desperate to sell tours and selling to cheaply, are they good at sales and so on...

    so looking at the model below you still need to wqrk out how or where you want to store the amount taken in bookings. that could be a cash sum in TourEvent, Cash Sum and No People or add back in customers and TourBookigns entites
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    It's this: 'tours of town A N%, town B O% and so on....'

    No, I don't need 'Customers' or 'Tour Bookings'.

    I'm not sure if your diagram will work for me, I'm going to execute it later this afternoon. Just one thing, what's the (10) or the (6) you refer to in fields?

    I shall reply later this afternoon or more probably tomorrow, hopefully with a successful result. If unsuccessful, I do hope you will help me more.

    Many thanks Lisa

  15. #15
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    OK I have worked up your model but had to stop on the relationship between 'guides' and 'tour event'. There are many guides to each tour event - not just one - so I suppose this will require a separate table. I attach a jpg of your model (excursions relationships) and a jpg of my original model (excursions relationships original) which shows the extra table, tblGuides Excursion1. It didn't work though.

    Also, this may not help you but it does help me to visualise the end result, I attach a jpg of how I'm thinking the form will look. You see that 'tour event' is the main form, with a datasheet subform of the many guides, which should also include the relevant commission rate.

    Thank you!
    Attached Thumbnails Attached Thumbnails excursions relationships.jpg   excursions relationships original.jpg   form example.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •