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!