Hi

I'm trying to think of the best way to have a database of items which would have unlimited sub categories, i.e. Main category of Cars then sub category of Manufacturer a sub of that say Parts and so on.

Do you have any ideas on how this could be done as i have only had experience of having a set number of sub categories in seperate tables and i know this isn't the correct way of doing it?

Is it possible to have all the categories and subcategories listed in the same table and linked somehow?

Sorry if i have worded the question strangely but i can't get my head around how to solve this problem.

Thanks

Paul

You would do this:

tblManufacturer
ManufacturerID - PK
ManufacturerName - text
etc

tblCar
CarID - PK
CarName - text
ManufacturerID - FK to tblManufacturer
etc

tblPart
PartID - PK
PartName - text
PartDescription - text

Then create a junction table to join parts to cars:

tblJunction
JunctionID - PK
CarID - FK to tblCar
PartID - FK to tblPart
etc

This last table is how yu have all cars and their parts listed together in the same table and linked.

Does this help?


mmcdonal

Thanks for the reply.

I understand what you've said but it's not quite what i'm trying to do.

I'm looking to make a website which lists lots of categories with lots of levels of subcategories which can be added/updated/deleted all the time and i want my database, which i'm playing about with in Access before i decide anything properly, to be able to cope with any amount of subcategories without having to create any more tables as there is potential for alot of categories and other other categories below those.

I hope i tried to clear up to you what i'm trying to do.

Thanks again for your help

Paul