[OT?] A database design question: many-to-many-to-many...

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

[OT?] A database design question: many-to-many-to-many...

R. Stephen Gracey
I wonder if someone here can help me out. I'm rewriting a content database
in which different kinds of content are interrelated--or rather, clustered
together. For instance, it's easy to say that a calendar event has multiple
documents associated with it, such as a flyer, a form, and whatever, but how
do you say that this bunch of documents are related to one another?

I can manage it when two individual things are related to one another, but
what about three or four? How do you store that information?

I'm trying to learn MySQL now, having learned FMPro, and I couldn't figure
out how to do it in FMPro.

Thanks,
Stephen

R. Stephen Gracey
Bishop's Assistant for Information Strategy
Episcopal Diocese of Ohio

[hidden email]

http://www.dohio.org

Trinity Commons
2230 Euclid Avenue
Cleveland, OH 44115

Direct:   (216) 774-0454

Diocesan: (216) 771-4815
In Ohio:  (800) 551-4815
 



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Douglas Burchard
On May 23, 2005, at 12:18 PM, R. Stephen Gracey wrote:

> I wonder if someone here can help me out. I'm rewriting a content  
> database
> in which different kinds of content are interrelated--or rather,  
> clustered
> together. For instance, it's easy to say that a calendar event has  
> multiple
> documents associated with it, such as a flyer, a form, and  
> whatever, but how
> do you say that this bunch of documents are related to one another?
>
> I can manage it when two individual things are related to one  
> another, but
> what about three or four? How do you store that information?

Typically developers will implement many to many relationships with  
join tables:

     Books -< JoinTable >- Authors

So to describe this further, each record in a join table defines a  
relationship between one record in each of the associated tables:

     Authors
     -----------------
     ID
     Name
     Address


     JoinTable
     -----------------
     AuthorID
     BooksID


     Books
     -----------------
     ID
     Name
     ISBN

If you need a many to many relationship within the same table, the  
implementation can be just about the same accept with only one  
associated table:

     FormPostings >= Thread(JoinTable)


     FormPostings
     -----------------
     ID
     Subject
     Body


     Thread(JoinTable)
     -----------------
     PostingOneID
     PostingTwoID


Does this describe what you're looking for?


--
Douglas Burchard, President
DouglasBurchard.com, Web Applications
15024 NE 66th Street
Redmond, WA  98052, USA

direct: (206) 227-8161
[hidden email]
http://www.douglasburchard.com/


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

R. Stephen Gracey
In reply to this post by R. Stephen Gracey
So, keeping the book example, is there anyway--short of defining a specific
common topic--that you can say, "These three books are related to each
other" in a clump, regardless of author or anything else?

Stephen

R. Stephen Gracey
Bishop's Assistant for Information Strategy
Episcopal Diocese of Ohio

[hidden email]

http://www.dohio.org

Trinity Commons
2230 Euclid Avenue
Cleveland, OH 44115

Direct:   (216) 774-0454

Diocesan: (216) 771-4815
In Ohio:  (800) 551-4815
 

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf
Of Douglas Burchard
Sent: Monday, May 23, 2005 3:51 PM
To: [hidden email]
Subject: Re: [OT?] A database design question: many-to-many-to-many...


On May 23, 2005, at 12:18 PM, R. Stephen Gracey wrote:

> I wonder if someone here can help me out. I'm rewriting a content
> database
> in which different kinds of content are interrelated--or rather,  
> clustered
> together. For instance, it's easy to say that a calendar event has  
> multiple
> documents associated with it, such as a flyer, a form, and  
> whatever, but how
> do you say that this bunch of documents are related to one another?
>
> I can manage it when two individual things are related to one
> another, but
> what about three or four? How do you store that information?

Typically developers will implement many to many relationships with  
join tables:

     Books -< JoinTable >- Authors

So to describe this further, each record in a join table defines a  
relationship between one record in each of the associated tables:

     Authors
     -----------------
     ID
     Name
     Address


     JoinTable
     -----------------
     AuthorID
     BooksID


     Books
     -----------------
     ID
     Name
     ISBN

If you need a many to many relationship within the same table, the  
implementation can be just about the same accept with only one  
associated table:

     FormPostings >= Thread(JoinTable)


     FormPostings
     -----------------
     ID
     Subject
     Body


     Thread(JoinTable)
     -----------------
     PostingOneID
     PostingTwoID


Does this describe what you're looking for?


--
Douglas Burchard, President
DouglasBurchard.com, Web Applications
15024 NE 66th Street
Redmond, WA  98052, USA

direct: (206) 227-8161
[hidden email]
http://www.douglasburchard.com/


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Doug Gentry
In reply to this post by R. Stephen Gracey
Sorry to be jumping into this thread late - but this is similar to
something I had to solve during the past month or so. Maybe this will
help (or maybe not...)

I have a site that lists films. So there is a table for films, with all
of the basic information on each film.

Then I have a separate table to list related films. There are three
fields:

a unique ID - not really used, but just there because...
a host film id
a related film id

On the host film page it will list other films related to it.  The
related film id is one of those related films.  The host film might
have 3 or 5 or 10 records in the related table, each listing a
different related film.

So there is a page that allows the filmmaker to add related films,
based on whatever criteria they like. On the host film page there is an
inline that searches the related table for the host film id, and then
lists all of the related films. Now actually I use an SQL query to do
this so that for every related film id I can also query the films table
and pull in the title of the film, its web address, etc.

...Doug

On May 25, 2005, at 11:26 AM, R. Stephen Gracey wrote:

> So, keeping the book example, is there anyway--short of defining a
> specific
> common topic--that you can say, "These three books are related to each
> other" in a clump, regardless of author or anything else?
>
> Stephen
>
> R. Stephen Gracey
> Bishop's Assistant for Information Strategy
> Episcopal Diocese of Ohio
>

---
Doug Gentry
Dynapolis & Southern Oregon University
p:  541-261-8501 / Toll Free: 888-490-0644
[hidden email]


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

R. Stephen Gracey
In reply to this post by R. Stephen Gracey
Thanks, Doug.

And so, how do those fields get populated? If there are three related films,
for instance, then what does the table look like? How would you search it?

So, let's say you have film1 that is related in a cluster to film2 and
film3.

1 film1 film2
2 film1 film3

Is that enough? or do you have to do this...

1 film1 film2
2 film1 film3
3 film2 film1
4 film2 film3
5 film3 film1
6 film3 film2

?

Stephen



R. Stephen Gracey
Bishop's Assistant for Information Strategy
Episcopal Diocese of Ohio

[hidden email]

http://www.dohio.org

Trinity Commons
2230 Euclid Avenue
Cleveland, OH 44115

Direct:   (216) 774-0454

Diocesan: (216) 771-4815
In Ohio:  (800) 551-4815
 

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf
Of Doug Gentry
Sent: Wednesday, May 25, 2005 2:41 PM
To: [hidden email]
Subject: Re: [OT?] A database design question: many-to-many-to-many...


Sorry to be jumping into this thread late - but this is similar to
something I had to solve during the past month or so. Maybe this will
help (or maybe not...)

I have a site that lists films. So there is a table for films, with all
of the basic information on each film.

Then I have a separate table to list related films. There are three
fields:

a unique ID - not really used, but just there because...
a host film id
a related film id

On the host film page it will list other films related to it.  The
related film id is one of those related films.  The host film might
have 3 or 5 or 10 records in the related table, each listing a
different related film.

So there is a page that allows the filmmaker to add related films,
based on whatever criteria they like. On the host film page there is an
inline that searches the related table for the host film id, and then
lists all of the related films. Now actually I use an SQL query to do
this so that for every related film id I can also query the films table
and pull in the title of the film, its web address, etc.

...Doug

On May 25, 2005, at 11:26 AM, R. Stephen Gracey wrote:

> So, keeping the book example, is there anyway--short of defining a
> specific
> common topic--that you can say, "These three books are related to each
> other" in a clump, regardless of author or anything else?
>
> Stephen
>
> R. Stephen Gracey
> Bishop's Assistant for Information Strategy
> Episcopal Diocese of Ohio
>

---
Doug Gentry
Dynapolis & Southern Oregon University
p:  541-261-8501 / Toll Free: 888-490-0644
[hidden email]


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Paul Melia
In reply to this post by R. Stephen Gracey
This is how I would approach it using your film example...

Table: Directors

1 steven spielberg
2 george lucas
3 stanley kubrick

Table: Studios

1 LucasFilm
2 Universal
3 Fox
4 United Artist

Table: Movies

1 Raiders of the Lost Ark
2 Star Wars
3 2001

To associate the three tables to each other, I would add the  
following fields to the Movies table..

DirectorID
StudioID

so the entry for 2001 looks like this

ID           Name       DirectorID        StudioID
1            2001          3                        4

but for Raiders it would look like this...

ID           Name                                        
DirectorID        StudioID
1             Raiders of the Lost Ark          
1                       1,2

and Star Wars would be...

ID           Name                 DirectorID       StudioID
1             Star Wars          2                       1,3

You then can search against the fields looking for what ever you want  
(show me all movies produced by Lucasfilm) and get back those films.  
The code I use for for this with MySQL and Lasso 8 is...

[var:'studioID'=(field:'studioID')]

                 [var:'refarray'=(array)]
                         [var:'refarray'=(var:'studiorid')->(split:',')]
                             [iterate: $refarray, (var:'temp')]
                                 [Inline:     -search,
                                             -Database='movies',
                                             -Table='studios',
                                             -username='username',
                                             -password='username',
                                             -op='eq',
                                             'id'=(var:'temp'),
                                             -Returnfield='id',
                                             -Returnfield='name']

                                         [field:'name'] <br>
                                     [/if]
                                 [/inline]
                             [/iterate]


This is just the display code. I can post the edit/add code if this  
the solution you where looking for.

HTH,
Paul

On May 25, 2005, at 3:10 PM, R. Stephen Gracey wrote:

> Thanks, Doug.
>
> And so, how do those fields get populated? If there are three  
> related films,
> for instance, then what does the table look like? How would you  
> search it?
>
> So, let's say you have film1 that is related in a cluster to film2 and
> film3.
>
> 1    film1        film2
> 2    film1        film3
>
> Is that enough? or do you have to do this...
>
> 1    film1        film2
> 2    film1        film3
> 3    film2        film1
> 4    film2        film3
> 5    film3        film1
> 6    film3        film2
>
> ?
>
> Stephen
>
>
>
> R. Stephen Gracey
> Bishop's Assistant for Information Strategy
> Episcopal Diocese of Ohio
>
> [hidden email]
>
> http://www.dohio.org
>
> Trinity Commons
> 2230 Euclid Avenue
> Cleveland, OH 44115
>
> Direct:   (216) 774-0454
>
> Diocesan: (216) 771-4815
> In Ohio:  (800) 551-4815
>
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]  
> On Behalf
> Of Doug Gentry
> Sent: Wednesday, May 25, 2005 2:41 PM
> To: [hidden email]
> Subject: Re: [OT?] A database design question: many-to-many-to-many...
>
>
> Sorry to be jumping into this thread late - but this is similar to
> something I had to solve during the past month or so. Maybe this will
> help (or maybe not...)
>
> I have a site that lists films. So there is a table for films, with  
> all
> of the basic information on each film.
>
> Then I have a separate table to list related films. There are three
> fields:
>
> a unique ID - not really used, but just there because...
> a host film id
> a related film id
>
> On the host film page it will list other films related to it.  The
> related film id is one of those related films.  The host film might
> have 3 or 5 or 10 records in the related table, each listing a
> different related film.
>
> So there is a page that allows the filmmaker to add related films,
> based on whatever criteria they like. On the host film page there  
> is an
> inline that searches the related table for the host film id, and then
> lists all of the related films. Now actually I use an SQL query to do
> this so that for every related film id I can also query the films  
> table
> and pull in the title of the film, its web address, etc.
>
> ...Doug
>
> On May 25, 2005, at 11:26 AM, R. Stephen Gracey wrote:
>
>
>> So, keeping the book example, is there anyway--short of defining a
>> specific
>> common topic--that you can say, "These three books are related to  
>> each
>> other" in a clump, regardless of author or anything else?
>>
>> Stephen
>>
>> R. Stephen Gracey
>> Bishop's Assistant for Information Strategy
>> Episcopal Diocese of Ohio
>>
>>
>
> ---
> Doug Gentry
> Dynapolis & Southern Oregon University
> p:  541-261-8501 / Toll Free: 888-490-0644
> [hidden email]
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage
>
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage
>


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Chris Tracewell
In reply to this post by R. Stephen Gracey
Stephen,

I would suggest creating a category table and assigning each film/
book to a specific category. You may need to have two such tables -  
"category" and "category_sub".

Each film would be assigned an id of a category it relates to. You  
need not think of the categories as only generic topics but you can  
use them to create very specific categories such as "William Smith  
Books About Gardening" - again a dual category set-up would make data  
entry easier ie - "William Smith" -> "Gardening"

Now when someone is looking at a William Smith book you just pull the  
books with the same category ID (or sub category ID if you have two  
category tables). Here's an example

TABLE: BOOKS
---------------------------------------------------
title: William Smith Gardening Delight
ID: 100
id_category_sub: 110

title: William Smith Gardening Awakenings
ID: 101
id_category_sub: 110


TABLE: CATEGORY_SUB
---------------------------------------------------
title: Gardening
ID: 110
id_category: 220

title: Kite Flting
ID: 111
id_category: 220


TABLE: CATEGORY
---------------------------------------------------
title: William Smith
ID: 220


So lets say your user is looking at a page for record 100 of the  
books table. Your SQL to get all other books in that sub category  
would be...

var:'the_sql'='
     SELECT
         b.id,
         b.title
     FROM
         books AS b
     WHERE b.id_category_sub = 110
     AND b.id != 100';

Hope this helps


Chris Tracewell
[hidden email]
Sports Merchandise College, MLB, NASCAR, NBA, NFL, NHL
http://www.teammascot.com


On May 25, 2005, at 12:10 PM, R. Stephen Gracey wrote:

> Thanks, Doug.
>
> And so, how do those fields get populated? If there are three  
> related films,
> for instance, then what does the table look like? How would you  
> search it?
>
> So, let's say you have film1 that is related in a cluster to film2 and
> film3.
>
> 1    film1        film2
> 2    film1        film3
>
> Is that enough? or do you have to do this...
>
> 1    film1        film2
> 2    film1        film3
> 3    film2        film1
> 4    film2        film3
> 5    film3        film1
> 6    film3        film2
>
> ?
>
> Stephen


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Paul Melia
In reply to this post by R. Stephen Gracey
FYI - the code below was a rough cut-n-paste, so there are one or two  
typos, like an errant [/if]

On May 25, 2005, at 3:45 PM, Paul Melia wrote:

> This is how I would approach it using your film example...
>
> Table: Directors
>
> 1 steven spielberg
> 2 george lucas
> 3 stanley kubrick
>
> Table: Studios
>
> 1 LucasFilm
> 2 Universal
> 3 Fox
> 4 United Artist
>
> Table: Movies
>
> 1 Raiders of the Lost Ark
> 2 Star Wars
> 3 2001
>
> To associate the three tables to each other, I would add the  
> following fields to the Movies table..
>
> DirectorID
> StudioID
>
> so the entry for 2001 looks like this
>
> ID           Name       DirectorID        StudioID
> 1            2001          3                        4
>
> but for Raiders it would look like this...
>
> ID           Name                                        
> DirectorID        StudioID
> 1             Raiders of the Lost Ark          
> 1                       1,2
>
> and Star Wars would be...
>
> ID           Name                 DirectorID       StudioID
> 1             Star Wars          2                       1,3
>
> You then can search against the fields looking for what ever you  
> want (show me all movies produced by Lucasfilm) and get back those  
> films. The code I use for for this with MySQL and Lasso 8 is...
>
> [var:'studioID'=(field:'studioID')]
>
>                 [var:'refarray'=(array)]
>                         [var:'refarray'=(var:'studiorid')->
> (split:',')]
>                             [iterate: $refarray, (var:'temp')]
>                                 [Inline:     -search,
>                                             -Database='movies',
>                                             -Table='studios',
>                                             -username='username',
>                                             -password='username',
>                                             -op='eq',
>                                             'id'=(var:'temp'),
>                                             -Returnfield='id',
>                                             -Returnfield='name']
>
>                                         [field:'name'] <br>
>                                     [/if]
>                                 [/inline]
>                             [/iterate]
>
>
> This is just the display code. I can post the edit/add code if this  
> the solution you where looking for.
>
> HTH,
> Paul
>
> On May 25, 2005, at 3:10 PM, R. Stephen Gracey wrote:
>
>
>> Thanks, Doug.
>>
>> And so, how do those fields get populated? If there are three  
>> related films,
>> for instance, then what does the table look like? How would you  
>> search it?
>>
>> So, let's say you have film1 that is related in a cluster to film2  
>> and
>> film3.
>>
>> 1    film1        film2
>> 2    film1        film3
>>
>> Is that enough? or do you have to do this...
>>
>> 1    film1        film2
>> 2    film1        film3
>> 3    film2        film1
>> 4    film2        film3
>> 5    film3        film1
>> 6    film3        film2
>>
>> ?
>>
>> Stephen
>>
>>
>>
>> R. Stephen Gracey
>> Bishop's Assistant for Information Strategy
>> Episcopal Diocese of Ohio
>>
>> [hidden email]
>>
>> http://www.dohio.org
>>
>> Trinity Commons
>> 2230 Euclid Avenue
>> Cleveland, OH 44115
>>
>> Direct:   (216) 774-0454
>>
>> Diocesan: (216) 771-4815
>> In Ohio:  (800) 551-4815
>>
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:[hidden email]]  
>> On Behalf
>> Of Doug Gentry
>> Sent: Wednesday, May 25, 2005 2:41 PM
>> To: [hidden email]
>> Subject: Re: [OT?] A database design question: many-to-many-to-
>> many...
>>
>>
>> Sorry to be jumping into this thread late - but this is similar to
>> something I had to solve during the past month or so. Maybe this will
>> help (or maybe not...)
>>
>> I have a site that lists films. So there is a table for films,  
>> with all
>> of the basic information on each film.
>>
>> Then I have a separate table to list related films. There are three
>> fields:
>>
>> a unique ID - not really used, but just there because...
>> a host film id
>> a related film id
>>
>> On the host film page it will list other films related to it.  The
>> related film id is one of those related films.  The host film might
>> have 3 or 5 or 10 records in the related table, each listing a
>> different related film.
>>
>> So there is a page that allows the filmmaker to add related films,
>> based on whatever criteria they like. On the host film page there  
>> is an
>> inline that searches the related table for the host film id, and then
>> lists all of the related films. Now actually I use an SQL query to do
>> this so that for every related film id I can also query the films  
>> table
>> and pull in the title of the film, its web address, etc.
>>
>> ...Doug
>>
>> On May 25, 2005, at 11:26 AM, R. Stephen Gracey wrote:
>>
>>
>>
>>> So, keeping the book example, is there anyway--short of defining a
>>> specific
>>> common topic--that you can say, "These three books are related to  
>>> each
>>> other" in a clump, regardless of author or anything else?
>>>
>>> Stephen
>>>
>>> R. Stephen Gracey
>>> Bishop's Assistant for Information Strategy
>>> Episcopal Diocese of Ohio
>>>
>>>
>>>
>>
>> ---
>> Doug Gentry
>> Dynapolis & Southern Oregon University
>> p:  541-261-8501 / Toll Free: 888-490-0644
>> [hidden email]
>>
>>
>> --
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription:
>> http://www.listsearch.com/lassotalk.lasso?manage
>>
>>
>>
>> --
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription:
>> http://www.listsearch.com/lassotalk.lasso?manage
>>
>>
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage
>


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Doug Gentry
In reply to this post by R. Stephen Gracey
I think the latter in your case. If you want film 1 to show films 2 and
3 as related, and also on film 2's page, to show film 1 and 3 as being
related I believe you'd need to do the longer list below.

In my case the relationships are just established one way - a film
maker just chooses what other films are related to his film.

I feel it in my bones that one of the SQL experts here might know of a
query that would allow the use of the smaller table below, but it's
beyond me.

...Doug

On May 25, 2005, at 12:10 PM, R. Stephen Gracey wrote:

> Thanks, Doug.
>
> And so, how do those fields get populated? If there are three related
> films,
> for instance, then what does the table look like? How would you search
> it?
>
> So, let's say you have film1 that is related in a cluster to film2 and
> film3.
>
> 1 film1 film2
> 2 film1 film3
>
> Is that enough? or do you have to do this...
>
> 1 film1 film2
> 2 film1 film3
> 3 film2 film1
> 4 film2 film3
> 5 film3 film1
> 6 film3 film2
>
> ?
>
> Stephen
>

---
Doug Gentry
Dynapolis & Southern Oregon University
p:  541-261-8501 / Toll Free: 888-490-0644
[hidden email]


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

jasonhuck
In reply to this post by R. Stephen Gracey
I have been using queries like these to make the relationship work "in
both directions":

-- to get any column from the related items
SELECT i2.id, i2.name
FROM Items i
        INNER JOIN RelatedItems r
                ON (
                        i.id = r.main_id
                        OR i.id = r.related_id
                )
        INNER JOIN Items i2
                ON (
                        r.main_id = i2.id
                        OR r.related_id = i2.id
                ) AND i.id != i2.id
WHERE i.id = 14;

-- to get just the related ids
SELECT IF(r.main_id = i.id, r.related_id, r.main_id) id
FROM Items i
        INNER JOIN RelatedItems r
                ON (
                        i.id = r.main_id
                        OR i.id = r.related_id
                )
WHERE i.id = 14;


...but the OR clauses definitely incur a performance penalty,
especially once you start stacking them up...


- jason






On 5/25/05, Doug Gentry <[hidden email]> wrote:

> I think the latter in your case. If you want film 1 to show films 2 and
> 3 as related, and also on film 2's page, to show film 1 and 3 as being
> related I believe you'd need to do the longer list below.
>
> In my case the relationships are just established one way - a film
> maker just chooses what other films are related to his film.
>
> I feel it in my bones that one of the SQL experts here might know of a
> query that would allow the use of the smaller table below, but it's
> beyond me.
>
> ...Doug
>
> On May 25, 2005, at 12:10 PM, R. Stephen Gracey wrote:
>
> > Thanks, Doug.
> >
> > And so, how do those fields get populated? If there are three related
> > films,
> > for instance, then what does the table look like? How would you search
> > it?
> >
> > So, let's say you have film1 that is related in a cluster to film2 and
> > film3.
> >
> > 1     film1           film2
> > 2     film1           film3
> >
> > Is that enough? or do you have to do this...
> >
> > 1     film1           film2
> > 2     film1           film3
> > 3     film2           film1
> > 4     film2           film3
> > 5     film3           film1
> > 6     film3           film2
> >
> > ?
> >
> > Stephen
> >
>
> ---
> Doug Gentry
> Dynapolis & Southern Oregon University
> p:  541-261-8501 / Toll Free: 888-490-0644
> [hidden email]
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage
>

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Bil Corry
In reply to this post by R. Stephen Gracey
> ...but the OR clauses definitely incur a performance penalty,
> especially once you start stacking them up...


I've found that using UNION has been faster for MySQL than one complex query.
So assuming the table holding the related values (RELATED) had two columns,
film1 and film2:

-----------
RELATED
-----------
film1 film2
----- -----
12 14
12 11
11 13
14 10


Then the query would look like (for film id = 14):

(
        SELECT
                r.film2 as id,
                f.name
        FROM
                related r,
                films f
        WHERE
                r.film1 = 14 and
                r.film2 = f.id
)
UNION
(
        SELECT
                r.film1 as id,
                f.name
        FROM
                related r,
                films f
        WHERE
                r.film2 = 14 and
                r.film1 = f.id
)
ORDER BY
        f.name


Which would return IDs 10 and 12.

- Bil

------

Bil Corry
[hidden email]

Enterprise internet application development and security consulting
  http://www.fivegeeks.com/

Tools for Rapid Lasso Development
  http://www.lassoware.com/
 
-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
Jason Huck
Sent: Wednesday, May 25, 2005 3:14 PM
To: [hidden email]
Subject: Re: [OT?] A database design question: many-to-many-to-many...

I have been using queries like these to make the relationship work "in
both directions":

-- to get any column from the related items
SELECT i2.id, i2.name
FROM Items i
        INNER JOIN RelatedItems r
                ON (
                        i.id = r.main_id
                        OR i.id = r.related_id
                )
        INNER JOIN Items i2
                ON (
                        r.main_id = i2.id
                        OR r.related_id = i2.id
                ) AND i.id != i2.id
WHERE i.id = 14;

-- to get just the related ids
SELECT IF(r.main_id = i.id, r.related_id, r.main_id) id
FROM Items i
        INNER JOIN RelatedItems r
                ON (
                        i.id = r.main_id
                        OR i.id = r.related_id
                )
WHERE i.id = 14;


...but the OR clauses definitely incur a performance penalty,
especially once you start stacking them up...


- jason






On 5/25/05, Doug Gentry <[hidden email]> wrote:

> I think the latter in your case. If you want film 1 to show films 2 and
> 3 as related, and also on film 2's page, to show film 1 and 3 as being
> related I believe you'd need to do the longer list below.
>
> In my case the relationships are just established one way - a film
> maker just chooses what other films are related to his film.
>
> I feel it in my bones that one of the SQL experts here might know of a
> query that would allow the use of the smaller table below, but it's
> beyond me.
>
> ...Doug
>
> On May 25, 2005, at 12:10 PM, R. Stephen Gracey wrote:
>
> > Thanks, Doug.
> >
> > And so, how do those fields get populated? If there are three related
> > films,
> > for instance, then what does the table look like? How would you search
> > it?
> >
> > So, let's say you have film1 that is related in a cluster to film2 and
> > film3.
> >
> > 1     film1           film2
> > 2     film1           film3
> >
> > Is that enough? or do you have to do this...
> >
> > 1     film1           film2
> > 2     film1           film3
> > 3     film2           film1
> > 4     film2           film3
> > 5     film3           film1
> > 6     film3           film2
> >
> > ?
> >
> > Stephen
> >
>
> ---
> Doug Gentry
> Dynapolis & Southern Oregon University
> p:  541-261-8501 / Toll Free: 888-490-0644
> [hidden email]
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage
>

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [OT?] A database design question: many-to-many-to-many...

Olivier Miossec
In reply to this post by R. Stephen Gracey

You can read this article about Database Design on MySql

http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.html

---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage