Database naming conventions are largely a matter of personal preference. For more opinions see Database Design Guide, Database, Table and Column Naming Conventions?, and Database Table and Field Naming Suggestions.

The important point is that a project must have consistent naming rules across the development team. It's good practice to have a naming standard for databases, similar to the way you have a coding guidelines. If you are not working a greenfield project, which tends to a lot of the time, it's best to follow the naming rules already in use for the particular project.

With that out of the way, here are my own preferences, largely influenced by coding standards that I use (Zend Framework's coding standards):

1. Table names.
   If you using some form of the active record pattern, then tables usually map to a class in code. Tables should be named using upper camel case e.g. Users or CarModels rather than User or CarModel. Prefixes can be added to the table name, separated from it by an underscore '_', e.g. D6_Users, or Catalogue_Products. The prefix might be denoting a module in which the table is used.
   Association tables should be named as <table1>2<table2> or Lookup<table1>2<table2> e.g. Users2Files or LookupUsers2Files for a User to File association table.

   An alternative, especially if your database does not support uppercase in table names, is to lowercase the name, and separate words using the '_' e.g. car_models or lookup_users_2_files.

2. Table fields
   Table fields should be named in lower camel case (except for ID or UID). Again, if you are using some form of the active record pattern, you can access the field as e.g. $user->lastName rather than $user->last_name ($user is PHP notation). $user->lastName corresponds to how I name class attributes - using lower camel case.
3. Primary fields
   All tables should have an auto-incrementing ID (or UID) column. This will be the primary key for all references to a particular record. The exception is a feature-less joining table whose primary key is a compound of it's columns. If a join table must have columns other than the reference keys for the tables in the join, then it must have an ID (or UID) column. The reference keys then become a compound unique index.
4. Foreign keys
   If you are using a database engine which supports referential entegrity, adding applicable foreign keys is a good idea. Foreign keys should be named in two parts: <table name>_<field name>. e.g. to reference an ID field in a Categories table, the foreign key field in Products would be Categories_ID.