Just started learning MySQL, more specifically using Python and MySQLdb and everything is up and running fine.

I have an interface where i generate buttons:

1) Firstly the main catagories are generated.
2) on clicking these buttons, sub-catagories of these catagories have thier buttons generated.
3) once again on clicking these "sub-catagory" buttons, a last set of "item" buttons are created.

as far as i can see, here is my rough structure:

main catagory > holds many > sub catagories > holds many > items > has many > properties.

Here is my question.

I know i need a table for my main catagories, but do i need a new table for everyone of my sub catagories aswell, or do i simply bung them all into one table and retrieve them based on their parent catagory?

I need to use a database because catagories and sub catagories can be added or removed. I already have a working version of this mechanism using "folders", "sub-folders" and text documents representing the "items" containing thier properties, although this isnt at all secure which is why i am moving to a database solution.

Thanks for the help.

What if i place a field in the "sub-catagory" table called "parent_catagory" or something similar, do i need to link this in some way?

The entire mechanism is going to have a maximum number of 8 catagories, 8 sub-catagories per catagory and a maximum of 25 items per sub-catagory.

Could i have one table for catagories, one for sub-catagories and one for items? (its a maximum of 1600 items)

If there is a "propper" way to do this i would prefer to learn that so that when dealing with larger databases i am designing them correctly.

Any thoughts?

actually u only need three tables
1. main category with key main-category-key and other fields
2. sub category with primary key : sub-category-key with foreign key main-category-key and other fields
3. item with primary key is item-key with foreign key sub-category key, and other fields
alternatively because ur data is small for number 3 u can use another foreign key main-category-key such that u don't need to go through sub category to find the main category your item is in
i hope it is useful

I will likely do the three tables and link them up as you suggested.

Il definately use sub catagories though because it its a quick way of filtering results while ensuring there is a comfortably managable amount of information on screen, ive only got roughly a quater of the screen dedicated to this interface.

Thanks for your time and help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.