File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Database Design Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Database Design" Watch "Database Design" New topic

Database Design

Andrew Shafer
Ranch Hand

Joined: Jan 19, 2001
Posts: 338

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?

Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
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.
[This message has been edited by Daniel Dunleavy (edited May 16, 2001).]
subject: Database Design
It's not a secret anymore!