We put common prefixes on related tables to assure they display next to each other in our DB management software (Toad, Enterprise Manager, etc).
So for example, all user tables start with the word User:
Ideally, in honor of the three great virtues of a programmer these tables should be named User, Event, Purchase respectively to save some typing, agreed?
Is this naming convention the best (only?) practice for grouping related tables together naturally?
I would use this notation i.e "UserEvent" in case this table is a representation of many-to-many relation between "User" and "Event" tables.
I personally favor naming conditions similar to what you have listed there, as it is a very logical pattern, they are organized in a way that you can easily find them, and overall the extra bits of typing typically doesn't cause many issues or reductions in time.
I think it depends on how if/how you plan to expand upon your data model. For instance, what if you decided that you not only want to have User Events, but also, Account Events, or Login Events, and User, Accounts, and Logins are all different types of entities, but they share some Events. In that case, you would might want a normalized database model:
I tend to go against the grain in naming conventions on two counts here...
I don't like using prefixes so that things group together in a given UI. To me the tables should be named such that in code they are easily readable and make sense. There have been numerous studies (mostly ignored by programmers) that show that things like using underscores, logical names, eliminating abbreviations, and eliminating things like prefixes have a very big impact on both comprehension and the speed of working with code. Also, if you use prefixes to break down tables what do you do when a table is used by multiple areas - or even worse it starts out in only one area but later starts to be used in another area. Do you now have to rename the table?
I almost completely ignore name lengths. I'm much more concerned about readability and understandability than I am about taking 1/10th of a second longer to type a column or table name. When you also keep in mind all of the time wasted trying to remember if you abbreviated "number" as "no", "num" or "nbr" and keep in mind the use of things like Intellisense, using longer, more meaningful names is a no brainer to me.
With those things in mind, if your UserEvents table really has to do with events related to a user then that name makes perfect sense. Using just Events would end up giving you a poorly named table because the name isn't clear enough in my opinion.
Hope this helps!
I wouldn't use a naming convention for purposes of alphabetizing table names. It's nice when it works out that way, but this shouldn't be by design.
Read Joe Celko's book "SQL Programming Style." His first chapter in that book is about naming conventions, guided by the ISO 11179 standard for metadata naming. One of his recommendations is to avoid unnecessary prefixes in your naming convention.
I guess that the naming convention in SQL is or at least should follow the same guidelines as used in other programming languages. I have always preferred variable names / class name that clearly identified the purpose and that usually means more typing. I think the importent thing to remember is that code is only written once but read a lot of times, so clarity is vital. The last 4 or 5 years I have noticed that the variable names have grown from "eDS" to "employeeDataSet" and I think that IntelliSense is the main reason for that. I think we will see the same happen in SQL since Red Gate SQL Prompt and now MS SQL2008 have introduced Intellisense into the mainstrem database world.
I also would avoid using prefixes in order to get meaning out of an alphabetically ordered list. It's generally bad data management to use names that way. If tables are closely related, then the software that manages the data model should be able to model relationships between tables, regardless of how the tables are named.
A name like UserEvents should be used when the content of each row describes a relationship between a User and an Event. If I saw such a table in a database that was new to me, I'd expect to see a primary key in this table made up of two foreign keys, one to the Users table and one to the Events table.
If I saw something different, I would have to slow down until I understood the designer's convention.
This last point raises some questions: home many new people will be coming up to speed on the database? What documentation will those people have available? How important are those new people to the success of the project that ijncludes the database, or the success of the database designer?