Lookup between lists with additional data

Question

I have two entities, Toolbox and Tool, where Toolbox contains a set of tools (hammer, saw, screwdriver).
The trick is that there can be several hammers in the same toolbox, so I need an amount, or quantity.
All in all the overlay looks like this:
Here my toolbox contains one Drill and two hammers.

Although SharePoint lists are far far from any database, one could imagine it like this:
Here ToolboxTool is a "joining table" connecting the two entities, with the additional information about Quantity. Of course in a normal database one could justify quantity by creating duplicates of ToolboxTool instances, but this is perhaps more SharePoint-like.

My actual question

What is the perfect way to setup something like this in SharePoint?
Several times I have faced similar problems when defining Lists, but I have never taken the step to find the perfect solution, which is why I ask here now.

Some virtual restrictions:

• I'm looking for OOTB solutions, but any suggestions are more than welcome.
• Does not have to be bound to foundation, but non-enterprise answers are preferred.
• OOTB presentation (e.g., XsltListViewWebPart) should be part of the consideration.

Answer 1

By looking at your reputation, I am not sure if you not ever heard about lookup columns as explained in this MSDN article they are doing exactly what you want but only difference is they are playing with bunch of bananas and your with tools ;)

Hope it helps too :)

EDIT

Since your looking for XsltListViewWebPart you can use CAML Joins and Projections as explained in book SharePoint 2010 as Development Platform

Answer 2

im a bit confused asto what your trying to achive? Why create so many lists to make the relationships when you have sql? surely this can all be done with three tables in you database? one table is the toolbox another tools and the last is the connection between the two. From there you can use what you want be it list or webpart(i think is best) to display as you need, You can even add/ammend or remove from the list.

Sharepoint should be the one that displays the information and sql is the one that contains the relationship to the data. You pull out the data from the SQl DB using stored procedure that will show up as information in the webpart ;).

Its no OOB feature but its far far better solution and wouldnt take long atall infact could be done in couple of hours if that! SQL is already there and is specifically designed for this task.

hope it helps :)