Reputation: 93
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.
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`)
)
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.....'
);
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:
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');
My issues with the references table 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.
Upvotes: 0
Views: 285
Reputation: 1523
Your database design is causing most of your issues here. Your three articles tables, maths, news and other should all be the same table with a type column to distinguish between the different types. Then it will be straight forward to set up a references table that contains two foreign keys to the articles table, one for the source article and one for the reference article.
I usually manage referential integrity in the application itself rather than in the database layer so that all your business logic is in one place. So if you delete an article then any reference entries should be deleted by the application itself.
Hope that helps!
Upvotes: 1