Home -> Usenet -> comp.databases.theory -> tricky database design

tricky database design

From: Daniel Talsky <danieltalsky_at_hotmail.com>
Date: 6 Jan 2003 17:56:33 -0800
Message-ID: <7d05e1d8.0301061756.7ed95327_at_posting.google.com> 





I'm having a database design difficulty that I'm not sure how to
solve.



I want to implement a very simple Content Management System in
PHP/PostgreSQL.



The main gist of what I need to do, is be able to associate a page (a
web page) with a number of different elements in a specific order. 
For example, headings and hunks of copy.



So, first of all I have my page table...simple:
(this schema is postgres, but it's pretty basic)



CREATE TABLE "pages" (

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




);




Then I have the tables for my headings, and my copy:
(this is simplified)



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")




);




Now I just need to associate them, including an order.  I came up with
an ordering table, but I'm not so sure this is a good way to do it,
and I'm having a hard time coming up with the SQL statement that will
give me what I want: A result set that includes the content I want in
the correct order for a given page.  Here's what I came up with:



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?



Thanks!
Received on Tue Jan 07 2003 - 02:56:33 CET












Original text of this message