Storing object relations and searching them VB2008 SQL2005

[DemonMade]

I am working on a database to store product information in VB.Net 2008 Express and Microsoft SQL Server 2005 Express. The questions I have regard how to store the relations between products contained within other products. I have an example below:



Here, there is a Hardware pack, part number 125. It consists of 4 bolts, 8 washers, 4 nuts, the bag itself and the sticker put on the bag to identify it. Is there a better way to store these relations? I am unaware of any ways to store a collection or matrix of unrestricted size within a single database cell.

Also, to prevent lengthy searches, is there a way to structure an SQL query such that it stops on the first line that does not meet search criteria after a line that does meet search criteria? As an example, refer again to the image above. Product 125, and all others, will always, with an acceptably small margin of error, be listed in a concurrent block. If I search for the contents of product 125, I want it to stop when it sees product 126, because there will be no more listings for product 125.

Last but not least, is there a way when inserting into a database to specify where you want the row inserted rather than always being inserted at the bottom?

Thank you,

Dan

[techgnome]

Last two questions first... no and no.... it's just the way DBMSs work, they store the info in the manner they see as being the most optimal for retrieval.... you could create a clustered index on the identifying field (just don't make it a text field, use a numeric field if you can help it)... that will physically group like records together.... that's about as close as you are going to get for that. It does slow down data inserts though (which may or may not be noticable)... so be careful. But it will significantly increase searches, especially if the clustered field is also the field you do a lot of look ups on.

Something else you can do, is jsut simply adding an index will speed up searches.

I had an idea for structure, but then realized it leads to repeated data, which isn't necessarily a good thing... so I'll rethink it first..

-tg