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
Database performance:
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
SELECT
s is usually lower.
See Also :