My question really isn't Java specific but I couldn't think of a better place to discuss it so here goes. Let me give you a quick idea of what I'm working on and then my basic questions and known options. Then any advice or comments would be appreciated. The application (front end is JSP) must store and retrieve information about chemical thermodynamics and kinetics. The database has chemical species, each of which has physical properties (entropy, enthalpy, heat capacity). The current design has a species index table and then a seperate table for physical properties for each species. (Entropy, enthaply and heat capacity are calculated by fitting 7 coefficients to a temperature range so different coefficients are used for different ranges of temp.) Now, the database also contains information about reactions. Reactions can be of the form A -> B + C, A + B -> C, A+B -> C+D, or A->B. Each reaction has thermodynamic and kinetic information. The current design has an index of the reactions (which references the index of species) and then a seperate table for the information on each reaction. There might be 100 different entries for the reaction based on temperature, experiment, theory and semi-empirical calculations. The tables for species physical properties and reaction kinetics are generated automatically when a new species or reaction is entered into the respective index. The Db is still in design phase. Eventually the species will number in the thousands and the reactions will be at least 100 times that. Does anyone have any insight or suggestions for designing the DB for long term scalability and maintainability? Everything is all in on big DB with many tables, I was considering seperating the indexes into a seperate DB and put the physical properties and kinetic info in seperate DB, does this seem like a good idea? I have tried to follow the rules of normalization in the current design and believe that has been done fairly well. I want to have the best possible design going in. Any ideas?
Just remember that with very large databases you usually have to start denormalizing to increase performance. This is the last step to be taken. Sometimes I see people too strict with normalization. Dan [This message has been edited by Daniel Dunleavy (edited May 16, 2001).]