Hey,
it depends on what type of relation you want. I'm no expert, but
here's my 2cents.
If PEOPLE can only have one shirt, one pants, one shoes ...
then
(1)your PEOPLE table might look like.
CREATE TABLE PEOPLE(
people_id auto.. blaa
pants_id int ..
shirt_id int ..
shoe_id int ..
..
)
(2)but if people can have more than one stuff .. then.
you'll need a seperate table called PEOPLE_LINK
CREATE TABLE PEOPLE_LINK(
people_link_id auto.. blaa
people_id int ..
pants_id int ..
shirt_id int ..
shoe_id int ..
..
)
option two is the best, gives you more flexiblility.
your SHIRT, PANTS .. table might look something like this.
CREATE TABLE SHIRTS(
shirts_id auto.. blaa
shirts_name varchar...
..
)
now assume you want to do a select ..
SELECT P.name,S.shirts_name
FROM people AS P, people_link AS PL, shirts AS S
WHERE P.people_id=PL.people_id AND
P.people_id='people_id' AND
PL.shirts_id = S.shirts_id
this will give you the person's name and all the shirts he/she owns.
hope this helps.
cheers
Ken
------------------------------------------------------------------------------
please reply to : [EMAIL PROTECTED]
www.mysqlwebring.com
------------------------------------------------------------------------------
>
>
> I have a relational DB question. I'll preface this and say I've had no
> formal training in DB's only on-the-job knowledge. I've been trying out some
> CASE tools and have discovered the following:
>
> Lets say I have a table that stores PEOPLE. I also have a table that stores
> SHIRTS, PANTS, SHOES. Now in the CASE tools when I create relationships
> between these tables, I get tons of "link" tables to relate PEOPLE to
> SHIRTS, PEOPLE to PANTS, PEOPLE to SHOES, and then I've got SHIRTS to PANTS,
> SHIRTS to SHOES, etc.
>
> I guess this is the proper way to implement these relationships. My question
> is, what I've done in the past is create a table like the following:
>
> PEOPLE_LINK
> ----------
> people_link_id int
> people_id int
> link_type char
> link_id int
>
> Where link_type is either SHIRT, PANTS, SHOES (like an ENUM). Depending on
> what the "link_type" is, the link_id is either the shirt_id, pants_id,
> shoes_id.
>
> Is there anything wrong with using this one table instead of four tables to
> implement these relationships? Obviously, if "PANTS" becomes "TROUSERS" I'll
> have to do a replace, but as far as relational DB's go, is there anything
> wrong with have a relationship based on two keys and a char field.
>
> Any insights would be greatly appreciated. Thanks very much.
>
> --
>
> Clarence Kwei
>
> [EMAIL PROTECTED]
>
> ScienCentral, Inc.
> (212) 244-9577 ext. 118
>
> <http://www.stn2.com>
> <http://www.sciencentral.com>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
