 Table Relationship Problem, many parent many child |
|
|
|
|
  |
 Nov 2 2007, 07:24 AM
Post
#1
|
|
|
|||[ n00b King ]|||  Group: [HOSTED] Posts: 591 Joined: 20-June 07 From: Auckland Member No.: 45,102  |
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. |
 |
 
|
|
Nov 2 2007, 03:04 PM
Post
#2
|
|
|
A computer once beat me at chess, but it was no match for me at kick boxing.  Group: [MODERATOR] Posts: 3,744 Joined: 24-July 05 From: In Trouble Again... still? Member No.: 9,787  |
I think yu need a third table to allow the many-to-one relationship.
CODE MONSTER TABLE Like that...
-------------------- 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) |
|
|
|
|
Dec 11 2007, 09:43 AM
Post
#3
|
|
|
Newbie Group: Members Posts: 5 Joined: 11-December 07 Member No.: 54,581 |
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 |
|
|
|
|
Similar Topics
|
Lo-Fi Version | Time is now: 13th May 2008 - 06:24 PM |