Naming Conventions Confusion

[dee]

I am creating a database and trying to stick to standard naming conventions,
using prefixes such as tbl frm, etc.

I have come across two definitions of lookup tables - tlkp and tlu - and am
not sure which to use.

I also see tjx for join table and ttbl for title table, but am unclear as to
what these do, especially the title table.

Any guidance would be appreciated!

 

[Jeff Boyce]

Dee

There is no "right" answer. You are looking at naming "conventions", so you
may end up deciding what approach you want to use. Stick with it after you
decide!

The prefixes don't do anything for Access... they are intended to make your
understanding (and that of those who pick up your database/code later)
greater.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mray29]

Congrats on using naming conventions! In my view tbl is a sufficient prefix
for a table name. I don't think it's necessary to have table types in the
prefix. Tables only have one function, to hold data, so all tables are
essentially equal. If you do decidd to use special naming conventions for
lookup tables, etc., as long as you're consistent it doesn't matter which one
you pick.

 

[dee]

Thank you both for your input.

I get it now!

BTW, what the heck is a Title Table?

 

[Michael Gramelspacher]

I am creating a database and trying to stick to standard naming conventions,
using prefixes such as tbl frm, etc.

I have come across two definitions of lookup tables - tlkp and tlu - and am
not sure which to use.

I also see tjx for join table and ttbl for title table, but am unclear as to
what these do, especially the title table.

Any guidance would be appreciated!

Do yourself a really big favor and drop the prefix altogether from table names.

 

[Jeff Boyce]

I've never heard of one...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rick Brandt]

Do yourself a really big favor and drop the prefix altogether from table
names.

Agreed. I abhor type-prefix naming conventions.

 

[Jamie Collins]

Tables only have one function, to hold data, so all tables are
essentially equal.

I agree. SQL has but one data structure: the table. So why does anyone
need to give it a prefix?

One issue with the Leszynski naming convention (http://
en.wikipedia.org/wiki/Leszynski_naming_convention) is that there are
(at least on some level) two types of table: a base table and a
virtual table, the latter being a VIEW i.e. a stored Query object
comprising a non-parameterized query that returns a resultset; a
parameterized one or 'action Query' is a PROCEDURE. With the LNC, base
tables get a tbl- whereas both VIEWs and PROCs get qry- i.e. can't
tell which qry- objects are logical tables. Sometimes I want to 'hide'
a base table and expose it only via VIEWs -- say, it has some columns
which should only be exposed to certain user groups rather than PUBLIC
-- and a qry- prefix would give the game away. Sometimes a table
starts out as 'public' and only later goes 'private' at which point I
want to change the name of the base table and create a VIEW using the
base table's former name; under LNC you'd have to rename from tbl- to
qry- in all the SQL queries and statements in all the applications
that use the table (a.k.a. breaking compatibility).

I've been told by a regular in these groups that the tbl- and qry-
prefixes are merely to tell an Access developer whether to look on the
Tables tab or the Queries tab respectively in the Access environment.
If that is the case, I'm not sure why so many Access developers think
it is worth the bother. I rather suspect it is a desire to emulate
their peers i.e. self-perpetuating; I've even heard someone say, "Call
themselves an Access developer? Why, they don't even prefix their
tables with tbl-!!"

Jamie.

--

 

[Jamie Collins]

Congrats on using naming conventions! In my view tbl is a sufficient prefix
for a table name. I don't think it's necessary to have table types in the
prefix. Tables only have one function, to hold data, so all tables are
essentially equal. If you do decidd to use special naming conventions for
lookup tables, etc., as long as you're consistent it doesn't matter which one
you pick.

FWIW see the "Level 2 tags" in the following MSDEN article:

Naming Conventions for Microsoft Access
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

Jamie.

--

 

[dee]

Well, thanks for all of your input. Now I'm TOTALLY confused!

I know for myself that when I have used code or in various situations where
I wanted to know what the "entity", I had wished I knew by a prefix.

So, maybe I will just use the simple one and leave it at that.

I will need to look at the links suggested and do some more research!

Thanks again!

 

[Michael Gramelspacher]

Well, thanks for all of your input. Now I'm TOTALLY confused!

I know for myself that when I have used code or in various situations where
I wanted to know what the "entity", I had wished I knew by a prefix.

So, maybe I will just use the simple one and leave it at that.

I will need to look at the links suggested and do some more research!

Thanks again!

Look at Northwind. Tell me how those tables would be enhanced by having tbl
prefixes dangling in front of them. Look here
http://www.databaseanswers.org/data_models/index.htm and choose any model.
Tell me how a single model table would be enhanced by having a tbl prefix
dangling in from of it.

 

[BruceM]

I use prefixes because it helps me to keep things sorted out in my mind when
I am working on a database. Others, as you have seen, dislike their use.
When I started learning about Access I was told that I should prefix fields
(txt for text, int for integer, dat for date etc.). I have dropped that
approach. Instead, I give the table a short but meaningful name (tblCust,
for instance). The primary key field is CustID. A text box bound to CustID
is txtCustID. The main form based on the table is frmCust. If there are
several Customer forms I add a suffix to the form name. And so forth. It
works for me, but I have read articles that are utterly contemptuous of the
approach. I don't care. If I was working with others on a project I would
be sure we all agreed on the naming convention, but on your own you can make
your own choices. You could adopt a standard naming convention, only to
work with others on a project and find that an entirely different convention
is in use. The main thing is that it needs to work for you.

 

[mray29]

I agree that the tblprevix in Sql server table are not particularly useful.
But in Access, where , lacking a naming convention, when writing or
deciphering code, queries can be confused forms, or tables with queries, etc.
Just ask anyone who has had to take over maintenance of an Access database in
which no naming conventions were used. For example, if a data source for a
form is "Employee", you don't know whether that's a table or a query, and you
have to go find out. However, if the data source is "qryEmployee" or
"tblEmployee", you already know.

 

[BruceM]

Whether or not somebody adds a prefix to a table name, there are other
choices to be made for forms, reports, and other objects, as well as for
controls.
Regarding your latest posting, the OP asked about naming conventions, and
did not advocate one particular approach. Therefore, challenging the OP
with "Tell me how ..." seems unnecessary.
I find it easier when reading SQL to identify tables by way of a tbl prefix,
but I'm sure I could adjust to a no-prefix system if necessary. Another
advantage to a naming convention is that by using a prefix I am very
unlikely to use accidentally one of the many reserved words in Access or
Jet. There are ways of checking for such use of reserved words, but there
is something to be said for a naming system that sidesteps the potential
problem.

 

[Michael Gramelspacher]

For example, if a data source for a
form is "Employee", you don't know whether that's a table or a query, and you
have to go find out. However, if the data source is "qryEmployee" or
"tblEmployee", you already know.

Northwind must be bewildering.<g>

 

[dee]

Not at all <g>

Have asked for advice, received it, and have made up my mind on naming
conventions... I suppose everyone could challenge me to agree with their
view...

I appreciate your input!

 

[Maurice]

Good for you, the main challenge will be to be consistent in using it. Once
you do that you've convinced yourself that you have made the right choice ;-)