From: Peter Brawley
Date: September 19 2007 3:03pm
Subject: Re: Linking tables
List-Archive: http://lists.mysql.com/mysql/209164
Message-Id: <46F13A5C.8050305@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin,

To link articles & keywords wouldn't you want a table like this?

CREATE table articles_keywords(
  AK_id int auto_increment PRIMARY KEY,
  AK_article_id int NOT NULL,
  AK_keyword_id int NOT NULL
);

I don't understand why you want the sort of 'dynamic table' you describe.

PB

-----

Kevin Waterson wrote:
> 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
> Kevin
>
> 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)
> );
>
>
>
>