Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: Access Relationship Problem

    Hi everyone,
    I am designing a database in Access and am completely stuck in one area of it. My tables look like so: image link, which I need to relate with one-to-many relationships. As you can see I have a many-to-many relationship which needs to be fixed.

    The problem arises with the field named 'EmployeeIDOfManager' which holds the EmployeeID of the manager of that employee. I need to retrieve this value but I do not want to make a self-join, it must be through the use of one-to-many relationships using tables.

    Could anyone provide me with a solution to this problem I have been stuck with for weeks? I really need help to make this database work.

    Thx

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I do not want to make a self-join, it must be through the use of one-to-many relationships using tables
    Whose limitation is this? Yours or your lecturer's?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by StarTrekker
    Whose limitation is this? Yours or your lecturer's?
    My teacher told me that we should follow Boyce-Codd's normal form
    I understand the normal form, but I'm having difficulty following it and overcoming the problem I showed you. Does anyone know how to solve it?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Why do you need to join 2 fields in the Employee table to 1 field in the Manager table? I might re-think this type of relationship as you'll most likely get poor performance from queries and some nasty looking SQL statements to return a few fields of data. You may also have some difficulties entering data via the forms with this type of relationship unless the forms are designed with some "unnecessary" coding behind the scenes unless the relationship is changed (I usually avoid linking any 2 fields between the same 2 tables for ANY relationship as this results in very poor query performance (especially when dealing with any mass number of records.) The rule of thumb I use is 1 field links to 1 table (that doesn't mean you can't have 2 or 3 fields in 1 table with each field linking to a different table - that's ok. Just don't have all those 2 or 3 fields linking to only 1 other table - it causes problems for data entry forms.)

    To construct a 1 to many relationship between 2 tables, first join the field from the first table to the field in the second table. Then double-click on the line joining the 2 tables and select the appropriate join method (ie. all the records from table X and only those records from table Y which match.) You may also want to read up on joins to see how they're done correctly. The Northwind database which ships with MSAccess has some nice relationships to show you how joins work if you look at the relationships in that mdb file (it probably has the exact relationship type you're looking for to meet the needs of your assignment.) I believe you can download this via Microsoft's website also but I'm not sure. Otherwise I'd suggest browsing the code bank (or googling) and then download/look at how relationships are setup in those examples.

    Good luck. It takes a while for some to get the hang of relationships so the more examples you can look at and then test yourself the better. Sometimes there's also different ways to accomplish the same type of relationship and it is done that way so certain coding can be put into place (for some reason or another). You'd also probably get some varied results if you asked everyone here to come up with a relationship schematic for you. A lot also depends on how the whole process is supposed to work which is something you just have to think through and depends on the specific instructions/criteria you were given.

    You could also try to associate the relationships with something that's easier to understand (ie. hooking up a stereo system to a TV where you have certain cables which go to certain devices such as VCR or DVD or subwoofer, etc....) That might not be the best association to work for you but try to think of other things you could relate to each other in a 1 to many way (ie. 1 President (with a special ID number) to many ceo's (who all have separate ID numbers) to many managers (again with separate ID numbers) to many employees, and so on... And to carry it further, the President may have specfic traits (ie. height, weight, name) as well as the ceo's, managers, and employees.)

    Once you understand how it works, the rest is pretty easy. But it is easy to forget so don't make it an assignment where you only devote a little time to it here and there. You'll need to really sit down and think it through for a while and then try out different methods. If you have time, I'd design a few forms to see if all the data can be entered as it's supposed to. (if you have problems adding data via a simple form or two, go back to the drawing board and revisit how your relationships are setup.)

    And always keep in mind, it's the user's you're going to be trying to please versus the instructor in the real world. Users tend to have only so much patience before they become frustrated if something always has problems (especially if it's something they need to use daily.)

    And lastly, ID type fields (usually numbers) are important as they are the mechanism whereby you use a Primary Key ID field (in the main table) to join to that same number value in the joining table designated as the Foreign Key field (keep the ID field name the same in both tables to make thing easier but don't always just ID as the name in every table! - ex: ManagerID (in both main and joining table), EmployeeID (in both main and joining table), etc..) MSAccess will then nicely join these 2 fields together for you when you bring both tables into a query design.
    Last edited by pkstormy; 05-22-09 at 01:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2009
    Posts
    8
    I had a look at the relationship diagram of the Northwind database, but none of the fields in the tables required data from the same table.

    I tried again for hours trying to think of how to overcome this problem, but I keep coming up with the same design everytime, but the design is flawed

    I realised that I have a one-to-one relationship linking from Branch to Manager, which would need to be fixed, but even that I cannot resolve. This database stuff is making me want to cry. I even tried searching Google to find an example of something in a situation like mine, but couldn't find anything.

    Why do you need to join 2 fields in the Employee table to 1 field in the Manager table?
    The EmployeeID field in the Manager table is a foreign key of the EmployeeID in the Employee table. Then the EmployeeIDOfManager field in the Manager table retrieves the data from the EmployeeID field in the Manager table, but obviously this doesn't work...
    Last edited by compgirlxx; 05-22-09 at 10:29.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    compgirlxx, let me start with my standard disclaimer. Design is not my strong subject. However,

    What purpose does EmployeeIDOfManager in Employee table get you?

    If it is to identify the Manger of the Employee, you already have that information in the Manager table. You could just Join Employee table EmployeeId to Manager table ManageID to get this information. This would remove what looks like your problem relationship.

    Employee table would list only Employee information (Managers and non-Managers).

    Manager table would list the Managers and the Employees they manage directly. (This could be expanded to include Managers who Manage other managers (Region Manager to District Manager and District Manager to Branch Manager) with no change in design).

    Branch table would list the Branch and who the Manager is.

    Think of it this way. Do you have a BranchIDOfEmployee in the Employee table? It makes as much (or little) sense as ManagerIDOfEmployee.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unless an employee can have more than one direct manager (I have seen it....), you don't need the Manager table. A relationship from EmployeeIDOfManager and EmployeeID in the Employee table does not violate BCNF.
    Apart from anything else, the relationship from Employee to Manager would be one-to-one (the PK would be EmployeeID not ManagerID). This immediately tells you it is unnecessary.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by Stealth_DBA
    compgirlxx, let me start with my standard disclaimer. Design is not my strong subject. However,

    What purpose does EmployeeIDOfManager in Employee table get you?

    If it is to identify the Manger of the Employee, you already have that information in the Manager table. You could just Join Employee table EmployeeId to Manager table ManageID to get this information. This would remove what looks like your problem relationship.

    Employee table would list only Employee information (Managers and non-Managers).

    Manager table would list the Managers and the Employees they manage directly. (This could be expanded to include Managers who Manage other managers (Region Manager to District Manager and District Manager to Branch Manager) with no change in design).

    Branch table would list the Branch and who the Manager is.

    Think of it this way. Do you have a BranchIDOfEmployee in the Employee table? It makes as much (or little) sense as ManagerIDOfEmployee.
    EmployeeIDOfManager just holds the employee's manager's EmployeeID.

    Are you saying that this would work?

    Quote Originally Posted by pootle flump
    Unless an employee can have more than one direct manager (I have seen it....), you don't need the Manager table. A relationship from EmployeeIDOfManager and EmployeeID in the Employee table does not violate BCNF.
    Apart from anything else, the relationship from Employee to Manager would be one-to-one (the PK would be EmployeeID not ManagerID). This immediately tells you it is unnecessary.
    Today 09:56
    But 3rd normal form states that all fields should be independent, but if I create a self-join in the table then the EmployeeIDOfManager field will be dependent on the EmployeeID.

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    compgirlxx, not quite. What I suggested was leave the Manager table as it was

    Manager table
    ManagerID PK
    EmployeeID

    Remove EmployeeIDOfManager from the Employee table.

    Alter Manager table with:
    FK1 between ManagerId and EmployeeID in Employee table.
    FK2 between EmployeeId and EmployeeID in Employee table.

    (You don't want to be able to enter either a ManagerID or an EmployeeID in the Manager table unless they exist on the Employee table.)

    But I would give pottle flump's suggestion serious consideration (as long as the 'an employee can only have one manager' business rule applies). I don't believe it violates any 3NF rules (but I am not an expert) as it is an element of the employee (an Employee can have a Start date, street address phone number, manager, etc.).

    Actually if there was data in the table and you did a

    SELECT EmployeeIDOfManager, EmployeeID from Employee
    and
    SELECT ManagerID, EmployeeID from Manager

    The output would be identical (assuming, again, one manager per employee).

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by compgirlxx
    But 3rd normal form states that all fields should be independent, but if I create a self-join in the table then the EmployeeIDOfManager field will be dependent on the EmployeeID.
    I am glad you are paying attention to Third Normal Form. However, that is not quite right. This states that there can be no dependencies between non key attributes. Emloyeeid is a key attribute. An employees manager is functionally dependent on the employee, it is not transitively dependent on anything else - thus this is fully normalised.

    The only possible risk is how your course defines 1NF. If it defines it like Date does, then you cannot have any nullable attributes. In that case, my suggestion would not actually be in first normal form (unless you had people without managers managing themselves or some other "solution" to ensure there was a value in every managerID column). I would be surprised if the definition you are working to requires a value in every column, however you can check this out yourself.

    EDIT - cleared up some typos, and made something a bit more clear
    Last edited by pootle flump; 05-22-09 at 16:39.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Stealth_DBA - look at your solution again - I take it managers in your organisation are not allowed to manage more than one person
    The PK should be on EmployeeID, assuming an employee can only have one manager. That then changes it into a one to one relationship which renders a second table redundant.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Oops . You are correct pootle flump. I was concentrating too much on either getting rid of EmployeeIDOfManager (my suggestion) or getting rid of the Manager table (your suggestion).

    And as long as you brought it up...

    If EmployeeIDOfManager is left in Employee table, there is that situation of 'Who Manages the Manager?'. As you mentioned, it has to either be NULL or the same as the EmployeeID (Manages him/her self) at some point.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Stealth_DBA
    If EmployeeIDOfManager is left in Employee table, there is that situation of 'Who Manages the Manager?'. As you mentioned, it has to either be NULL or the same as the EmployeeID (Manages him/her self) at some point.
    Correct. If you are relational purist, or scared of NULLs, it will be a problem. If you worry about more important things, it ain't
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    May 2009
    Posts
    8
    So should I get rid of the idea of creating a manager table and instead just make the EmployeeIDOfManager field have a self-join to the EmployeeID of the Employee table? I re-read about 3NF and it states that it should "relate to the key, the whole key, and nothing but the key."

    What I don't understand about a self join is how I would make the table display the data in the field, and when it will display it. I know SQL.

  15. #15
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    compgirlxx, Having just the Manager of the Employee (EmployeeIDOfManager) is okay as that can be considered an attribute of the Employee. But if the Manager's Phone number (or anything else that relates to the manager) is included, that would be a violation as it has nothing to do with the Employee.

Posting Permissions

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