MySql database schema referencing problem

I have the following tables; which开发者_StackOverflow中文版 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

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!