I am currently working on a shop system in JBoss Seam using Hibernate as ORM.
The shop has a stock list with many articles. All these articles have a default retail price. The articles are assigned to several lists where an article can be in none, one or many lists. Finally every customer has exactly one list that is assigned to him.
In general the retail price for the customer is the retail price of the article itself. But on every list the price of each article can be overwritten, so that the price of one article can differ from the original price and the other lists that can define own prices as well. At last a price for one article of a certain list can be overwritten for a single customer.
So if I want to look up a price I have to check (in that order) if a price of the article for the customer is set, if a price of the article for the list is set and otherwise the default retail price of the article is used.
Because the ressources of the server are limited I really need to reduce overhead and database traffic. So what do you think might be the best way to realize that structure?
I thought about using a 'Price' class for the article and two inherited classes 'ListPrice' and 'UserPrice'. But if an article lists consist of a few hundred articles the lookup might result in umpteen joins and database querys. But to cache all these values might result in a very large memory consumption.
I think you are on the right lines. I think you're right about modelling them as subclasses etc. and the complexity of the joins being a drawback. Personally, I would implement ListPrice and UserPrice classes and map them via Hibernate but have no associations or hierarchical structure between them.
You can then load and cache the list prices if necessary. You're right that caching will increase the memory footprint, but this really depends on how much / what you cache. If you are just using it to look up prices then you could use the PKEY as the cache key and the price as the value. I'm not sure what other data you use or how often the prices change. If not much changes then maybe just look at having an in-memory representation of the prices as a lightweight object and use Hibernate to allow administrators to set new list prices etc (via Hibernate). Obviously this adds to the complexity in keeping the two lists synchronised, but shouldn't be too onerous.
Another option could be to denormalise the list price and hold this in each of the customer lists. This then moves the storage burden to the database layer but is also more complex in updating all the lists when the prices change.