Schema design - Reference slightly different data in different tables

Question

I'm trying to design a schema for a MySQL database and got stuck on the following. My database stores documents which should have relations with blocks of content.

create table document (
 `id` int unsigned auto_increment,
 `title` varchar(128) not null,
 primary key(id)
);
create table block_html (
 `id` int unsigned auto_increment,
 `type` varchar(32) not null,
 `contents` TEXT not null,
 primary key(id)
);
create table block_image (
 `id` int unsigned auto_increment,
 `type` varchar(32) not null,
 `url` varchar(255) not null,
 primary key(id)
);

As you can see the block data differs slightly between different types and I'm not sure how to design the relation. Obviously having a simple reference table will not work.

create table document_block (
 `document_id` int unsigned,
 `block_id` int unsigned,
 // foreign key??
);

What is a good solution to this problem?

• jynus: What you are asking is how to represent polymorphic associations. We had a similar question recently, and a link to this presentation by Bill Karwin was useful that last time: slideshare.net/billkarwin/sql-antipatterns-strike-back/32
• Bart: @jynus Thank you for the link to the slides. Looks very helpful.

Answer 1

is this what business process of yours?

enter image description here

that must be

document table :

create table document (
 `id` int unsigned auto_increment,
 `title` varchar(128) not null,
 primary key(id)
);

block table

create table block (
 `id` int unsigned auto_increment,
 `id_document` int // foreign key to document
 primary key(id)
);

block_html (one-to-one relation to block table)

create table block_html (
 `id` int unsigned auto_increment, // foreign key to block table and make it primary
 `type` varchar(32) not null,
 `contents` TEXT not null,
 primary key(id)
);

block image (one-to-one relation to block table)

create table block_image (
 `id` int unsigned auto_increment,// foreign key to block table and make it primary
 `type` varchar(32) not null,
 `url` varchar(255) not null,
 primary key(id)
);