[thelist] SQL Multiple entries in same column?

[isaac]

> the thing to remember, though, is that a many-to-many logical
> relationship
> is always implemented as two one-to-many physical relationships
>
> holler if you would like me to give examples

just quickly for those who've not tried a many-to-many relationship (in a
database that is ;)), here's a little example that might help things make
sense. i'm far from an expert, so rudy will correct me if i screw things up.
(he was the one who helped me work it out in the first place.)

anyway, imagine that you have a table of basketball club members. you'd have
columns like memberid, membername, memberemail, memberprofile, etc.

then, imagine that the club had multiple teams (mens division 1, mens
reserves, womens division 2, etc). the columns in the team table might be
teamid, teamname, etc.

now, each team is going to relate to multiple/many members. if each member
only played for one team, then you'd be fine with a one-to-many r'ship: just
throw a teamid column in the member table.

but if you have some people playing for multiple teams, you'll need a
many-to-many r'ship, and as rudy suggested, you implement this using two
one-to-many r'ships. this means you'll want a go between table. something
that shares teamid's and memberid's. think of this table as a recording of
"player records".

in the player table, the fields would be playerid (your primary key),
memberid, teamid (the 2 foreign keys), and you might want to have an extra
field: playercaptain. this can specify whether or not a member on any
particular team is the captain or not.

your one-to-many r'ships would exist like so:

	member.memberid - player.memberid
and
	team.teamid - player.teamid

the in-between table (PLAYER) links members to teams giving you your
many-to-many r'ship.


i'll populate the db:

MEMBER
memberid	membername
1		isaac
2		rudy
3		oliver

TEAM
teamid	teamname
1		mens div1
2		mens div2
3		mixed casual
4		veteran div1

if i played for div1 and mixed casual, oliver captained div2, and rudy
played for div2 and captained veteran div1, the PLAYER table would be
something like this:

PLAYER
playerid	teamid	memberid	playercaptain
1		1		1		no
2		3		1		no
3		2		3		yes
4		2		2		no
5		4		2		yes


hope this helps clear some things up for other db newbies (i'm not far from
it myself :p)


isaac
                                           --------------------------
                                           isaac: triple zero digital
                                           dynamic internet solutions
                                           e: isaac at triplezero.com.au
                                           web: www.triplezero.com.au
                                           mobile phone: 0414 758 000