Relationships 2

[TamedTech]

Hi there Ladies and Gents,

I’m new to MySQL, I have done plenty of work with Microsoft Access so databases in general I am more than comfortable with. I’ve worked a little in MySQL, but only amending the odd table here and there.

I’m trying to achieve a particular relationship between two or maybe three table, yet I’m not sure of the best approach to do this in MySQL. I have a PHP MyAdmin panel which should make life a little easier.

The front end of this database is a ColdFusion based site, which is going to be dynamically driven by the Database.

Basically this is all hypothetical at the moment, but the idea is that I will have two tables, the first of which is the Main section of my site, it will contain all the information for the main pages of my site such as Home, About Us, News, Contact Us etc etc and will be structured as follows.

ID
page_title
page_header
page_content
page_footer

Now, the relationship I need to build is for all the sub sections of the site, so for instants I have a “News” page that is listed in the above table, I will then have another table that will list all of the News Articles. So when someone visits the news page I can then list all of the News Articles for them to read.

The sub-page table will probably have a very similar content to the table above and I just need to link their ID fields so all news article information links to the main news page in the other table.

I’ve probably explained that ass about tit … but I’ve given it a shot … I hope a few of you kind chaps can give me a hand.

Thanks,

Rob

 

[vacunita]

Basically all you need is to give you news articles a foregin key that matches that of the News page so for example:

if your News page has an ID of [red]2[/red]

then all your news articles would have a field named something like Parent_ID and have a value of 2 also.
That ius have the value of the ID of the parent page
Sub Table

ID  |    Parent_ID    |    TITLE    |     .....
1             1           something
2             2          News Article 1   
3             1         somethijng else
4             1         another content
5             2         News Article 2

Whe you query all news articles it's just a questtion of specifying the parent id such as:

SELECT *from subtable where parent_id=2

This would retunr all news articles.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[TamedTech]

Thanks for that vacunita, I thought that would be the method. The problem i had figuring out was how to establish that foriegn key. Can you offer any advice?

Thanks,

Rob

 

[sleipnir214]

The foreign key is not necessary unless you need the database to enforce referential integrity.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[vacunita]

As Sleipnir pointed out its not necessary, The relationship will happen when you create the records on the sub_table, and give then the proper Parent_ID.

Whe you issue the queries, you know that a parent_ID of @ is an article, of 1 will be main content etc...

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[TamedTech]

Ah i see .. this makes sense ... I just create the record for the news articles, i just choose the fact that its a news article on my form and have it place the correct ID in thier.

Thanks guys, that makes perfect sense ... i'll purple star you both.

Rob