Apologies if this trivial: Most of my experience is in Java with very little DB knowledge. I'm brand new to Hibernate and pretty new to J2EE. I've looked for an answer everywhere without much success.

Background: I'm working on a project using JBoss/Hibernate- It's main function is to collect and store data from environmental sensors. Samples are taken every minute and must be archived. Thus, we end up with lots of data to manage. Until now, each sensor's information was archived in its own table. 99% of the queries are specific to one sensor, so the organization made sense (at least to me). The fields (columns) for each of the sensors are the same, however.

Question: Looking at Hibernate, it appears (once again, at least to me) that table-per-instance approach is discouraged. I get that impression from each persisted class being mapped to one table alone. Is this the case? Is it better to place all sensor data in one table and all readings in another, then just do a key relationship between the two? My only concern is that this "readings" table would have hundreds of thousands of results relatively quickly.

Thanks! (And if I serve as flame-bait, I understand) =)
Michael

Sorry- I should clarify that we are attempting to migrate to Jboss/Hibernate, at the moment we are using a different solution.

This is the correct way to use relational technology. Relational databases are specifically designed to handle tables with many rows and can much more efficiently index large amounts of data than your application can. Think about it: why do you believe you would be able to index data more efficiently than your 20 years-of-maturity RDBMS?

I don't, nor was that my implication: I believed that perhaps I could organize the entries more intelligently based on our usage. The table mapping used closely mirrors the way we access data, allowing us to do unrestricted "select ... from table" style queries instead of having several 'where' clauses and forcing the DBMS to search through hundreds of thousands of irrelevent records.

It makes sense to me logically, though I understand that the reality of the situation may be drastically different- which is why I requested the guidance of those more enlightened than I. =)

Thanks for the reply and please bear with my ignorance on the subject, I'm very new to the field.

Well, my point is that it does not need to "search" through many irrelevant rows, it uses an index. (And that index can include multiple columns.) So getting to the right range of rows is just a couple of hops down the tree, all in memory.

At the physical level, there is no real difference between searching the catalog for a particular table, and searching a table for a range of rows, AFAICT.

I recommend Dan Tow's SQL Tuning.

Thanks for the clarification. I wasn't aware of the way db's indexed information, so that makes much more sense now. I'm working my way through college, so I'm trying to get a headstart on the learning curve associated with enterprise/J2EE development.

Good luck ... :-)