Hey all,

this is my first post on this forum...
What I am going to describe is properly a 'classic' problem and will have a clasic solution,
I am just not sure about this so I would like to have your opinion on this:


We have a database with part_numbers, product groups, product sub-groups, vehicles names, vehicle model, division name country, year etc.. etc..


That means for each country we can lookup what vehicle in what country we have and what parts are availabe in a specific year,
I think you get the idea for such a warehouse database..

in a sense they can be ordered like this:

world -> market -> country -> year -> division -> vehicle -> vehicle model -> product -> sub-product -> part_number

Here are some examples:

world -> europe -> Germany -> 2006 -> Saab -> 9-3 -> 9-3 Saloon -> interior -> electronics -> 12345678
world -> europe -> Germany -> 2006 -> Saab -> 9-3 -> 9-3 TurboX -> exterior -> wheels -> 3424455
world -> America -> USA -> 2007 -> Saab -> 9-3 -> 9-3 TurboX -> exterior -> wheels -> 4234563

Some properties of the data:

- We do have proper primary keys for each 'object'
- Each copy/description/image can be created in different languages
- Each vehicle can appear in different years, but they are the same vehicle (same primary key)
- Each vehicle can appear in different countries, but they are the same...
- Each part can be applied to one or more vehicles under it's own product/sun product group,
however this can differ per vehicle.. so some part might not always fall under interior/electronics but might be under
exterior/electronics for an other vehicle
- I call each node type 'meta data', that is additional data that belongs to a node. This contains additional data that os specific for each node type

This is what I had in mind for it's design, it's based on Celko nested set, however I am not 100% sure if this is the correct appraoch.

My nested set would look more or less like this:


CREATE TABLE product_portfolio
(
item_id character varying(36) NOT NULL, -- UUID that identifies this node
lgt integer NOT NULL,
rgt integer NOT NULL,
level integer NOT NULL, -- Indicates what sort of object it is (division, vehicle, part number vehicle_model etc....)
ref_id character varying(36) NOT NULL, -- UUID that references a other meta data object 1-1 relation
)

One of my meta data tables could look like this
CREATE TABLE vehicles
(
item_id character varying(36) NOT NULL, -- UUID that identifies this vehicle
vehicle_name text,
vehicle_attribute text,
vehicle_weight text,
vehicle_image text
)

CREATE TABLE part_numbers
(
item_id character varying(36) NOT NULL, -- UUID that identifies this vehicle
part_description text,
part_price number,
part_image text
)


The idea is to let ref_id in the product_portfolio reference my meta data rows in other tables and that the level number tells
me what meta data type it is.
By doing this I can use the switch command in SQL to call the correct name and thus build a tree..

Something like this:

SELECT amtc.*,
CASE
WHEN level=4 THEN
(SELECT vehicle_name FROM vehicles a WHERE a.item_id = pp.item_id)
WHEN level=5 THEN
(SELECT division_name FROM divisions a WHERE a.item_id = pp.item_id)
WHEN level=6 THEN
(SELECT product_group_na,e FROM product_groups a WHERE a.item_id = pp.item_id)
WHEN level=xxx THEN
......
ELSE
'TBD'
END AS translation
FROM product_portfolio pp;

That means for each node type, I need to have a seperate table to specify the meta data for the node.



What's your thought on this, and would it be the right approach for this problem?
If you have any pointers to pages that explains this problem (with solution??) then that would be great,
since I don't think this is a unique problem...

I did read about Graphs in SQL (is this a graph?) But currently don't full understand how that would work..

thanks,
Ries

How are these relationships maintained? Is it all manually or do you have a source (3rd party) for building these relationships?

I actually built a parts DB for a large e-commerce site years ago. We based it off of a catalog from a company that was called Profit Pro at the time. There should be some industry standards that you can try to tap into rather then growing your own relational structure.

Is this an inventory DB that you are creating? Or are the countries related to where the car is manufactured?

If I remember correctly our parts DB went something like:

Year > Make > Model > Engine Size > VIN

Not sure if that helps at all.

I am not sure if I would try to relate Country > Car > Relationship > Part in the fashion you are proposing. I would make sure I can uniquely identify a car using something like what I proposed above, having 1 ID associated with that, 1 ID associated with your given part, and then have any multitude of cross-reference or junction tables relating the two to have the part fit in the car. Country just sounds like an attribute of an inventory table keyed by part. And Continents can be an attribute of your base Country table.

Hey Gagnon,

we don't go down to the VIN level, but we need to associate which part numbers fit what models and under what product group, sub product group.

The relation in our new database are going to be held together with the tb_categories ( need to re factor that table name...) which is basically a big nested set, or you can see it as a huge MM relation table.

We have to go down to each level so we can check what part fit a specific vehicle under what year, but also if a user goes to the website to 'drill down' in the system and got from division down to the part details.

The incoming data is basically flat files (can come in on a daily base) that we would load using a ETL product.

I didn't want to use Year -> Make -> Model -> Engine Size (relational whize) because a lot of vehicles would fit on the different years and in different countries. Hense the categories table.

I just never came across to a website where such a system would be explained....

Currently I am making a couple of stored procedures that can travel the tree to find correct information. And make a proof of concept...

Ries

I'm trying to picture the normal scenario when somebody comes in and says that he has a car made in year yyyy and he needs some spare part, but he doesn't know exactly the name.

The SQL that searches this seems a bit complicated for me.

I like the tree to maintain the hierarchy, and to build the full tree, but I don't know if it's the best in terms of searches.

I must admit that I'm no experienced database developer, so I might be wrong.