Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Double Relationship Between Two Tables

    Hello, would greatly appreciate any help with this question.

    How do you implement a relationship between two tables where the primary key on one table is referenced twice as foriegn keys in a second table?

    An example - forgive any breach of conventions

    tblROLES
    RoleID, EntityID, RoleType
    01, LizHurley, Model
    02, m51t, Student

    tblASSOCIATIONS
    PrimaryAssociate, SecondaryAssociate, Association
    01, 02, Stalker

    The RoleID attribute is used for both the PrimaryAssociate and SecondaryAssociate fields in the ASSOCIATIONS table. An association is a relationship between two people performing a role. Therefore I think I need to reference the ROLES table twice to describe an association.

    How could I logically model this? I tried creating a double relationship in MS Access and it produced a duplicate ROLES table so that there was two single relationships from each ROLES table.

    Thanks in advance

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    What access does is correct for access.

  3. #3
    Join Date
    Mar 2005
    Posts
    5
    What about a solution independent of database?

    I was using Access to model a basic ERD only - to print from the relationship view. Implementation will be in MySQL.

    I was unsure that a double relationship could exist, so I do not know how to solve for my situation. I have not seen an equivalent example to what I am trying to model so I am stuck as how to model it using correct ERD conventions.

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    double relationships can exist, e.g.

    take a
    HumanResourceTable
    id_person
    name
    department


    ApplicationTable
    id_application
    id_person_technical
    id_person_accountant

    where both id_person~ reference HumanResourceTable

    I did this in the past for sybase and oracle

  5. #5
    Join Date
    Mar 2005
    Posts
    5
    Excellent. Thank you osy45.

    I couldn't see why such a relationship could not exist, but did not feel confident with the results that Access was giving me - duplicating the ROLES table so that there was two single relationships between the Associate attributes and RoleID.

    ie

    Original ROLES table
    ROLES.RoleID <-> ASSOCIATES.PrimaryAssociate

    Duplicated ROLES table
    ROLES(1).RoleID <-> ASSOCIATES.SecondaryAssociate

  6. #6
    Join Date
    Nov 2002
    Posts
    833
    as your target system should be mysql ...

    but in the past I had troubles with access, too, when applying generated datamodels from powerdesigner to access it fails while only switching to sybase or oracle it works fine

    to me access is not a real rdbms but other may have another opinion about it ...

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    I strongly agree with you that MS Access is not a real relational database. With its emphasis on physical files, Access confuses the physical and logical aspects of databases.

    For that matter, using the same argument, MYSQL is also not a real database either. When you can specify the format you want your table data to be stored in, you are no longer talking about a relational database. Then, too, is their insistence that foreign keys (or referential integrity) are not required. This makes it abundantly clear that the designers of MYSQL wouldn't recognize relational database theory if somebody hit them on the head with it.

    Ravi
    Last edited by rajiravi; 03-09-05 at 07:12. Reason: grammar to be corrected

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    somebody has been visiting dbdebumph.com too often

    access is a real relational database

    mysql is a real relational database

    please stop being so elitist

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Yes, I visit www.dbdebunk.com every Friday afternoon when they have a new set of articles. And I do learn a lot from that site.

    Some of the moderators of this site seem not only to visit the site, but also contribute to it. So, I guess I am in good company there.

    I haven't bought anything from that site yet, but plan to do so to support them and to learn from them.

    Sites like Fabian Pascal's dbdebunk are sorely needed.

    Ravi

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm happy for you, and i'm glad that you can stomach the arrogance and vitriol that permeates that site

    however, please do not let it affect your grasp of reality

    according to everybody else in the world, access and mysql are certainly real enough, and they are indisputably relational databases

    for someone to denigrate these two excellent products, used by millions of people around the world, based only on some perceived shortcomings as measured against theoretical and idealistic standards, only reveals how far removed from reality the speaker is

    according to those theoretical and idealistic standards, there is no product in existence which could be called a "real relational database"

    yes, i've heard of Tutorial D, but i don't think it has displaced too many access or mysql installations -- let's revisit this subject when it starts to get picked up by some fortune 500 (or even fortune 5000) companies

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    It is interesting that people see Fabian Pascal's postings as arrogant.

    I, for one, do not see them as such.

    He states his facts and asks others to prove their statements when they disagree with him. Most often they can not rebut him on the technical points of his argument.

    Then the next step is to say that his ideas will not work in "the real world." Again this statement is made without any proof. As I've mentioned here before, I've implemented a web application built on a 3rd normal form database with real time access to it. No performance issues at all! The users' comment was that it was the "cleanest" application that they had seen. (It also had zero bugs during its lifetime!) Wouldn't have been as clean without it being a 3rd NF database. Also, this was before I had visited Pascal's site and heard about the evils of nulls. Strangely enough, my database design contained very few null columns. Most tables had only required fields. A similar, but simpler project, that denormalized the database for "performance" turned out to be much slower. (An attempt to convert it to a Java Based application was a total failure.)

    My real world experience strongly supports Fabian Pascal's views. Hence I am very sympathetic to his ideas.

    Is Mr. Pascal bitter? Yes, he is. So would anyone be who is a master of his subject yet sees others denigrating him merely because they do not understand what he is saying. His comments about the sorry state of affairs in the software world is very accurate.

    Vitriol from Fabian Pascal? What a strange idea! There is as much vitriol from others towards him, if not more.

    Regarding Access and MySQL, many people consider a spreadsheet as a database and use it as such. That does not make a spreadsheet a relational database. Access and MySQL are databases, but not relational by any stretch of my imagination. I would not use either if given a choice. I like PostgreSQL though.

    Ravi

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajiravi
    Access and MySQL are databases, but not relational by any stretch of my imagination.
    i guess you need to stretch your imagination a wee bit more, then

    would you like me to show you specific examples on pascal's site of colossal arrogance, vitriol, elitism, and rudeness?

    sorry, i won't, because it would mean i'd have to go visit that site again, and while you can stomach him, i cannot

    however, i do have two items bookmarked:

    "There is absolutely nothing that will persuade me to consider anything that has to do with Celko. 100% waste of time." (http://www.dbdebunk.com/page/page/1490837.htm)

    "Stay away from Celko!!!!" (http://www.dbdebunk.com/page/page/857309.htm)

    arrogant, rude, and condescending

    Q friggin E D
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Rudy,

    I agree with you that Fabian Pascal could have used gentler phrasing. In other words, he was highly opinionated. But rude? No. Arrogant? Yes. Condescending? Maybe.

    I can tolerate arrogance from those who have something to be arrogant about. Like JohnMcEnroe, Michael Jordan, etc. (Not that they were arrogant.) But the truly great people are those who are acknowledged masters, and humble at the same time. Like Chris Date.

    In this particular case, I disagree with Fabian Pascal. There are quite a few interesting things that Joe Celko says. Does he make mistakes from time to time? Yes. But then, I do not have to agree with Celko or Pascal on every single topic.

    There is a saying in India to the effect that: The Lotus is a beautiful flower despite growing in very dirty water. Ignore the place that it grows in and appreciate its beauty.

    I apply that to the debunk site and ignore the arrogance that sometimes comes across. My aim is to learn new things, and that site does do that for me.

    Ravi

Posting Permissions

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