SQL Design - Many to Many?

Is this what I want, and if so, how?

[Nick_W]

Hi all,

I'm having a little trouble working out how to represent some data and relate it. It's best described with an example:

Puppies
PuppyId
PuppyName

Handlers
HandlerId
HandlerName

I want a seperate table (i think) to say which handler takes care of which puppy. BUT: Sometimes more than one Handler may take care of a puppy and some Handlers may take care of several puppies.

WIth me?

What's the best way to do this? - I've been thinking about it a lot today and can only come up with tables with duplicate rows or some kind of 'comma delimited' list thingy. Like this:

PuppyHandler
puppyid ¦ handler <-- lots of dupe entries

or HandlerPuppy which would have the same thing.

So, is this the way to go?

->handlerid for puppyA = '1,3,3,45' and let PHP explode() that value to get the handler id's or is their a more elegant solution?

Many thanks!

Nick

[jatar_k]

If you have a links table like this

puppyid Ķ handler <-- lots of dupe entries

it should still only have one entry for each puppy to handler, why the dupes?

Why do you need handlerid for puppyA = '1,3,3,45'? Why would handler 3 be in there twice?

Unless you are relating it to some third item like time.

<added>typo

[edited by: jatar_k at 5:39 pm (utc) on Mar. 30, 2003]

[andreasfriedrich]

The canonical way to handle many to many relationships is having a third table which does the mapping.

+-------------------+ 
¦ ..Puppy2Handler.. ¦ 
+-------------------+ 
¦ PuppyID ¦ HndlrID ¦ 
+---------+---------+ 
¦ ......1 ¦ ......1 ¦ 
¦ ......1 ¦ ......4 ¦ 
¦ ......1 ¦ ......7 ¦ 
¦ ......3 ¦ ......2 ¦ 
¦ ......3 ¦ .....19 ¦ 
¦ ......4 ¦ ......8 ¦ 
¦ ......5 ¦ .....14 ¦ 
+---------+---------+ 

Querying for a PuppyID will give you all rows of HandlerIDs.

BTW a dog is for live not for christmas in case you havenīt learned that from S Club Juniorsī Puppy Love video. ;)

Andreas

[Nick_W]

Thanks andreas, just wanted to know what the 'established' method might be. Seems like it' the easiest way ;)

>for life

heh! - Was reading an normalization tutorial today that used them, just a good example..

Cheers...

Nick

[andreasfriedrich]

>>>for life

Dang, even spell checking did not help with that ;).

Andreas

[Nick_W]

>it should still only have one entry for each puppy to handler, why the dupes?

Sorry Jatar, missed your post!

Well, there isn't I guess. I'm just (or was) a little confused. In Andreases example there can/will be 'handler 4' repeated several times as she handles several puppies but I guess it's not too inefficient right? - I'm just in a bit of a whirl from to much reading.

Earlier, in a fit of entusiasm for my new knowledge I normalized a set of data to 7 tables.

I've calmed down now, it's only 4 ;)

Nick

[jatar_k]

andreas was more explicit by this If you have a links table like this I meant the same thing. He just laid it out much better. ;)

[andreasfriedrich]

>>it's not too inefficient right?

No, it wonīt. But more importantly you do the mapping in a way that will scale well and which will be easily extensible. You can query puppy handler relations any way you want. It speed matters that much than Iīd still go with the generic setup and provide some temporary and precalculated hash table for increased speed. But your basic data set should still be there.

Andreas

[Nick_W]

>any way you want

Yes, I'd need to do it both ways. So, make both an INDEX?

Nick

[andreasfriedrich]

Sure.