Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39

    Unanswered: Advice on Relationship structure

    Hi

    I'm having problems with the database I'm currently working on.
    It requires that a list of employees be related to a course that a business is taking - So if Business A takes a particular course on a particular date, there is a list of employees that attended.

    However, this list changes for every course, so it can't be directly associated with the business - it has to be linked to the Results table (which is the transaction table between Businesses and Classes)

    Also, more than one business will attend a course. So of all the employees attending a course, One group is from Business A, one group from Business B, etc.

    I've attached a picture with the current relationships, and I think that it should work. The only problem (I think) is that I can't enter an EmployeeGoupID into the EmployeeGroup table.

    Can anyone tell me what I'm doing wrong here?
    I'd be happy to post more details/pics if it would help

    Thanks in advance,

    Frekster
    Attached Thumbnails Attached Thumbnails relationship structure.gif  

  2. #2
    Join Date
    Nov 2003
    Location
    Queensland
    Posts
    17

    Relationships

    One quick question for you, you don't seem to have any connection between the employees table and the business table. How do you figure out which employees come from which business? It might be helpfull if in the employees details or employee groups detail that you add a column called business name and and that way you can link those tables

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You need a couple more tables...

    employees should be linked to businesses, groups should be linked to businesses, then you should have a "group detail" or what-have-you that would assign employee id's to a given group id. Then bring the group id into a "class attendees" table or something of the sort, which is linked to your current results table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39
    Thanks guys,

    Only problem is, I can't quite see how to do this.

    The Results EmployeeGroupID is unique for each combination of BusinessName/ClassID. This provides the link between the Business and the Employee. I can't link it directly to the Business table because the group of Employees is different for each class/course they do.

    I just figured that a link with the Results table was the best place to put it. Could someone explain the reasoning behind linking directly with the Business table in the above scenario.

    And Teddy... I'm not sure I can make sense of that If you have the time, is there a possibility you could do me a quick diagram of something? I'm a bit of a visual person. Thanks for your ideas either way.

    Cheers,

    Frekster

  5. #5
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39
    Just to let you guys know where I'm at.... I found a temporary solution to this problem by simply adding five fields for Employee names to the results table. This should do for what the database is to be used for.

    Thanks guys for your help. I'd love to find a proper solution to this, but for some reason I have _less_ time on my hands now that I've finished year 12.

    Cheers,

    frekster

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh for the love of...

    *smacks forehead*

    Quick fixes are simply BEGGING for far more extensive work later on. Seriously, take the time to do this right.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's quite a few ways to do this one actually. I would create a table off your class details table for just employees attending a class. You can derive which businesses are attending through a relationship between employees and businesses.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi frekster
    I think you rdesign has some serious flaws, that only an interpretaion form the samll amount of info you have release

    If you don't mind my implementation would be something similar to
    presenters & class details as is
    business as is (but called customer)
    new table students - incorporates employees has a m:1 link with customer
    new table enrollment has a M;1 link with students and a m:1 link with class

    I find it easier to analys the links pictorially, a suggestion if you use the access relationships dialog then I would always suggest putting the 1 (parent) table to the left of the dependant table.

    results would be registered for a student not an employee group

    looking at you design
    it infers there can only be one result for an employee group - doesn't feel right
    It is arguable that a training establishment might want to treat the employee / student as the main child entity (because an employye may change employers but still be enrolled. There is a problem if you want to track employees of a compnay who have studeid in the past, which my proposed model doesn't answer

    In short without the full spec its impossible for a third party to deswing it, and lets face it it breaks the whole spirit or course/homework

  9. #9
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39

    Unhappy Thanks

    Hi once again!

    Just for the record healdem, this database is actually for my Dad's home business - as was my school one, but this one is not linked to schoolwork in any way.

    And Teddy, I quite agree with you. I have been assured that it is fine as is by my dad, but I believe I will modify it just to be on the safe side.
    After that I will be passing this database onto someone more knowlegable to polish it before being put into use.

    Thankyou for all you help, and I regret that I will not be able to finish it myself, as I simply do not yet know enough to implement the other features needed. I am hoping to gain these skills though Uni this year, but unfortunately I can't wait that long. So, thanks again to all.

Posting Permissions

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