A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Include tablename in fieldname



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2010, 02:40 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Include tablename in fieldname

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


  #2  
Old April 16th, 2010, 03:07 PM posted to microsoft.public.access
Allen Browne
external usenet poster
 
Posts: 11,706
Default Include tablename in fieldname

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
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?


  #3  
Old April 16th, 2010, 03:08 PM posted to microsoft.public.access
PvdG42
external usenet poster
 
Posts: 66
Default Include tablename in fieldname


"Lars Brownies" wrote in message
...
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



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.


  #4  
Old April 16th, 2010, 03:41 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Include tablename in fieldname

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


"Allen Browne" schreef in bericht
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
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?


  #5  
Old April 16th, 2010, 04:26 PM posted to microsoft.public.access
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Include tablename in fieldname

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.

--
Duane Hookom
Microsoft Access MVP


"Lars Brownies" wrote:

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


.

  #6  
Old April 16th, 2010, 05:12 PM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Include tablename in fieldname

On Fri, 16 Apr 2010 16:41:43 +0200, "Lars Brownies" wrote:

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?


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?


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).
--

John W. Vinson [MVP]
  #7  
Old April 16th, 2010, 08:35 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Include tablename in fieldname

Thanks.

Lars

"John W. Vinson" schreef in bericht
...
On Fri, 16 Apr 2010 16:41:43 +0200, "Lars Brownies"
wrote:

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?


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?


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).
--

John W. Vinson [MVP]


  #8  
Old April 16th, 2010, 11:33 PM posted to microsoft.public.access
Bob Quintal
external usenet poster
 
Posts: 939
Default Include tablename in fieldname

"Lars Brownies" wrote in
:

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

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.

--
Bob Quintal

PA is y I've altered my email address.
  #9  
Old April 17th, 2010, 03:09 AM posted to microsoft.public.access
Allen Browne
external usenet poster
 
Posts: 11,706
Default Include tablename in fieldname

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" wrote in message
...
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?


  #10  
Old April 17th, 2010, 12:38 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Include tablename in fieldname

Thanks for the addition. The query naming is a good idea! and the PK suffix
can come in handy too.

Lars

"Bob Quintal" schreef in bericht
...
"Lars Brownies" wrote in
:

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

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.

--
Bob Quintal

PA is y I've altered my email address.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.