Re: tricky database design

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)

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

     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

   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 Tue Jan 07 2003 - 04:18:00 CET