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
RoleID, EntityID, RoleType
01, LizHurley, Model
02, m51t, Student
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.
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.
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.
Original ROLES table
ROLES.RoleID <-> ASSOCIATES.PrimaryAssociate
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.
Last edited by rajiravi; 03-09-05 at 06:12.
Reason: grammar to be corrected
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.
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.