List:General Discussion« Previous MessageNext Message »
From:Kevin Waterson Date:September 15 2007 7:31am
Subject:Linking tables
View as plain text  
Hi all, having a spot of bother with a 'keywords' table.
I have a table of articles with an article_id and a table
of categories with a category_id, all is well.
These are linked via a article_link table so that an article
can be a member of multiple categories.

I then have a table of 'keywords' that will relate to each article.
I wish to be able to SELECT all articles associated with the keywords.
No issues so far.

Next, I wish to be able to dynamically add a table to the database, 
eg: a 'cats' table.

If a cat has a keyword of 'tiddles' associated with it. I would then like
to be able to SELECT all related articles, that is, all records in the articles
table, with the keyword 'tiddles'.

MySQL 5 and the db layout looks like this so far.
thanks for looking

CREATE table articles (
  article_id int(9) NOT NULL auto_increment,
  user_id int(9) default NULL,
  article_category_id int(11) NOT NULL,
  article_title varchar(50) NOT NULL,
  article_subject varchar(50) NOT NULL,
  article_blurb varchar(250) default NULL,
  article_body text,
  article_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  article_publish_date timestamp NOT NULL default '0000-00-00 00:00:00',
  article_orig_author varchar(50) NOT NULL,
  article_image longblob,
  PRIMARY KEY  (article_id)

CREATE table article_categories (
  article_category_id int(11) NOT NULL auto_increment,
  article_category_name varchar(20) NOT NULL,
  article_category_left_node int(11) NOT NULL,
  arcitle_category_right_node int(11) NOT NULL,
  PRIMARY KEY  (article_category_id)

CREATE table article_links(
 article_link_id int(11) NOT NULL auto_increment,
 article_id int(11) NOT NULL,
 article_category int(11) NOT NULL,
 PRIMARY KEY (article_link_id)

CREATE table keywords(
 keyword_id int(11) NOT NULL auto_increment,
 keyword_name char(20) NOT NULL,
 PRIMARY KEY (keyword_id)

"Democracy is two wolves and a lamb voting on what to have for lunch. 
Liberty is a well-armed lamb contesting the vote."
Linking tablesKevin Waterson15 Sep
  • Re: Linking tablesPeter Brawley19 Sep