Include tablename in fieldname

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

Lars Brownies

What's the general concensus about including the tablename in every field
name, like:

tblCustomer
ID_Customer
Lastname_Customer
etc.

Is it necessary? Should it be prefix or postfix?
Thanks,

Lars
 
Not sure you'll see a concensus on this, Lars. Some do it. I don't because:

a) JET can handle queries where 2 fields from different tables have the same
name.

b) You can avoid that situation by aliasing the field in a query if it
really matters.

c) In general, if you have tables that have similar field names, you may
want to consider whether the data actually belongs in one table.
 
Lars Brownies said:
What's the general concensus about including the tablename in every field
name, like:

tblCustomer
ID_Customer
Lastname_Customer
etc.

Is it necessary? Should it be prefix or postfix?
Thanks,

Lars
Click to expand...

You should get many responses to this, but this is my *opinion*.

IMHO, it's redundant and unnecessary because references to fields in queries
can easily be qualified by the table name, as in [Table Name].Field. If
field names are duplicated in multiple tables, references to those names in
queries must be qualified by table name, as in WHERE Table1.ID = Table2.ID.
Clear enough, I believe.
 
Thanks Allen, PvdG42,

That sounds clear. I'll adopt it.

Now, I have a table called Education and I want a field that holds the name
of the education. Even if 'name' wasn't a reserverd word I wouldn't call
that field 'Name' or 'Description'. In those cases, what do you suggest?

Another thing I'm doubting is whether to use the plurol form of the
particular word for the fieldname. For instance: in a Customer table should
I use Firstname of Firstnames as field name?

Thanks,

Lars
 
I have never regretted the naming convention that I use. It has a few basic
rules that if used provide a somewhat self documenting and explicit system.

- Every field name in a table begins with 3 lower-case letters identifying
the table:
empFirstName
empLastName
empStreet
empDptID
jobTitle
jobDescription
(you should be able to determine which tables contain the above fields)

- The primary key field is an autonumber that repeats the first 3 letters
and adds "ID" so there are always 8 letters with a specific pattern:
empEmpID
jobJobID
prjPrjID
(you should be able to name the tables, data types, and index type)

- Foreign key fields follow the same convention. Find the field above
that is a foreign key to tblDepartments.dptDptID

- The usual don't include spaces or special symbols and
use upper and lower case for easy reading.

As I stated, this system has served me well particularly when working with
multiple developers. I have not always followed these rules when connecting
to other data sources.
 
Thanks Allen, PvdG42,

That sounds clear. I'll adopt it.

Now, I have a table called Education and I want a field that holds the name
of the education. Even if 'name' wasn't a reserverd word I wouldn't call
that field 'Name' or 'Description'. In those cases, what do you suggest?
Click to expand...

I'll usually use a shorthand version of what kind of name it is: EduName
perhaps.
Another thing I'm doubting is whether to use the plurol form of the
particular word for the fieldname. For instance: in a Customer table should
I use Firstname of Firstnames as field name?
Click to expand...

Again, conventions vary, but I'll typically use a plural as the name of a
table (because a table contains multiple Customers), but a singular for
fieldnames (because a customer has only one Firstname).
 
Thanks.

Lars

John W. Vinson said:
I'll usually use a shorthand version of what kind of name it is: EduName
perhaps.


Again, conventions vary, but I'll typically use a plural as the name of a
table (because a table contains multiple Customers), but a singular for
fieldnames (because a customer has only one Firstname).
Click to expand...
 
What's the general concensus about including the tablename in
every field name, like:

tblCustomer
ID_Customer
Lastname_Customer
etc.

Is it necessary? Should it be prefix or postfix?
Thanks,

Lars
Click to expand...
It's usually more a hindrance than a help. Except for key fields,
columns should be unique to the database anyways.

I do use PK and FK as a suffix to key column names often prefixed
with the singular version of the table's plural name. I do not bother
with the tbl prefix, but do use qsl, qup, qdl, qtl and xt as prefixes
for queries of types Select, Update, Delete, Totals and Crosstab.

So my tables for a sales system would be Customers, Orders,
OrderLines

Some of the fields in OrderLines would be OrderLinePK, OrderFK,
CustomerFK.
 
Hopefully you have read the opposing views too.

Regarding plurals, again it's stylistic, but I don't do it.

I have wondered about whether I would use a plural name for a multi-valued
field, but since I don't use them the question is academic.
 
Thanks for the addition. The query naming is a good idea! and the PK suffix
can come in handy too.

Lars
 
IMHO:
I prefer to sometimes use the tablename in fieldnames.
Example I might have a table that is used a lot like tblContact and
I have fields "Firstname" and "LastName"

In a second table tblEmployee (which only holds the 20 employees) I might
call fields
"EmployeeLastName" and "EmployeeFirstName"

It just makes a few things easier (not getting duplicate names and having to
tack the table name on all the time)
Also it keeps the fields "LastName" and "FirstName" shorter (I use them a
lot). So in general I try to not have too many fields
with the same name.

I also use singular names for everything (so I don't need to remember is it
singular or plural)

I don't use short abbreviations very much (usually spell things out etc...).

For Primary keys I always use "ContactID" or "EmployeeID" and keep the same
names
for foreign keys in other tables.

I never use spaces.

However back when I did Datawarehouse design I used the tablename in every
field.
Followed Ralph Kimball's data warehouse techniques.
ContactID
ContactFirstName
ContactLastName

No two fields in the entire database had the same name.

Now I just do it how I like it.

My two cents,
 
Thanks for the addition.

Lars

Mark Andrews said:
IMHO:
I prefer to sometimes use the tablename in fieldnames.
Example I might have a table that is used a lot like tblContact and
I have fields "Firstname" and "LastName"

In a second table tblEmployee (which only holds the 20 employees) I might
call fields
"EmployeeLastName" and "EmployeeFirstName"

It just makes a few things easier (not getting duplicate names and having
to tack the table name on all the time)
Also it keeps the fields "LastName" and "FirstName" shorter (I use them a
lot). So in general I try to not have too many fields
with the same name.

I also use singular names for everything (so I don't need to remember is
it singular or plural)

I don't use short abbreviations very much (usually spell things out
etc...).

For Primary keys I always use "ContactID" or "EmployeeID" and keep the
same names
for foreign keys in other tables.

I never use spaces.

However back when I did Datawarehouse design I used the tablename in every
field.
Followed Ralph Kimball's data warehouse techniques.
ContactID
ContactFirstName
ContactLastName

No two fields in the entire database had the same name.

Now I just do it how I like it.

My two cents,

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com
Click to expand...
 
Back
Top