MySQL Schema Design – Reference Different Data in Tables

foreign keyMySQLrelational-theoryschema

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?

Best Answer

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)
);
Related Question