I've the database table page using the fields contenttype_id and content_id. I wish to link a page to some contenttype via contenttype_id and make reference to a real content via content_id. The issue is contents rely on the contenttype_id and therefore are modelled in a different way, and so the content_id describes different tables with respect to the contenttype_id.

("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 achieve that? Or perhaps is it already theoretically wrong?

What about this, to begin.

alt text


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

Presuming this content from the page have different data structure and also the submissions are not appropriate to become saved like a simple blob.

Have page table with page_id and content_type_id. You might have different contents in various tables (one per each content_type_id), each content table supplying an overseas key column page_id pointing towards the page table. The disadvantage is you cannot make one join to come back the contents for all sorts of pages, but you will need to build SQL queries according to different values of page.content_type_id area.