I cannot get my head round how to create a database where two of the tables
require a many-to-many relationship.
I posted on another group and was advised to make a third joining table, but
I don't really understand how this works.
Can anyone help?
Basically I have a table of Individuals, and a table of Businesses. A
business can be related to many individuals, and an individual to many
businesses.
ThanksKeith
Did you read the link which David posted here?
"Keith" <@..> wrote in message news:esRuNtWFEHA.2976@.TK2MSFTNGP10.phx.gbl...
> I cannot get my head round how to create a database where two of the
tables
> require a many-to-many relationship.
> I posted on another group and was advised to make a third joining table,
but
> I don't really understand how this works.
> Can anyone help?
> Basically I have a table of Individuals, and a table of Businesses. A
> business can be related to many individuals, and an individual to many
> businesses.
> Thanks
>|||Keith wrote:
> I cannot get my head round how to create a database where two of the
> tables require a many-to-many relationship.
> I posted on another group and was advised to make a third joining
> table, but I don't really understand how this works.
> Can anyone help?
> Basically I have a table of Individuals, and a table of Businesses. A
> business can be related to many individuals, and an individual to many
> businesses.
Yes, a junction table is needed. From BOL - it is a table that establishes a
relationship between other tables. The junction table contains foreign keys
referencing the tables that form the relationship. For example, an
OrderParts junction table can show what parts shipped with each order by
having foreign keys to an Orders table and a Parts table. In your case:
Business
--
PK ID_Business int
(...)
Individuals
--
PK ID_Individual int
(...)
BusinessIndividuals
--
PK ID_Business int
PK ID_Individual int
Business.ID_Business -- BusinessIndividuals.ID_Business
Individuals.ID_Individual -- BusinessIndividuals.ID_Individual
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Keith
Many to Many relationships takes a bit of effort to get
your head around.
Anyways here is my take on it.
You have a table called student, and a table called class.
A Student can take may classes, and a class can have many
students.
So in the Student Table you would have something like
Keith SQL Beginner Course
Keith SQL Intermediate Course
Julie SQL Beginner Course
Matt ASP Beginner Course
However in the Class table you would have
SQL Beginner Course Keith
SQL Beginner Course Julie
SQL Intermediate Course Keith
ASP Beginner Course Matt
Now this is bad because you are copying data (third normal
form), in the Student table it would be better to have
each student entered once, and in classes, its better to
enter a classes only once.
Here is where the third table comes in.
So out Student Table we would have
Keith
Julie
Matt
and in the Classes table we would have
SQL Beginner Course
SQL Intermediate Course
ASP Beginner Course
Now you would have a link table
Keith SQL Beginner Course
Keith SQL Intermediate Course
Julie SQL Beginner Course
Matt ASP Beginner Course
Anyway the example is a very simple one. There are tons of
examples out on the net.
Enjoy
J
>--Original Message--
>Keith
>Did you read the link which David posted here?
>"Keith" <@..> wrote in message
news:esRuNtWFEHA.2976@.TK2MSFTNGP10.phx.gbl...
where two of the
>tables
third joining table,
>but
Businesses. A
individual to many
>
>.
>|||Thanks everyone
I have just got my head round this now (or at least have got it working -
still not sure I understand it :-))
"Keith" <@..> wrote in message news:esRuNtWFEHA.2976@.TK2MSFTNGP10.phx.gbl...
> I cannot get my head round how to create a database where two of the
tables
> require a many-to-many relationship.
> I posted on another group and was advised to make a third joining table,
but
> I don't really understand how this works.
> Can anyone help?
> Basically I have a table of Individuals, and a table of Businesses. A
> business can be related to many individuals, and an individual to many
> businesses.
> Thanks
>
Monday, March 26, 2012
Many-to-Many Relationship?
Labels:
create,
database,
group,
head,
many-to-many,
microsoft,
mysql,
oracle,
relationship,
round,
server,
sql,
tablesrequire
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment