Re: tricky database design

Daniel,

  It sounds to me like perhaps all you need is

CREATE TABLE "pages" (

        page_id                 serial,
        page_name               varchar(30),
        template_path           varchar(100),
        publish_path            varchar(100),
        PRIMARY KEY ("page_id")

CREATE TABLE PageElement (

        element_id              serial,
        ElementType             varchar(10), -- if necessary
        ElementData             ???,  -- see below
        PRIMARY KEY (element_id)

CREATE TABLE Page (

        page_id int not null references pages(page_id),
        element_id int not null references PageElement(element_id),
        position int not null, -- ordinal position on the page
        PRIMARY KEY (page_id, position) -- allows a repeated element

The main conceptual issue I see here is that various types of data are all "page elements" from the point of view of the layout on a page, but are distinct entity types like "image", "text," etc., intrinsically. But if you are putting together a web page, then you only need HTML descriptions for each element, and perhaps PageElement.ElementData can just hold the HTML that would need to go into the page for that item. Unless there is some reason you need more than the HTML for an item in the layout, maybe this will work.

SK

Daniel Talsky wrote:

>> One of my rules of thumb is that as soon as you start putting
>>the names of tables into other tables, something is wrong with
>>the model. Table names are metadata, and data inside tables
>>are data - they are very different things.
>>
>>
>
>I totally agree. I could sense that I wasn't quite going about it the
>right way, but could think of any other way to do it. I just thought
>that perhaps doing it the wrong way (ie. the only way I could think
>of) would help someone understand what I was trying to do.
>
>
>
>> I think it might be wise to step back and think about what
>>your data represents. You have pages, and best I can tell,
>>the structure of a page is that it contains items of two kinds
>>in a particular order. The fact that you have named them
>>headings and copy suggests to me that headings "head"
>>something (perhaps some "copy"?). If that is the case,
>>perhaps you have pages - pages that have sections, and
>>sections that comprise one heading and one (?) copy - a
>>shallow hierarchy, perhaps.
>>
>>
>[...]
>
>
>> Do you think you can provide a better idea of just
>>how things relate to one another?
>>
>>
>
>Sure. Thanks for offering your expertise. You can't know how much I
>value this kind of valuable specialized free advice. Hail usenet!
>
>I actually do not want such a hierachy. I want there to be elements
>that are all children of the parent page itself. Later maybe
>subgroupings could be introduced, but at this point I want only one
>level of depth. I want to be able to have elements of different types
>that could be ordered together in any way the user desires....
>
>You are correct that I am thinking of "heading" as basically a heading
>for copy. An <h1> basically. "copy" would be basically a paragraph
>or <p>.
>I might also like to have other types of elements...perhaps images.
>
>So, the user could have this configuration on a page:
>
>Heading
>Paragraph
>Image
>Paragraph
>Heading
>
>Or, they could switch it around and also have:
>
>Heading
>Heading
>Paragraph
>Image
>Heading
>Image
>
>I'd like to save these elements in thier respective tables, along with
>the order in which they appear on the page. Then, if the user wanted
>to move the elements around on the page, they could do so in any order
>they want. It's very easy for me to concieve of how to actually
>execute this (I'm more of a programmer than a database engineer) than
>it is to understand how to store it and retrieve the arrays I need
>from the database.
>
>I know there's something I'm missing...some way to represent the
>relationship between the page, and these elements that are located in
>several different tables, but I just can't quite wrap my brain around
>it.
>
>Is that a little bit clearer? If not...I could actually generate some
>examples of the kind of HTML pages I'd like to generate, and the kinds
>of forms that I'd like people to be able to use to manipulate it.
>
>Thanks again,
>DanielTalsky.com
>
>
Received on Wed Jan 08 2003 - 20:41:32 CET