Many-Many vs Object Relational

[Mikey]

Hi all. In the process of trying to figure this thing out, I've been
doing the old "stand around in the store and read as much as possible
before you look like a derelict" thing. This time, with the O'Reilly
"Managing and Using SQL" (I think that's it).

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key
you're interested in.

This is why I'm looking for books... I assume that there's some
MySQL-specific "flavor" to design that I'm unaware of.

--
"It's beyond my ken... and my Barbie, and all of my action figures."

[Rich R]

"Mikey" <ex***********************@earthlink.net> wrote in message
news:2004120718420752357%exceptionsTakeThisOutDude @earthlinknet...

Hi all. In the process of trying to figure this thing out, I've been
doing the old "stand around in the store and read as much as possible
before you look like a derelict" thing. This time, with the O'Reilly
"Managing and Using SQL" (I think that's it).

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key
you're interested in.

This is why I'm looking for books... I assume that there's some
MySQL-specific "flavor" to design that I'm unaware of.

--
"It's beyond my ken... and my Barbie, and all of my action figures."

Hi Mikey,

Even with MySQL there is only one way to implement a M:M relationship. It is
pretty tradtional:

A Project employs many Employees
An Employee works on many projects.

RDMS (including MySQL) can only do binary relationships. So you get the
following

Table Employee
*EmpID
EmpName

Table Project
*ProjectID
ProjectName

Table Assignments
*ProjectID
*EmpID

This is the only way you can do this correctly. And this is not
implementation specific. It works, and has worked since 1970. Many to Manys
resolve in a table where the combination of both keys are unique. And you
can even add attributes to this relation.

Best Regards,

Rich

[Bill Karwin]

Mikey wrote:

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key you're
interested in.

I'll probably get in trouble with O-R purists for saying this, but I
believe in practice, object-relational is an abstraction of well-known
relational concepts.

You could have an O-R model in which your primary table is a collection
of objects, and each of these objects has an attribute which is a
reference to another collection, the set of objects in the second table
related to this primary object. In that way, a many-to-many
relationship can be abstracted and represented in an object-oriented way.

So for example, you have a table of Employees, and each employee object
has a method getProjects(), which returns a subset of objects from the
Projects table. Likewise, each Project object has a method
getEmployees(), which returns a subset of objects from the Employees
table.

The physical storage behind this abstraction is likely identical between
O-R and plain relational; there's a join entity that references both the
Employees table and the Projects table. The O-R mapping just allows you
to fetch the matching rows without knowing having to know about the join
entity in the middle.

Regards,
Bill K.

[Mikey]

On 2004-12-07 21:15:42 -0500, Bill Karwin <bi**@karwin.com> said:

Mikey wrote:

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key
you're interested in.

I'll probably get in trouble with O-R purists for saying this, but I
believe in practice, object-relational is an abstraction of well-known
relational concepts.

You could have an O-R model in which your primary table is a collection
of objects, and each of these objects has an attribute which is a
reference to another collection, the set of objects in the second table
related to this primary object. In that way, a many-to-many
relationship can be abstracted and represented in an object-oriented
way.

So for example, you have a table of Employees, and each employee object
has a method getProjects(), which returns a subset of objects from the
Projects table. Likewise, each Project object has a method
getEmployees(), which returns a subset of objects from the Employees
table.

The physical storage behind this abstraction is likely identical
between O-R and plain relational; there's a join entity that references
both the Employees table and the Projects table. The O-R mapping just
allows you to fetch the matching rows without knowing having to know
about the join entity in the middle.

Thanks, guys. Super, another mystery solved.
--
"It's beyond my ken... and my Barbie, and all of my action figures."