Hi guys

Just a quick question that I hope someone can help me with.

So, I am trying to design a database which mimics the market in EVE-ONLINE, a space combat/trading game.

The market is split into categories.
The items are in seperate sub-categories.

This would in itself be relatively easy to do, but the problem comes when some items are in a Category - Sub Category, while others are in a Category - Sub Category - Sub Category.

Basically some items are organised into a 2 tier category, some a 3 tier, and there is noway of knowing if any item will be added in the future that make it a 4 tier category.

So my question is, how do I model this accurately.

Hope that makes sense.

Thanks

what you probably need is some form of self referencing column (ie aq column which refers to another column in the same table
eg..
tblCategory
CatID autonumber PK
CatDesc varchar
ParentCatID Integer FK to CatID in same table

1 | Food | Null
2 | Clothing | Null
3 | Deserts | 1
4 | Appetisers | 1
5 | Savoury | 3

the downside is that it may take slightly longer to iterate down the tree

iterating down the tree is an interesting problem, discussed in some detail in this article: Categories and Subcategories

in this particular case, your items would then reference the category or subcategory or subsubcategory the same way -- with a foreign key to CatID

Infinate teirs of categories...

Err: How about a table like this:
Heirachy(ParentID, ChildID)
Note that the key is combined.

I'm not the best at modelling, but this is the solution I can think of!
EDIT: Doesn't need to be a new table, you could jsut store the parent (or child) ID in the same table - but this way ensures that you don't get two items with the same parent and child

p
/ \
c c
\ /
gc

Hi guys.

Thanks for the prompt reply and links, I hoped you would say this as it was the conclusion I had come to.

I,m still unsure if a database query can sort it for me or if I would have to do this with a PHP script, but now I know my thinking was correct I can test it.

Thanks again.

Hi again.

Slightly of the database topic, but still relevant to my problem, does anybody have an elegant method of producing a form that can handle the selection and input?

Thanks

the selection and input of what
a form for what development environment (VC++, VB, VStudio, Access, Delphi.....)

one of the design methodologies Ive seen work quite well is either a collapsable tree control or say 2 list boxes with a clickable text above
eg

Food -> Deserts -> Hot Deserts -> Pies & Pastries
in fact its very similar to the clickable text you see on this web page#
"dBforums > General > Database Concepts & Design > How to model optional elements? > Reply to Thread"

List Box 1: contains all the sub categories of the current parent
List Box 2: contains all sub categories for the selected item in ListBox1
the text contains the full navigation to get to the current master category

in the above example it would contains all Pies & Pastries

I think the smarter style is going to be a collapsable tree control. its more intuitive for the user, easier to set up, once set up works fien (theres no interaction required between controls.

Hi

Basically the form will be in HTML, I did think about dropdown selection boxes, where the one to the right would update depending on what was selected in the first, however this would involve javascript and there are two problems with this, I dont know it and not everyone has it enabled, but would be the more elegant solution.

I think I am going to go with a simple process of adding a drop down selection box each time the page is updated until there is something input into a form field, which would indicate that the desired category level has been reached and a new category should be created.

Unless anyone has a better idea.

Thanks