Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    I need help with relanshionships

    I am new in all these stuff.


    I have create 7 different tables.
    Office table
    Machine table
    Physicist table
    Physicians table
    Engineers table
    Licenses table
    Officers table

    I need to answer these kind of questions:

    Who are the physicists at X office?
    Who are the engineers at X office?
    When does the license due at X office?


    How can I do that?
    Am I ok with the tables?
    How can I create the relationships?

    Thanks and I hope soembody can help me.
    Leandro.

  2. #2
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    The tables are ok, you can create additional tables to store the relationship, for example, create a table office_physicist, the main two fields would be office_id and physicist_id.

    To make the whole structure simpler, you may want to use a table "people" to replace those staff tables, specify their role with a field, for example:
    role_id 1 -> Physicist
    role_id 2 -> Physicians
    ....

    It's easier to maintain this way.

  3. #3
    Join Date
    Nov 2005
    Posts
    7
    That is a good idea.
    But if a person plays two different roles at the same time, how can I do that?
    should I repeat the row with the same name, telephone but a different role?
    Ej: John is a physicist in X location, but is also an officer in Y location.
    How can do that?

    The database is very simple (I think so). I have 70 different offices, each has a license, and in each license I have a list of physicist, physicians, engineers and officers. Each of these guys can be listed under different licenses. So sometimes I need to know who are the guys that are listed under X license or office.

    Am I clear?

    Somebody can help me with the design?

    Thanks in advance.

  4. #4
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Then change it a little bit:
    Make the office and people tables simple, store all relationships in a relationship table.
    The basic roles like this:

    [office]
    id (PK)
    name
    ...

    [people]
    id (PK)
    name
    ....

    [role]
    id (PK)
    name
    ...


    [relationship]
    office_id
    people_id
    role_id
    PK is (office_id, people_id, role_id)

    Try some queries and evaluate if it is easy and efficient.

  5. #5
    Join Date
    Nov 2005
    Posts
    7
    OK. I understood the logic of the 3 tables. The thing is, that I do not understand what data should I put in the relationship table. Do I have to fill that table? The other 3 are filled with the data.
    Thanks very much for your help.

  6. #6
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Yes, you should fill that table, otherwise who will do it for you?

    One common situation:
    There is a new engineer, you need to insert a new record into the table "people", then insert a record into the table "relationship". So the record should be:
    people_id: this value is from the table "people".
    role_id: he is an engineer, in the role table, you have all roles with an ID.
    office_id: you know which office he is assigned.

  7. #7
    Join Date
    Nov 2005
    Posts
    7
    and what happened when that engineer is assigned wiht an area with many offices? Do I have to fill as many records in the relationship table as offices is he assigned? I ask that because that is very common in my department.
    Thank you so much.

  8. #8
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Yes, insert one record for each relationship, that's why the primary key is the combination of office_id, people_id and role_id.

  9. #9
    Join Date
    Nov 2005
    Posts
    7
    thank you so very much, I will try right now.

  10. #10
    Join Date
    Nov 2005
    Posts
    7
    One question:

    I have 5 different roles, 70 different offices and 150 different people. Do I have to make all the posible combinations and put that data in the relationship table?

    Ex: Relationship Table

    Office ID/ Personal ID / Role ID
    1 / 45 / 2
    1 / 34 / 1
    1 / 36 / 1


    etc....

    Is that correct? If so, I have a lot of data to put in.....

  11. #11
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Yes, if the database doesn't have all the relationships, how could you read all relationships? Any database programs work this way.

  12. #12
    Join Date
    Nov 2005
    Posts
    7
    So bassically I have to write all the information that then I will need to get.
    It is like I do all the job and then the only thing I can do is to get the information in different ways. Is that correct? I though that one I put in the tables, the names of the persons , the facilities and the roles, then I could ask the database who works for X facillity, etc. Instead of that, I have to tell the database: For X facility, we have ABCD people, who are all engineers, and GHK who are physicist. And then I can ask who are the physicist at X facility?
    Is that correct? Sorry to bother you but I am getting a little confuse.
    thanks a lot.

  13. #13
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Yes, that's right. You can make a form so that anyone can input these data. Normally developers don't have to do it.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums