Remember the basics of database design
Remember the basics of database design, or normalization:
- each table has a primary key
- the job of the primary key is to uniquely identify records, not to store business data; any use of business data in a primary key is a dangerous practice, since any changes to such data will have large ripple effects
- a good deal of data normalization is summed up by the memorable phrase: "a table stores facts about the key, the whole key, and nothing but the key"
- relations between tables are implemented using foreign keys, where one table refers to the primary key of another table
- almost all relations are 1..N
- 1..1 relations are permitted, but need a reason to not be a 1..N relation (this reason is usually a performance optimization)
- cross references between tables A and B are implemented by a third table C, which contains two foreign keys, one for each of the primary keys of tables A and B
- Connection pools can improve performance
- using PreparedStatement instead of Statement can improve performance as well
- fetching data with SELECT statements can often account for the great majority of activity (often 95% or more). For apps that focus on processing transactions, the relative number of SELECTs is usually lower.
See Also :
Would you use this technique?