This Howto is NOT meant as a complete guide of how to design a database. On the contrary, it is meant as a kind of addon, in the sense of "If I know in general how to design a databasein general, what do I have to keep in mind if I use Torque to access that database"
There are some column names which you can not use in Torque although your database would support them. These are any column name that contains characters that are not in Java's variable identifier character set. The reason is that column names are used as variable names in the OM Peer classes and these columns will cause the Torque generated code to not compile.
Note however, that SQL92 standard and up uses the same identifier characters as Java for non-delimited columns. So this should only apply to columns defined by the SQL standard as delimited columns, i.e. columns referred to surrounded by double quotes. Delimited column are considered to be case sensitive and/or contain non-standard characters. However, most good cross DB server designs should avoid these special types of column since some servers don't support delimited columns.
In addition, there are two column names that are handled slighly differently in the OM Peer classes. This is so they will not produce constants twice in the generated code. The following column names (case is ignored) will have an "_" prefixed in front of them in the Peer classes:
Note that prior to Release 3.3, using these two column names WILL produce duplicate constants and uncompilable code.
Furthermore, it is recommended that you do not use words which have a defined meaning in SQL as column names. Even if you can trick your database into using them, it is not sure whether Torque can do the same. And besides, even if it works for one database, if you ever decide to use another database, you may run into trouble then.
These design considerations apply to the most common uses of a database. In some cases where you have to meet very special challanges, there will be reasons not to follow the advice given here. But in general, you will be fine if you follow these guidelines.
For every table, you should create a primary key which has no meaning in real life. The reasons for this are:
You should use a primary key at all because it creates a well-defined link between the objects in the database and the objects in memory. Often, one has to decide whether a java object in memory describes "the same" object as a certain row in the database. For example, if you read an object from a database, change a field value and write it again to the database, you would usually want to update the row you read from. This is only possible if Torque can find the row in the database from which the object originated. For this, the primary key is used in relational databases. If two java objects have the same primary key, they describe "the same thing" and refer to the same row in the database. If you do not have a primary key, there is no well-defined way to decide if two java objects describe "the same thing". You might run into not being able to update an object.
Now that we know why we want to have a primary key at all, why should it have no meaning in real life ? This can be explained best by an example. Consider a table which holds manufactured parts. Each part has an unique serial number. So it is tempting to use the serial number as a primary key. But now imagine that we have registered the wrong serial number for a certain part in the database. Remember that the primary key is used to decide "is it the same object?" So we cannot change the serial number of a specified object without making it another object.
In Torque, this problem manifests itself in that there is no easy way to change the primary key of an object; you must trick Torque into it by using Torque's internals. This should be avoided if possible. If you use an additional primary key which has no meaning in real life, you do not run into that trouble.