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)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
