Relational Database question (MS Access)

Factual Questions
1

I am trying to make a relational database that can support as many catagories and subcatagories and sub sub cats, etc as possible. What I mean is something that can emulate a directory structure (Ie as many folders in folders as possible) using very few tables as possible.

What I did is create a table in Access with 3 fields. CatID, CatName, and SuperCat. SuperCat is related back to CatID, but is not required. It SEEMS like it works, but I haven’t tried any SQL queries yet, but I’m sure the query will be very complicated.

Is there a better, more robust and “safer” way to make this DB? How would you do make this? If anyone knows and likes SQL, please feel free to make the query for my table or any people post.

Thanks

2

And let me add that I have the flu really bad and can’t think straight right now.

3

You’ve got the idea. What you need is a recursive table, as you laid out. The only thing that I can add to this is to place a restriction on the SuperCat column that the values placed in it must be present in the CatID column.

Zev Steinhardt

4

So what you have is a recursive relationship between cat and supercat, which you’ve physically represented in a single table.

I think Access supports that–if you write a select query for all Cats where SuperCat = x, it’ll write a nested SQL statement. but it should work.

5

Zev- The problem with placing the restriction on the SuperCat colum is that the highest level Cats would need to point somewhere. How do I get over this?

6

Two tables, one for top-level categories, and the other for everything else.

Or put in a special element which is its own supercategory, and have all the top-level categories claim it as their parent.

7

You could have it be null, or self-referencing.

Zev Steinhardt

8

Ill Have it be null, seems like that will be easier.

The BIG question is: Will this import into SQL server??? Ill find out eventually…

9

Yes, it will. And with SQL Server, you can put a constraint on the table to make sure that the values come from the other column.

Zev Steinhardt