[thelist] Proper DB Table Design...

[Travis]

Hi All,

I have a question about what is the 'proper' table design for the following
situation

First, the background...

I want to create a db for storing 'news items' in arbitrarily deep levels of
categories. For this, I am using the adjacenct list model, so my tables look
something like this...


CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT,
parentid INT,
title VARCHAR(100),
PRIMARY KEY(id) )


CREATE TABLE documents (
id INT NOT NULL AUTO_INCREMENT,
parentid INT,
title VARCHAR(100),
article TEXT,
PRIMARY KEY(id) )


This way I can have any number of categories and subcategories with
documents in them.

Now...

I also would like to have the ability to sort documents and categories in
whatever arbitrary order I choose. The simplest way I can think of to do
this is to add an additional 'sortorder' column to each table, and order the
records numerically, so the new tables would look something like this...

CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT,
parentid INT,
sortorder, INT,
title VARCHAR(100),
PRIMARY KEY(id) )


CREATE TABLE documents (
id INT NOT NULL AUTO_INCREMENT,
parentid INT ,
sortorder INT ,
title VARCHAR(100),
article TEXT,
PRIMARY KEY(id) )

Where values of sortorder are unique across both tables, for all records
that have the same parentid.

I then SELECT a mixed resultset from these 2 tables using a standard UNION
statement and ORDER BY sortorder. Works fine, and as near as I can see, this
does not violate any of the rules of normalization.


However, technically speaking, is this 'proper'? Or should I instead be
creating a 3rd table, that looks like this...

CREATE TABLE sortorder (
sortorder INT NOT NULL AUTO_INCREMENT,
docid INT ,
catid INT ,
PRIMARY KEY(sortorder) )

The trouble with this, seems to me, is that there will always be empty rows
in the sortorder table, so in this respect is bad db design.

Anyhow, I would appreciate any advice.

Thanks!


-Travis


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.745 / Virus Database: 497 - Release Date: 8/27/2004