How to model a database where one page can have different content types?

Question

I have the database table page with the fields contenttype_id and content_id. I want to link a page to a contenttype via contenttype_id and refer to an actual content via content_id. The problem is contents depend on the contenttype_id and are modelled differently, therefore the content_id refers to different tables depending on the contenttype_id.

CREATE TABLE "page"
("id" "INT",
"author" "VARCHAR(45)",
"created" "DATE",
"content_type" "INT",
"content_id" "INT")

CREATE TABLE "contenttype"
("id" "INT",
"name" "VARCHAR(45)")

CREATE TABLE "content_redirect"
("id" "INT",
"url" "VARCHAR(45)")

CREATE TABLE "content_script"
("id" "INT",
"url" "VARCHAR(45)",
"params", "VARCHAR(45)")

CREATE TABLE "content_text"
("id" "INT",
"text" "TEXT")

CREATE TABLE "content_process"
("id" "INT",
"step1" "TEXT",
"step2" "TEXT",
"step3" "TEXT")

CREATE TABLE "content_extprocess"
("id" "INT",
"system_id" "INT",
"process_id" "INT")

How to do that? Or is it already theoretically wrong?

Answer 1

Assuming the content of the page have different data structure and the content is not suitable to be stored as a simple blob.

Have page table with page_id and content_type_id. You could have different contents in different tables (one per each content_type_id), each content table providing a foreign key column page_id pointing to the page table. The disadvantage would be that you can't make one join to return the contents for all types of pages, but you'll have to build SQL queries based on different values of page.content_type_id field.

Answer 2

How about this, to start.

EDIT:

create table Content (
      ContentID   integer primary key
    , ContentType char(2)
    , ContentText text
) engine=InnoDb;

create table ScriptContent (
      ContentID integer primary key
    , URL       varchar(45)
    , Params    varchar(45)
) engine=InnoDb;
alter table ScriptContent add constraint fk1_ScriptContent foreign key (ContentID) references Content (ContentID);

create table ExternalProcess (
      ContentID integer primary key
    , SystemID  integer
    , ProcessID integer
) engine=InnoDb;
alter table ExternalProcess add constraint fk1_ExternalProcess foreign key (ContentID) references Content (ContentID);