Complex Collection

[kochen]

Hi,
I have a question regarding how to persist a complex collection, or better yet, how to create a proper DB for it (that will allow easy & fast search of data within this collection).

let's assume I have a lot of products (main collection) held by a class called ProductManager.
each product belongs to a certain manufacturer.
each manufacturer belongs to a certain city (just for the example).

if we look the other way around, I have a city with many manufacturers, each producing their unique products...


now I want to be able to persist products & get them from the DB by certain cretirias (such a all products from Detroit or products created by Manufacturer_A).

is there any magic trick for this?
any suggestions on how to diagram my classes & how to maintain the DB?

[kochen]

if that was not clear, I want to prevent to loading of the entire Product Collection for each query (when only part of it is necessary).

moreover, I would like to have lazy load for this collection...
(get the city, does not load any manufacturer until needed, & when needed only a list of product IDs is loaded until I need one certain product)