Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Relationship problem

    Hi everyone.

    I am trying to build a db which records excursion bookings for a travel agency. There are 11 excursions, and 13 guides. The excursions are grouped by week. Each excursion occurs once a week, the date that they occur does not need to be recorded. Each instance of an excursion is attended by many guides. Each instance of an excursion needs to record the guide, the number of people (pax), the sales total for each guide, and from the sales total the following commissions need to be calculated: Guide commission, Tour Op commission, Office commission.

    The commission rates are different for every guide, and also for every excursion. These commission rates are stored in a table (tblGuideComms)with 143 records (11 excursions x 13 guides).

    I have a combo on a form which selects the guide, but needs to go one step further and select the guide (and therefore also the commission rates) from tblGuideComms, based on the ExcursionID in tblExcursion. I hope the attached illustration helps explain this better.

    I know this problem requires a little thinking about, I've been trying to fix this db for a year now, on and off, and I would really appreciate some help. Many thanks.
    Attached Thumbnails Attached Thumbnails relationships.jpg   relationships in form.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by LisaP View Post
    Relationship Problem
    perhaps I might be of some assistance

    Quote Originally Posted by LisaP View Post
    I am trying to build a db.....
    Aw

    When modelling it is easiest not to use surrogate keys. You can add the surrogates once you implement the physical design (as you have done) but I want to mention this to explain why I have done the below. Using natural keys make the relationships much easier to follow and, even more importantly, helps you with normalisation especially second, bcnf, fourth and fifth normal forms.

    Code:
    Excursions(ExcursionName)
    Guides(GuideID)
    GuideComms(ExcursionName, GuideID)
    *  FK: (ExcursionName) references Excursions(ExcursionName)
    *  FK: (GuideID) references Guides(GuideID)
    ExcursionInstances(ExcursionName, WeekNo)
    *  FK: (ExcursionName) references Excursions(ExcursionName)
    *  FK: (WeekNo) references Weeks(WeekNo) (THE Weeks TABLE IS  OPTIONAL - YOU MIGHT NOT NEED IT).
    ExcursionInstanceGuides(ExcursionName, GuideID, WeekNo)
    *  FK: (ExcursionName, GuideID)  references GuideComms(ExcursionName, GuideID)
    *  FK: (ExcursionName, WeekNo) references ExcursionInstances(ExcursionName, WeekNo)
    I have not included none key attributes, but you can add these in. I think the design accounts for everything you need. I have not included WCWDate but I think that is similar to Weeks.
    An excursion cannot occur without an instance (which is an excursion occurring on a given week).
    A guide cannot be associated with an excursion without having already had their commission rates set.

    How's that look? Have I missed anything?

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Leaving the office now, will look tomorrow, thanks so much

  4. #4
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    I just don't have the knowledge to do this. How can relationships be created without primary keys? I have attached a screenprint of how I interpreted your instructions, but I'm sure it's not right. I can make the queries, forms and reports ok but this higher level stuff I just can't do. I think I have to abandon this project, admit defeat and go back to wrestling with a monster excel spreadsheet on this.

    Thank you so much for your time, I do appreciate it.
    Attached Thumbnails Attached Thumbnails relationships.jpg  

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are primary keys. The notation I have used is:
    TableName{PKCol1Name, PKCol2Name, Attribute1Name, Attribute2Name}
    Primary key columns are underlined, non-primary key columns are not underlined. I didn't bother putting in the non-primary keys to save time.

    If your only experience of database design is using Access then you may have fallen for one of its evils - making you think that Primary Keys and autonumbers are the same thing. Have a look here:
    The Relational Data Model, Normalisation and effective Database Design
    What I am talking about is natural keys vs surrogate keys.

    WRT your design - can you upload your database and I'll edit it at lunch.

  6. #6
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Interesting webpage. I now understand about natural keys and surrogate keys.

    I'm a self-taught access user and I normally manage to write little databases that do the job with no problem, but this one is killing me, slowly. Yes, I have fallen for many access evils, the worst one probably being 'Acess is so friendly and nice that anyone can do it!'

    Thank you for your time.
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you are self taught in Access then I recommend that at a minimum you study that link really closely. And I do mean at a minimum
    You have I think hit the nail on the head - one of the best things about Access is that it is easy for anyone to pick it up and start designing databases. One of the worst things about Access is that it is easy for anyone to pick it up and start designing databases.
    BTW - I started out self taught in Access and so did most of the main contributors to this forum.

    Anyhoo - please download the attached. Change the extension from txt to mdb. Every table has a surrogate, autonumber primary key. They all also have a natural key. Open each table in turn and study it. In particular look at the indexes dialog - each table has a PK and an AltKey. For half the tables the AltKey contains two or more columns(these are known as composite keys). Note also I made the required property for every column set to Yes. There are no non-key columns - you will need to add these in. Finally, I used unicode compression - use this for your text columns if you don't deal with unicode data (e.g. Chinese letters).

    I think I did not really appreciate how difficult your requirements are to model using a design that utilises surrogate keys. I am not certain it can be modelled using them. I am a fan of natural keys and only use surrogates when I consider them necessary. As you can see, the natural key of ExcursionInstanceGuides is an intersection of the natural keys of ExcursionsInstances and GuideComms. This ensures that there must be an excursion instance and also a guide for any excursion that goes out. As a side note, in relational theory all keys are considered equal - the designation of one of them as "primary" is considered somewhat crude and archaic amongst some theorists. Hence then I could use the alternate key in relationships instead of the PK - an example of how alternate keys are treated just the same as PKs.

    I may ask some of the other experienced modellers to look at this - I am interested in whether or not it is possible to model your requirements using surrogate keys exclusively.
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Screen shot of design
    Attached Thumbnails Attached Thumbnails excursions.gif  

  9. #9
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    I'm sorry, I can't do this. I appreciate your time.

    I can't even work out where to put a record, that is, which table will hold the info of each guide attending each each excursion instance. Can't go in ExcursionInstanceGuides because a record is first required in ExcursionInstances and GuideComms. GuideComms should be a kind of reference only table - a list of all the guides and the commission each one commands from each excursion, you can't add commissions or excursions...

    I can't see where to start. I wouldn't blame you one bit if you want to call it a day on this one.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I already entered some sample data. run this query:
    Code:
    SELECT ExcursionInstanceGuides.ExcursionInstanceGuidesID, Excursions.ExcursionName, Guides.GuideName, WeekNo.WeekNo
    FROM ((ExcursionInstanceGuides INNER JOIN Excursions ON ExcursionInstanceGuides.ExcursionID = Excursions.ExcursionID) INNER JOIN Guides ON ExcursionInstanceGuides.GuideID = Guides.GuideID) INNER JOIN WeekNo ON ExcursionInstanceGuides.WeekNoID = WeekNo.WeekNoID;

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by LisaP View Post
    which table will hold the info of each guide attending each each excursion instance.
    That goes in ExcursionInstanceGuides.

    Quote Originally Posted by LisaP View Post
    Can't go in ExcursionInstanceGuides because a record is first required in ExcursionInstances and GuideComms.
    That is correct - a guide cannot go on an incursion instance until they have a commission structure recorded.

    Quote Originally Posted by LisaP View Post
    GuideComms should be a kind of reference only table - a list of all the guides and the commission each one commands from each excursion
    Yes, so you need to populate this with every combination of guide and excursion.

    Quote Originally Posted by LisaP View Post
    you can't add commissions or excursions...
    Yes you can - I did. Based on the format of the diagram you would enter data in the tables from the left most tables to the right most. There cannot be an ExcursionInstance for an excursion or week that does not exist in the parent tables. There cannot be a GuideComms for a guide or excursion that does not exist in the parent tables, and so on.

    Quote Originally Posted by LisaP View Post
    I can't see where to start. I wouldn't blame you one bit if you want to call it a day on this one.
    Follow the above instructions and you will be fine. Perhaps try adding a single excursion and single guide and work from there.

  12. #12
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Right. Will do.

  13. #13
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    It's going great! Just one little problem, the ExcursionInstanceGuides records are not grouping into weeks - see attached. Could you help with this?
    Attached Thumbnails Attached Thumbnails form.jpg  

  14. #14
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    I dare not start mucking about with your relationship joins.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by LisaP View Post
    It's going great!
    Good - I am pleased.

    Quote Originally Posted by LisaP View Post
    the ExcursionInstanceGuides records are not grouping into weeks - see attached. Could you help with this?
    Without seeing the code it is hard to see. However, I suspect you have a sub-form and have not set the master-child link properties. Check the sub-form's properties for these and set them so there is a link on WeekID.


    BTW - have you studied the database? Does it make more sense to you now? I forgot to mention one thing - use the same name for a column used in foreign keys throughout your table. Don't, for example, call a column "ExcursionID" in one table and "ExcursionName" in another.

Posting Permissions

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