Table Relationship Problem - many parent many child

sonesay

Nov 2 2007, 07:24 AM

Ok I did poorly in my database class so Im very bad at normalizing forms. I started creating my database and eventually ran into a problem with how I've set it up. Im not 100% sure how I'm going to fix it at this stage but heres the problem.

I have 2 tables MONSTER and ITEMS. Basically its layed out like this.

CODE

MONSTER TABLE
--------------------
m_id int (PK)
m_name varchar
m_level int
...etc


ITEMS TABLE
--------------------
i_id int (PK)
i_name varchar
i_nm int

I'm using them like this. item (Big sword with i_nm = 1) so the big sword drops off the monster of m_id of 1. I think this is what you call a 1 to 1 relationship. Later on I find out that the big sword with i_nm of 1 can also drop off another monster so it is a 1 to many.

I cant figure out how im going to alter my table to address this. I dont want to insert 2 similar items into the ITEMS table with 2 different i_nm ids to link them to each NM.

jlhaslip

Nov 2 2007, 03:04 PM

I think yu need a third table to allow the many-to-one relationship.

CODE

MONSTER TABLE
--------------------
m_id int (PK)
m_name varchar
m_level int
...etc

ITEMS TABLE
--------------------
i_id int (PK)
i_name varchar
i_nm int

Links Table
-------------
l_id int (PK)
m_id int (PK)
i_id int (PK)

Like that...

AiryDragon

Dec 11 2007, 09:43 AM

The rails way like that:

CODE

Links Table
----------------
Linked_id
Linked_class
Linking_id
Linking_class

This links table enables polimorphic associations. And you can add 1 more field like relation_type for relation types.
Samples to be clear:

CODE

1- Girl wearing blue shirt
  Linked_id : Girl.id
  Linked_class : Girl.class
  Linking_id : BlueShirt.id
  Linking_class : BlueShirt.class
  Relation_type : wear

2- Cat with red ribbon
  Linked_id : Cat.id
  Linked_class : Cat.class
  Linking_id : RedRibbon.id
  Linking_class : RedRibbon.class
  Relation_type : with

3- Boy holding cat
  Linked_id : Boy.id
  Linked_class : Boy.class
  Linking_id : Cat.id
  Linking_class : Cat.class
  Relation_type : hold

Added code tags to the lists

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE