categories off-set

Question

Hi there,

I am about to build an SQL database and i need to get it right instead of making mistakes and fixing them down the line which is why i am here...

I will have main categories and sub categories to them, i need to know weather it'd be best to store the sub categories in the same tables as the main but just offset them for example:

Protein | Fat Loss | (two main cateogies)
Whey    | example  | (these would be linked)

I was thinking of designing the table like this:

cid, name, lcid (linked number)

so if lcid is set to 0 then its a main categorie but if its got a number relating to a cid in it then its a sub-categorie.

then i would use php to link them together and display them.

Answer 1

What you're talking about is called an Adjacency List. It is useful for creating a hierarchy that changes often but doesn't have to provide much information. If, however, your hierarchy changes less often, but has to answer questions like "how many siblings/ancestors does this node have", a Nested Set model will do you better. Have a look here for a description of pros and cons.

Answer 2

Adjacency List seems like the best option thanks for your help