I'm trying to figure out how to make the logic for my parent/child relationship work and need some help. I'm dealing with bicycle parts to give you an idea of the data.

I think I'm going to have to give some overview of how I have designed part of the sytem I'm talking about. What I have done is created a Product table that has all the fields common to all products in the system. I then created a ProductVersion table that contains the fields common to all versions of a product in the system. So for any product there will be one record in the Product table then for say different colors of this product there would be a record for each color in the ProductVersion table. This allows different colors of a product to have different prices, weights and so on.

I then have a ProductSpecific table say Frame, Fork etc that has a many-to-one association with the Product table and a one-to-one association to the ProductVersion table. This three table design makes up a unique product.

What I have done now is to create a DefaultPart the table you see below.

CREATE TABLE DefaultPart ( 
    ParentVersionID int NOT NULL,
    ParentProductID int NOT NULL,
    ParentTypeID int NOT NULL,
    ChildVersionID int NOT NULL,
    ChildTypeID int NOT NULL,
    Price smallmoney DEFAULT (0) NOT NULL,
    DefaultType char(3) DEFAULT ('OEM') NOT NULL
)
GO

ALTER TABLE DefaultPart
ADD CONSTRAINT CHK_DefaultType CHECK (DefaultType IN ('INT', 'REC', 'OEM'))
GO

ALTER TABLE DefaultPart ADD CONSTRAINT PK_DefaultPart 
    PRIMARY KEY (ParentVersionID, ParentProductID, ChildVersionID)
GO

ALTER TABLE DefaultPart ADD CONSTRAINT FK_DefaultPart_ChildVersion 
    FOREIGN KEY (ChildVersionID) REFERENCES ProductVersion (ProductVersionID)
GO

ALTER TABLE DefaultPart ADD CONSTRAINT FK_DefaultPart_ParentProduct 
    FOREIGN KEY (ParentProductID) REFERENCES Product (ProductID)
GO

ALTER TABLE DefaultPart ADD CONSTRAINT FK_DefaultPart_ParentVersion 
    FOREIGN KEY (ParentVersionID) REFERENCES ProductVersion (ProductVersionID)
GO

ALTER TABLE DefaultPart ADD CONSTRAINT FK_DefaultPartChild_ProductType 
    FOREIGN KEY (ChildTypeID) REFERENCES ProductType (ProductTypeID)
GO

ALTER TABLE DefaultPart ADD CONSTRAINT FK_DefaultPartParent_ProductType 
    FOREIGN KEY (ParentTypeID) REFERENCES ProductType (ProductTypeID)
GO

Now the problem I am having is that I want to associate a fork to a frame and in some cases a specific color of frame to a specific color of fork. What I am doing is adding the frame ProductVersion.ProductVersionID to the DefaultPart.ParentVersionID column and then the ProductID (the same for all versions) to the ParentProductID column. I then add the fork ProductVersion.ProductVersionID to the DefaultPart.ChildVersionID column. I will then repeat this process again to add a second fork to the same frame, this gives me data that looks like the following.

ParentVersionID ParentProductID ChildVersionID  DefaultType
2444                   1710               4810             REC
2444                   1710               4811             OEM

Now using the data that I entered above it would allow me to write a query that returns forks 4810 and 4811 for frame 1710. Because the frame id 1710 is the frame ProductID (the generic model id) this would allow all versions of the frame to use the same two forks here. This solved the first requirment of a frame/fork association. Associate fork(s) to all versions of a frame without having to add all versions into the DefaultPart table.

Now the second requirment was to override the first requirment by associating a specific version of frame to a specific version of fork. So I add two more records into this so the data now looks like the following.

ParentVersionID ParentProductID ChildVersionID  DefaultType
2444        1710        4810        REC
2444        1710        4811        OEM
2458        1710        4814        REC
2458        1710        4815        OEM

When the frame 2458/1710 it should only get forks 4814 and 4815. So this is a case that should override the first. I can't figure out how to make this case override the first case. My rule for this should be something like "Always look for the generic ProductID association first and get the forks, but if there is a ProductVersionID/ProductID association don't get the first set of first, but instead this the second set of forks".

I am really having a hard time solving this problem and would love some help.

Excuse me If I don't understand it correctly, but wouldn't this be solved by a 2-way query? -> first execute a query which fetches the default set, with a filter which tests if there are multiple versions, and which should not return any value if there are multiple versions, then execute a query IF that query doesn't returns any values.

You can also do something with sorting I think: sort on the version number, descending. Do that in a subquery, and use that value as a filter on the product version: you then either get the default version (as there is then just 1 version stored) or the latest version.

Revoking my question... Otis already suggested it.

Otis wrote:

Excuse me If I don't understand it correctly, but wouldn't this be solved by a 2-way query? -> first execute a query which fetches the default set, with a filter which tests if there are multiple versions, and which should not return any value if there are multiple versions, then execute a query IF that query doesn't returns any values.

You can also do something with sorting I think: sort on the version number, descending. Do that in a subquery, and use that value as a filter on the product version: you then either get the default version (as there is then just 1 version stored) or the latest version.

I think you do understand the problem correctly. I am not sure how to do what you have suggested though. In your first solution using the 2-way query does this mean I have to make two round trips to the database to do this? I'm not sure how to do this test of multiple version and then not return anything if there are.

In the second method you suggest there will usually be one to three versions returned. Will this still work?

So what I have so far for a query is this one below which uses both the ParentVersionID and ParentProductID in a subquery and returns the first two records becaues the ParentVersionID is used.

SELECT
    [WS2].[dbo].[Product].[ProductID],
    [WS2].[dbo].[ProductVersion].[ProductVersionID],
    [WS2].[dbo].[Product].[Model]
FROM
    [WS2].[dbo].[Product]
INNER JOIN
    [WS2].[dbo].[ProductVersion] ON [WS2].[dbo].[Product].ProductID = [WS2].[dbo].[ProductVersion].ProductID
WHERE
    [WS2].[dbo].[ProductVersion].[ProductVersionID] IN
    (
        SELECT [WS2].[dbo].[DefaultPart].[ChildVersionID]
        FROM [WS2].[dbo].[DefaultPart]
        WHERE
        (
            [WS2].[dbo].[DefaultPart].[ParentProductID] = 1710
            AND
            [WS2].[dbo].[DefaultPart].[ParentVersionID] = 2444
        )
    )

Results in the following:

ParentVersionID ParentProductID ChildVersionID  DefaultType
2444        1710        4810        REC
2444        1710        4811        OEM

In this case the results are correct because these two forks are the two that I want to use for the generic result set for this frame. Now if I pass the ProductVersionID 2450 instead of 2444 (2450 is another version of this frame that I want to use with the two results from above) then this querey wouldn't work.

Then if I passed the ProductVersionID of 2458 in this query it would return the results below and give me what I want. This is a case where I want this specific frame/fork combo to override the one above.

ParentVersionID ParentProductID ChildVersionID  DefaultType
2458        1710        4814        REC
2458        1710        4815        OEM

Now what do I need to do from here. Your suggestion to me sounded like the first was more likely to give me what I want, but at the expense of needing two trips to the database. Would you please explain the second solution more?

In this system I have a number of different places where query will need to try and select a generic option which can be overridden by a more specific option. Solving this problem will be very helpful.

tprohas wrote:

Otis wrote:

Excuse me If I don't understand it correctly, but wouldn't this be solved by a 2-way query? -> first execute a query which fetches the default set, with a filter which tests if there are multiple versions, and which should not return any value if there are multiple versions, then execute a query IF that query doesn't returns any values.

You can also do something with sorting I think: sort on the version number, descending. Do that in a subquery, and use that value as a filter on the product version: you then either get the default version (as there is then just 1 version stored) or the latest version.

I think you do understand the problem correctly. I am not sure how to do what you have suggested though. In your first solution using the 2-way query does this mean I have to make two round trips to the database to do this? I'm not sure how to do this test of multiple version and then not return anything if there are.

Yes 2 queries.

In the second method you suggest there will usually be one to three versions returned. Will this still work?

So what I have so far for a query is this one below which uses both the ParentVersionID and ParentProductID in a subquery and returns the first two records becaues the ParentVersionID is used.

SELECT
    [WS2].[dbo].[Product].[ProductID],
    [WS2].[dbo].[ProductVersion].[ProductVersionID],
    [WS2].[dbo].[Product].[Model]
FROM
    [WS2].[dbo].[Product]
INNER JOIN
    [WS2].[dbo].[ProductVersion] ON [WS2].[dbo].[Product].ProductID = [WS2].[dbo].[ProductVersion].ProductID
WHERE
    [WS2].[dbo].[ProductVersion].[ProductVersionID] IN
    (
        SELECT [WS2].[dbo].[DefaultPart].[ChildVersionID]
        FROM [WS2].[dbo].[DefaultPart]
        WHERE
        (
            [WS2].[dbo].[DefaultPart].[ParentProductID] = 1710
            AND
            [WS2].[dbo].[DefaultPart].[ParentVersionID] = 2444
        )
    )

Results in the following:

ParentVersionID ParentProductID ChildVersionID  DefaultType
2444        1710        4810        REC
2444        1710        4811        OEM

In this case the results are correct because these two forks are the two that I want to use for the generic result set for this frame. Now if I pass the ProductVersionID 2450 instead of 2444 (2450 is another version of this frame that I want to use with the two results from above) then this querey wouldn't work.

Then if I passed the ProductVersionID of 2458 in this query it would return the results below and give me what I want. This is a case where I want this specific frame/fork combo to override the one above.

ParentVersionID ParentProductID ChildVersionID  DefaultType
2458        1710        4814        REC
2458        1710        4815        OEM

Now what do I need to do from here. Your suggestion to me sounded like the first was more likely to give me what I want, but at the expense of needing two trips to the database. Would you please explain the second solution more?

The second is also very doable, and probably better

In this system I have a number of different places where query will need to try and select a generic option which can be overridden by a more specific option. Solving this problem will be very helpful.

Re-reading it, I'm a bit lost and I wonder if I have suggested the right answer, and I also have the feeling it somehow is very simple but we're looking in the wrong direction.

Ok, what I meant with the second option was something like this:

SELECT  *
FROM    DefaultPart
WHERE   ParentVersionID = 
(
    SELECT  TOP 1 ParentVersionID
    FROM    DefaultPart D
    WHERE   D.ParentProductID = DefaultPart.ParentProductID
    ORDER BY ParentVersionID DESC
)

THis always gives the latest version. I thought you meant something like this. If there's just 1 version (1 parentversionid, this 'parent' thing confuses me) it will return that version. If there's a newer version, it will return that version.

Re-reading it, I'm a bit lost and I wonder if I have suggested the right answer, and I also have the feeling it somehow is very simple but we're looking in the wrong direction.

I think this statment is probably true. I'm not sure now if you do understand it exactly, but your very close and once understood probably is a fairly simple solution.

Ok, what I meant with the second option was something like this:

SELECT  *
FROM    DefaultPart
WHERE   ParentVersionID = 
(
    SELECT  TOP 1 ParentVersionID
    FROM    DefaultPart D
    WHERE   D.ParentProductID = DefaultPart.ParentProductID
    ORDER BY ParentVersionID DESC
)

THis always gives the latest version. I thought you meant something like this. If there's just 1 version (1 parentversionid, this 'parent' thing confuses me) it will return that version. If there's a newer version, it will return that version.

This query you have leaves out part of my problem. This will always return only the results for the top ParentVersionID. This is a problem because the frame/Parent could change which would cause the ParentVersionID and the ParentProductID to have completely different child parts. I think that at the least the ParentProductID has to be passed in as a filter parameter to make this work because what I am looking for is to get the child parts for a parent (ParentProductID) or the child parts for a specific parent (ParentVersionID). Does this make the problem more understandable? To give you a little more information, the parent part in this case is a frame, but it could be any part that has child parts associated to it.

tprohas wrote:

Ok, what I meant with the second option was something like this:

SELECT  *
FROM    DefaultPart
WHERE   ParentVersionID = 
(
    SELECT  TOP 1 ParentVersionID
    FROM    DefaultPart D
    WHERE   D.ParentProductID = DefaultPart.ParentProductID
    ORDER BY ParentVersionID DESC
)

THis always gives the latest version. I thought you meant something like this. If there's just 1 version (1 parentversionid, this 'parent' thing confuses me) it will return that version. If there's a newer version, it will return that version.

This query you have leaves out part of my problem. This will always return only the results for the top ParentVersionID. This is a problem because the frame/Parent could change which would cause the ParentVersionID and the ParentProductID to have completely different child parts. I think that at the least the ParentProductID has to be passed in as a filter parameter to make this work because what I am looking for is to get the child parts for a parent (ParentProductID) or the child parts for a specific parent (ParentVersionID). Does this make the problem more understandable? To give you a little more information, the parent part in this case is a frame, but it could be any part that has child parts associated to it.

Yes you probably have to filter, my example returns for every part its latest version. So if you specify the parentproductid as a filter (can be in the subquery or in the normal query) it should give you the proper results.

Does it return the right results when you pass that extra filter? If not, could you provide a simple SQL query which does work and give you the right data?

Otis wrote:

tprohas wrote:

Ok, what I meant with the second option was something like this:

SELECT  *
FROM    DefaultPart
WHERE   ParentVersionID = 
(
    SELECT  TOP 1 ParentVersionID
    FROM    DefaultPart D
    WHERE   D.ParentProductID = DefaultPart.ParentProductID
    ORDER BY ParentVersionID DESC
)

THis always gives the latest version. I thought you meant something like this. If there's just 1 version (1 parentversionid, this 'parent' thing confuses me) it will return that version. If there's a newer version, it will return that version.

This query you have leaves out part of my problem. This will always return only the results for the top ParentVersionID. This is a problem because the frame/Parent could change which would cause the ParentVersionID and the ParentProductID to have completely different child parts. I think that at the least the ParentProductID has to be passed in as a filter parameter to make this work because what I am looking for is to get the child parts for a parent (ParentProductID) or the child parts for a specific parent (ParentVersionID). Does this make the problem more understandable? To give you a little more information, the parent part in this case is a frame, but it could be any part that has child parts associated to it.

Yes you probably have to filter, my example returns for every part its latest version. So if you specify the parentproductid as a filter (can be in the subquery or in the normal query) it should give you the proper results.

Does it return the right results when you pass that extra filter? If not, could you provide a simple SQL query which does work and give you the right data?

The query you gave me above won't ever return records for the ParentVersionID of 2444. I'm really stuck trying to figure out how to make this work because in one case I want to get all the records for just the ParentProductID and not where the record is specific to a parent version. Lets say there was another column I could filter on like IsVersionSpecific. I would then query/filter on the ParentProductID (gives me back all the records) and where IsVersionSpecific is false (removes version specific results). Then in the other case I would query using ParentProductID, ParentProductVersion and IsVesionSpecific is true. The trouble is that I can't figure out how to make both queries work together to get the results I want. I keep thinking that the only way to solve this is to just query all the child parts for a parent and then deal with the results in code some how. I wanted to do this on the database side because it would give better performance.

I know I am going to have a similer problem where I have a Product which can have product blurbs (info/description of product). This is another situation where I have a generic blurb that goes with all versions of a product, but then in some cases there will be a product version specific blurb that has to override the generic blurb. I can't see how to make this work.

I must say, I'm lost.

I've to re-read everything to understand what the real problem is. What I see is a table with versioning in the PK, so sorting on that version number would grab the latest version (IMHO). BUt that's probably wrong, so I can't grasp how to formulate the resultset in terms of 'these are the filters on the total set', as I can't define these filters...

This is interesting because you just said something that made me realize my use of the word PrdouctVersion or ProductVersionID may easily be mis-understood. What I mean by ProductVersion is that this is a unique instance of a product like a specific color or price. I think when most people see the word version they think of version as in current version or release version. This is an understandable problem as I have used a common term in a different way. This is why sorting on version in the solution you gave me doesn't help me. Does this make more sense now?

Otis,

I think I might want to start a new thread describing the problem in a different way, or should I just continue this one. I think I might be able to explain the problem in a different way that will simplify it.

That would be great I now better understand it also, 'version' isn't a v1.2, or 2.0, but simply a different erm.. version!

Otis wrote:

That would be great I now better understand it also, 'version' isn't a v1.2, or 2.0, but simply a different erm.. version!

Seems clear as mud now, doesn't it.

So, are you saying a productVersion is like an inventory item?

For example, I have:

InventoryItems, and each record describes an item that can be in inventory.

Then I have,

InventoryVersion, and each record describes a physical unit that I have in stock. Perhaps with a FK to InventoryItems and a unique Serial Number ???

Is ProductVersion like Serial Number?

BOb

Yes, exactly. ProductVersion is like a serial number, though we are using DistributorNumber as the unique identifier. Does this help you understand the problem better?