(Part of PortableSql)
By this I mean:
- creating, altering, deleting tables and other database objects
- doing queries to find out which objects exist.
These activities tend to be very database-specific. Although the 'create table' statement is everywhere, specific datatypes, constraints, and so on tend to be different.
The major good practice I've found:
- Define your tables in XML and write code to automatically generate SQL scripts from them. This allows you to maintain your table definitions in one place and adjust output for each database you port to. (Note that you can also generate VBA scripts for Access or other types of scripts for other systems where SQL is not available or is not the preferred way to maintain the database.)
Can anyone post a URL to a DTD/XMLSchema and perhaps some XSLT for doing this (or the DTD itself)? This sounds like a great idea, but if one already exists, it would silly to create my own
- One can also use this technique for AutomatedCodeGeneration, creating methods for querying by keys, updating rows, handling child tables, etc. However, this can lead to brittleness, as any minor change to the schema may require a complete rebuild of the entire system, and may break code that is too dependent upon generated features.
Some other points:
-
Use PortableSqlDataTypes whenever possible.
-
Prefer varchar to char.
-
Set standards for the length of varchar columns.
-
Name your indexes. Also name your constraints, triggers, and anything else to which you can attach a name--it makes it easier to drop or alter all these things.
-
Prefix each index name with name of the table. For many database this doesn't matter, but Oracle puts all of a user's indexes in the same namespace.