For a parent-child database table relationship – could retrieving the nth child row be used?
I had a project comprising a main table with an associated secondary table.
Similar in style to a manufacturers parent table with associated products child table.
In this particular example I knew that there would only be two child entries associated with the parent table.
Interested in taking a different approach I wanted to retrieve a data row from the child table, knowing the parent table reference and the row number, the nth row from the database table.
In my project there would be control over the order of the entries in the child table. Derived from a series of forms the child entries would be in a defined order.
I chose to experiment with the idea that I could select the child entries by row number, rather than adding another controlling field.
Below is shown the outline of the stored procedure to get the data from the two tables.
CREATE PROCEDURE GetProduct @ManufacturerID [int], @ProductID [int] ASWITH tblProducts AS ( SELECT tblProducts.*, ROW_NUMBER() OVER (ORDER BY ProductID) AS 'RowNumber' FROM tblProducts WHERE ManufacturerID + @ManufacturerID ) SELECT * FROM tblProducts WHERE RowNumber = @RowNumber
My experiment worked and I was able to select the right entry from the secondary table.
I was concerned that with this implementation apart from the row number there was nothing to define what a row was.
Adding another field to define the child entry type was subsequently chosen as a better approach.
And adding this type of definition removed the whole point of retrieval by row number.
Considering things which could go wrong:
- What if the rows did get entered back to front?
- What if there was an error on save, maybe there were 3 rows?
As the project stood only the two child table rows would exist. But it’s not robust and designed for future development. Whilst it’s not possible to design for every eventuality potential hindrances should be avoided.
My conclusion, was that it is better to have an additional controlling field which can be used.