Re: tricky database design
Date: Thu, 09 Jan 2003 12:07:06 GMT
Message-ID: <3E1D653B.1090102_at_izzy.net>
Mr Kaas has gone and solved your problem for you! Still, I am going to respond to your answers to my questions, perhaps more for my benifit than for yours.
Note I am taking one step away from modeling HTML in an RDBMS. You don't want to do that. If you do want to do that just leave the HTML on the file system.
The last thing asked:
>>Isn't a heading really copy that is usually very terse?
>> - If you agree than heading is a property not an object, a column not a
>> table.
>
> It seems like they could just be on the same table. But I want them to be
> basically modules...for instance I'd also like to have images, horizontal
> rules, charts...that the user could move up and down on the page. So think
> of them as modules. I just used "headings" and "copy" to simplify it as
> much as possible.
So all your content belongs in one table, with a flag as to its type.
create table content_type (
content_type_id integer not null,
name varchar(32) not null,
primary key (page_content_type_id)
)
\g
create table content (
content_id integer not null,
type integer not null,
url varchar(256),
heading varchar(256),
body text,
primary key (page_content_id),
foreign key (type) references page_content_type
)
\g
I called the table content because it is simply content. You may want to format it as plain text messages to send to cell phones. You may want to output XML. You should not try to model your HTML document in the database. You are going to kick yourself if you do.
Just to be particular, we create a lookup table of types 'copy', 'heading', 'image', to catch any typos/thinkos in our code.
Three fields for content. A URL is a common component in publishing these days, applies to citations, links, images. A heading field separate from a body field, since that is the way most articles look. For headings, leave the body null.
The other things asked:
>>Will headings or copy appear on more than one page?
>> - Is this what the order table is about, what goes where?
>
> I am not intending that headings or copy will appear on more than one
> page, they only need a one page to many headings/copy relationship.
Neither Mr Kaas nor I seem to believe that you will only use a piece of content once.
create table page (
page_id integer not null,
publish_path varchar(32) not null,
primary key (page_id)
)
\g
create table page_content (
page_id integer not null,
content_id integer not null,
page_content_id integer not null, -- Repeat content on page (image twice?).
sort_order integer not null,
primary key (page_id, content_id, page_content_id)
foreign key (page_id) references page,
foreign key (content_id) refrences content
)
\g
Here content can be resused on different pages, and within a single page.
>>Can a piece of copy have more than one heading?
>>Do headings ever exist without a piece of copy?
>> - You need to relate copy and heading somehow, if they don't really
>> belong as columns in the same table. Are they separate so you can put
>> headings on a front page, and over the copy on a detail page?
> In a way, they are not really related. It's true that in this simplified
> example, headings and copy are really the same thing, but I want this tool
> to work for people with basically a sub-MSWord level of computer usage. I
> don't want people to have to think about formatting. <snip>
This is why I moved it all into one table, content, create new types as needed.
I hope this helps. I am open to any criticisms from the veterans in this group. Just trying to be helpful.
Alan Gutierrez Received on Thu Jan 09 2003 - 13:07:06 CET