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

What access does is correct for access.

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.

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

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

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 ...

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

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

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

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

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

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,

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