Jump to content
xisto Community
sonesay

Table Relationship Problem many parent many child

Recommended Posts

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.

MONSTER TABLE--------------------m_id int (PK)m_name varcharm_level int...etcITEMS TABLE--------------------i_id int (PK)i_name varchari_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.

Share this post


Link to post
Share on other sites

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

MONSTER TABLE--------------------m_id int (PK)m_name varcharm_level int...etcITEMS TABLE--------------------i_id int (PK)i_name varchari_nm intLinks Table-------------l_id int (PK)m_id int (PK)i_id int (PK)
Like that...

Share this post


Link to post
Share on other sites

The rails way like that:

Links Table----------------Linked_idLinked_classLinking_idLinking_class

This links table enables polimorphic associations. And you can add 1 more field like relation_type for relation types.
Samples to be clear:
1- Girl wearing blue shirt  Linked_id : Girl.id  Linked_class : Girl.class  Linking_id : BlueShirt.id  Linking_class : BlueShirt.class  Relation_type : wear2- Cat with red ribbon  Linked_id : Cat.id  Linked_class : Cat.class  Linking_id : RedRibbon.id  Linking_class : RedRibbon.class  Relation_type : with3- Boy holding cat  Linked_id : Boy.id  Linked_class : Boy.class  Linking_id : Cat.id  Linking_class : Cat.class  Relation_type : hold
Notice from jlhaslip:
Added code tags to the lists

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.