Re: tricky database design

Thanks for your help, Alan, let me explain a little further:

> 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:

The table field in the ordering table is meant to refer to a database table. I have an HTML page, and it wants to have some elements from the "heading" table and some from the "copy" table, and be able to use them interchangably.

So lines in the ordering table would look like this:

table_name id_ref page_ref sequence_order ---------- ------ -------- --------------

heading         233         42                1
copy             16         86                4
copy            403         22                1

See? So that's how I could get one result set that had both lines from the "copy" table and from the "heading" table.

I don't quite understand what you're doing with the table of contents page...that doesn't necessarily mean it's not correct...

Anyway...does that clarify? Or only muddle the waters?

DanielTalsky.com

>
> 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 Wed Jan 08 2003 - 02:20:01 CET