MySQL relationships...

lukemedway

I am trying to make my own news/commenting system and I have one table for News 'news_data' and one table for comments 'comments_data'.

The fields in the news_data table are:

news_id, Author, Date, Title, News_content;

and the fields in comments_data:

com_id, Author, Date, Title, Comment_content;

The problem I am having is knowing how to relate the two tables, so I can show the right comments to the corresponding news_id... I have thought of adding a news_id field to the comments table for a foreign key... but I'm not sure how it works...

Anyway... Any ideas?

carrot

you should use INNODB table type for your MySQL tables, since INNODB table can support foreign key.

lukemedway

INNO-WHAT!?!

Hmmm... SeemsI only have MyISAM, heap, ISAM & Merge table type.

what now? 🙁

nemonoman

One news_data record can have 0 to many associated comments_data records, yes?

Comments apply to one and only one news_data record, yes?

This is a simple 1 to Many relationship:

you would add a new column:
news_id to the comments_data table.

news_id is the KEY of a FOREIGN table (news_data)...a Foreign Key in the comments_data table that relates the 2 tables.

SELECT *
FROM
news_data
WHERE news_id=1

SELECT *
FROM
comments_data
WHERE news_id=1
ORDER BY com_id

If comments can apply to more than one news, it's a many to many relationship, handled differently. It doesn't look that way as you describe it, however.

lukemedway

You are right that the comments only apply to one news_id...

However, how do I get the values from the news_id field in the table 'news_data' in the news_id field in the table 'comments_data'?

Luke - mythed. 😕

carrot

INNODB is a first table type for MySQL. Before you use INNODB tables, you need to do download and install MySQL MAX version and set the ibdata and iblog path. If you didn't do these two things, you can't have foreign key support even your SQL is correct.

The following URLs may help you:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB

http://www.innodb.com/ibman.html

nemonoman

. If you didn't do these two things, you can't have foreign key support even your SQL is correct.

Could enlarge on this statement, please?

lukemedway

Well, I run forums on my host with no bother and I'm sure that somewhere in all the database tables there is a foreign key example.

Databases without the use of foreign keys sounds a bit silly to me!?! 😕

Wien

Actually you don't need a foreign key to refer an other table. But What you can't do is apply a constraint on the foreign key. in other words it can't check referential integrity

lukemedway

So how else would I do it?

Wien

I don't understand your problem, let's say you don't use INNODB, you can still sort your pb out with

news_data table(news_id, Author, Date, Title, News_content)

comments_data(com_id, Author, Date, Title, Comment_content, news_id)

carrot

. If you didn't do these two things, you can't have foreign key support even your SQL is correct.

what i said is to setup an environment to use INNODB table in MySQL. At the beginning, I also didnt understand what it is.....after i read the URLs that i stated before....i get more idea and can work with foreign keys to have an IC.

Since MySQL default table type is MYISAM that doesn't support foreign key, we need to use INNODB table if we want to use foreign key.

some said it is not recommened to use foreign key in tables, in fact, i dont think it is reasonable. as a normal DB, IC is a must for multiple tables.