I have a design question that has me completely perplexed - although part of
me feels that I may be over-complicatiing things!!
The scenario is this: My company manufactures budgeting software. Our
library consists of various families of suites, or product folders. (i.e.
Web Suite, Client/Server Suite). Within those suites, there are specific
software titles (i.e. Budgeter, Planner, Templates, etc.). And those
particular titles have version numbers. We need to design a database (or
tables) that will allow us to store a hierarchy of product folders and
products.
Product Folder 1
Product 1
Version 1
Version2
Product 2
Version 1
Product Folder 2
Pruduct Folder 3
As you can see, a product folder needn't have any products under it. A
product folder can essentially be a product unto itself.
Here's one idea I had:
PRODUCT TABLE
ProductID Identity NOT NULL
ProductName Varchar(64) NOT NULL
RootFolderID Identity NOT NULL
ParentProductID Identity NOT NULL
PRODUCT FOLDER TABLE
ProductFolderID Identity NOT NULL
ProductFolderName Varchar(64) NOT NULL
**FK relationship between two tables on RootFolderID/ProductFolderID
This woks somewhat but there's the issues of SELECT statements sorting on
the ProductID. The table(s)need to be designed as such so that no matter
where a new row is inserted into the product table, a SELECT statement will
return the hierarchy in the right order based on the product folder and then
by product, version, etc. It also needs to be designed as such to allow for
essentially an infinite number of levels under a root node(product folder).
