MySQL Forums
Forum List  »  InnoDB

Re: Multiple Referenced (Parent) Tables to One Referencing (Child) Table
Posted by: edward croft
Date: April 16, 2008 01:35AM

I am having a similar issue and have hit a wall much as you have.

i have many different types of content, each with their own characteristics relating to the stock market, and forums and wikis for each. But i'd like to store the data for each in the same tables...

i.e.so rather than having a posts_stocks and posts_sectors tables separately I'd like to have a single 'posts' table for all forums. But this creates an issue for how to relate the post information to the right foreign master table.

I thought a solution might be to have a foreign key id column and then an ENUM column stating the foreign table name... (stocks, sectors, etc)

e.g.
post_id___post_content_____foreign_id_____foreign_tablename
1_________bla bla__________4_______________stocks
2_________bla bla__________5_______________stocks
3_________bladebla_________4_______________sectors

etc



This solution works great when i know that all the info pulled on a certain web page relates to a single data type, but if i need to join specific rows to their master table i hit a brick wall.

i.e. if i needed to write a query such as...

select post_content, foreign_tablename.name from posts where foreign_tablename.id = posts.foreign_id

... i can't as the foreign table name is a column entry in my posts table.

I hope i've explained this clearly.
Any help much appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Multiple Referenced (Parent) Tables to One Referencing (Child) Table
3128
April 16, 2008 01:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.