I will preface this post with the fact that, I am familiar with a very base level of database design.

I am working on a database currently for an inventory system that will essentially inventory any and everything (from furniture, to *potentially* books).

Before I present the entire database, I'd like to present a problem to you all. I've got a table named "category" that consists of the fields ("id", "name","id_sub","master_id").

We have no idea how deep that the creation of subcategories is going to go, so this is the only solution I've been able to come up with.

It seems as though it's common practice to post the ERD here along with the OP. I'm working on that now for you all, but am eager to get this post up for you all to begin discussion.

(For the record, this design was done by someone else and I'm recreating it in DBDesigner and *attempting* to build upon it)

Of course, this post makes sense to me, but you all might be completely lost, and if so, I apologize ahead of time and will work on completing the ERD.

and that problem is... ?

Well, before we get into the specifics of your design, I'd like to present you with this solution to your problem:

We had a short discussion on hierarchies a while back (if that's the issue).

I've bold typed the portion that is in question.

Is there a better way to manage the categories... This seems awful dirty to me, but I cannot come up with a better solution.

Thank you for your quick replies.

EDIT: Reading mike_bike_kite's topic... it appears to have some very good discussion on the topic. Thank you.

so you have an adjacency model table, and you don't know how many levels you have

that still has not elucidated what the problem is

that fact that there are many levels isn't a problem, it's a feature!!!

This is all I needed to know. I was unfamiliar with the terminology "adjacency model". Now I've got something to work off of and do some more research on.

Thank you.

No problem. Stop back in the forums if you need assistance writing code around the schema.