Re: tricky database design

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Tue, 07 Jan 2003 03:18:00 GMT
Message-ID: <IFrS9.142576$pe.5804997@news2.east.cox.net>

Daniel Talsky wrote:

> CREATE TABLE "pages" (
> page_id serial,
> page_name varchar(30),
> template_path varchar(100),
> publish_path varchar(100),
> PRIMARY KEY ("page_id")
> );

> CREATE TABLE "headings" (
> heading_id serial,
> heading_text varchar(200),
> PRIMARY KEY ("heading_id")
> );

> CREATE TABLE "copy" (
> copy_id serial,
> copy_text text,
> PRIMARY KEY ("copy_id")
> );

> CREATE TABLE "ordering" (
> table_name varchar(30),
> id_ref int4,
> page_ref int4,
> sequence_order int2
> );
>
> So is there someone with a little bit more experience that knows a
> better way to do this?

To me it makes sense to order the assoication, since your stuff won't be ordered until you group it together on a page.

I don't see how the table ordering brings things together, some references clauses might help. When you mean table, do you mean table as HTML table? Like if you want to order a list of pages in a table of contents:

create table page (

     page_id     integer not null,
     path        varchar(256) not null,
     title       varchar(128) not null,
     primary key (page_id)

)
\g
create table table_of_contents (

     toc_id      integer not null,
     label       varchar(128),
     primary key (toc_id)

)
\g
create table table_of_contents_page (

     page_id     integer not null,
     toc_id      integer not null,
     sort_order  integer not null,
     heading     varchar(32) not null, -- heading as displayed in toc
     primary key (page_id, toc_id),
     foreign key (page_id) references page,
     foreign key (toc_id) references table_of_contents

)
\g

You could then generate your TOC with:

select *

   from table_of_contents as toc
   join table_of_cotnents_page as toc_page using (toc_id)    join page using (page_id)
  order

     by sort_order
\g

Hope this helps.

Alan Gutierrez Received on Mon Jan 06 2003 - 21:18:00 CST