开发者

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

开发者 https://www.devze.com 2023-01-15 18:38 出处:网络
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 co

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

CREAT开发者_开发问答E 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?


How about this, to start.

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

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


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号