I have the following tables; which will be holding information about various types of articles. I need some help with coming up with a proper schema for this.
Tables are:
CREATE TABLE IF NOT EXISTS `math_articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(250) NOT NULL,
`body` text,
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`)
)
CREATE TABLE IF NOT EXISTS `news_articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(250) NOT NULL,
`body` text,
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`)
)
CREATE TABLE IF NOT EXISTS `other_articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(250) NOT NULL,
`body` text,
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`)
)
CREATE TABLE IF NOT EXISTS `references` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`article_from_table_name` text NOT NULL,
`from_id` int(11) NOT NULL,
`article_to_table_name` text NOT NULL,
`to_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
inserting test data:
INSERT INTO `TEST`.`math_articles` (
`id` ,
`title` ,
`body`
)
VALUES (
NULL , 'fibonacci sequences', 'fib sequences are: 0,1,1,2,3,5...also see article Leonardo of Pisa'
);
Since this math_articles.title = 'fibonacci sequences' mentions that article 'Leonardo of Pisa' my program will insert in to other_articles table the following data:
INSERT INTO `TEST`.`other_articles` (
`id` ,
`title` ,
`body`
)
VALUES (
NULL , 'Leonardo of Pisa', 'Leonardo of Pisa also known as Leonardo of Pisa, Leonardo Pisano, Leonardo Bonacci, Leonardo Fibonacci, or, most commonly, simply Fibonacci, was.....'
);
The schema problem regarding table references
Since the table other_articles.title = 'Leonardo of Pisa' was referenced in the table math_articles.title = 'fibonacci sequences' i was to save this reference in the references table as follows:
not sure/problem insert into references table
INSERT INTO `TEST`.`references`
(`id`, `article_from_table_name`, `from_id`, `article_to_table_name`, `to_id`)
VALUES
(NULL, 'math_articles', '1', 'other_articles', '1');
Whats the best way of going about saving these references?
My issues with the references table schema!
- The data type of the two columns article_from_table_name and article_to_table_name is text but they are actual tables in my database.
- from_id and to_id should be forign keys of their prespective tables as from_id = article_from_table_name.id and to_id = article_to_table_name.id I don't know how to define this in the schema.
what if i delete the article math_articles.title = 'fibonacci sequences' then the references table to also be updated, I know I should use some sort of "ON DELETE CASCADE' trigger.
- Regards
