Hi All, I realize I posted this in the wrong forum category, so I'm adding it here, where it should have been in the 1st place. Apologees if I shouldn't be doing this, in which case please let me know. Thank-you.

---

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!

To my way of thinking, the sortorder table is a good idea. The way I think about this kind of problem implies multiple orders, depending on how I'm looking at things... Not all categories or documents belong in all orders.

You are correct, in that using a sortorder table leaves open the possibility of logical "holes" in your data, but those holes are an important part of my way of looking at the data!

-PatP