Data model for storing categories (multiple parent nodes)

Question

I've the following category structure:

  - Transport (10)
    - Cars (5)
      - Audi (2)
      - BMW (3)
      - ...
      - Spare Parts (5)
        - Audi (5)
          - Audi glass (1)
          - Carburetors (4)
          - Mirrors
          - ...          

    - Buses

    - Spare Parts (5)
      - Audi (5)
        - Audi glass (1)
        - Carburetors (4)
        - Mirrors
        - ...
      - BMW
        - Audi glass
        - Carburetors
        - Mirrors
        - ...
      - ...

What is the best way to store such data structure? I've tried tree, but it's a big duplication and counters doesn't work properly.

What I need is a way to store such structure in DB. To place item under each category. And have working items counters.

Answer 1

I would do

table TransportType (TTType(PK), Description)
table Model (MType(PK), Description)
table SparePart (SPNum(PK), Description)
table SparePartAssignment (AKey(PK), TTType(FK), MType(FK), SPNum(FK))

hope that helps

good luck MikeD

Answer 2

You can do it in SQL and as a matter of fact in db servers that don't directly support recursive queries. Have a look at here. The nested set model is elegant and easy to implement in any RDBMS.

Answer 3

If you know for sure how deep your nodes will go then a set of relating tables will work fine. However, if the nodes need to be able to handle data that is any number of levels deep then you might want to adapt a design more centered around tables that relate to themselves. Take a look here for more of an explanation.