Database - What names should I NOT use for tables/fields/..?
There are several limitations to what names you can give to your fields/tables/views/... , and big problems if you use ones that you shouldn't be using.
Many database systems will have strict rules to what you can name items, such as:- "each name must start with a letter, and be [x] to [y] characters long"
- "a name can only contain alpahnumeric characters, and spaces"
- ...
..but there are other things that you need to be careful about too, which usually aren't made clear enough.
If you use any names which break the following guidelines, you are likely to get errors (which may have 'unrelated' messages, and/or only occur in some situations!) or a noticable loss of speed when working with the data.
Avoid spaces and other non-alphanumeric characters
Many database systems will allow you to use spaces (and punctuation, etc) in names, but doing so can cause errors if those names aren't handled in a particular way - especially within SQL statements.
It is recommended that you do not use these characters, for example use uppercase letters instead of spaces to split words (eg: instead of "order details" use "OrderDetails").
If you do use names with these characters, then sooner or later you will almost certainly forget to use the special syntax (yes, I know I haven't explained what it is ;)), and will get problems of one kind or another (perhaps without realising it).
Avoid specific prefixes
Several database systems have their own special naming prefixes (for things like system tables), and using names that start with the same characters is very likely to cause slow-down, and possibly errors too.
As a very general guide, these include things like "sys", "msys", "pg_", and "sp". Check with the documentation of your database system to see an appropriate list.
Avoid reserved words
This is something that comes up on the forums far too regularly (many times each week), as people dont realise that there is an issue until it is too late...
There are several words that already have meanings within each database system (such as Name, User, Group, Index, Date, Month, Year, ..), and using them as a name can either make your queries return the wrong results (without an error), or create seemingly random errors (basically because the database system gets confused, thinking you meant a built-in function instead of your field/table/...). Unfortunately you do not get any kind of warnings when creating objects with these names, at least not in any database system I have used!
Note that some systems will have issues if any of these words are included as part of a name with spaces around them (such as "My Date"), but then you wouldn't use a space in a name anyway, right? ;)
The list of reserved words is slightly different for each database system (and often by version too!), so you should check the help files to find them (there is usually at least one article called "Reserved Words"). To help you out, here are direct links to the relevant pages of the online help for some popular systems:
If you are using a version of one of the above systems that is earlier than those listed (say Access 97), then the next-newest one that is listed (so Access 2000) will probably be appropriate.. except in the case of SQL Server, in which case any version earlier than 2000 is basically a different product!
If in any doubt, check the documentation that comes with your installation.
If any of the above links fail, please tell me via PM
On a final note... if you want to get fancy, you can use code to find out what the provider-specific reserved words are for the system you are connected to, eg:
VB Code:
'Classic VB code for ADODB - open a Recordset with one column, which contains the reserved words (see warning below!)
Set objRS = objConn.OpenSchema(adSchemaDBInfoKeywords)
'VB.Net code for ADO.Net - open a DataTable with one column, which contains the reserved words (see warning below!)
Dim table As DataTable = connection.GetSchema("ReservedWords");
Note that these do not include words that are reserved by the connection technology (eg: OLEDB) itself - such as obvious ones like "Date"!
Shameless plug/useful code.. here is a Classic VB function I wrote using this method, which checks the names of tables/fields/views in a database to see if they are reserved words - including all of the OLEDB/ODBC reserved words too!
