Re: tricky database design

Daniel,

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

  A workable design depends on what these entities are and how they relate. How you want them presented in a report is certainly important, and it's a big clue in the search for how things relate.

  Do you think you can provide a better idea of just how things relate to one another?

Steve Kass
Drew University

Daniel Talsky wrote:

>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 Wed Jan 08 2003 - 06:01:39 CET