mkk700
mkk700

Reputation: 93

MySql database schema referencing problem

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!

Upvotes: 0

Views: 285

Answers (1)

Caps
Caps

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

Related Questions