Naming Conventions

[Ian Mac]

All,

I try to use common naming conventions wherever possible.
Every now and then I come across what should be common but I can't find a standard method to use.

It not really a huge problem, but as this is the General forum I thought I'd ask.

Does anyone have a good link with a long list of conventions?

http://www.mvps.org/access/general/gen0012.htm
http://www.databasedev.co.uk/naming_conv.html
http://www.acc-technology.com/namconv.htm

Are all good places to look.

Does anyone have a standard way to name a table which permenantly resides in the client but the data is very much temporary.

I use these tables a lot and was just interested.

No matter if there isn't one, just seems a good idea to open up a discussion for anyone else who wastes time like I do on the name of objects etc. rather than doing the work

Any and all ideas on the subject most welcome.

Cheers, I hope this becomes a useful thread.

 

[Pat Hartman]

Using a consistant naming scheme consistantly is more important than which scheme you use.

 

[Ian Mac]

Pat,

You are entirely correct. I was more wondering what other people might use something which is commonly expectable, and readable, more importantly.

I find Access or any DB/Programming development a real pain when you start taking over 'that persons' database and try to unravel the logic they may have employed.

For example, I'm PRINCE2 trained, but I find it absolutely pointless, apart from the principles, when I am told to use these project management methodologies when I know the intended audience is not trained and frankly doesn't care.

An extreme example but one I feel is justified.

I thought maybe this questioning regarding naming conventions may spawn some great ideas which could filter out into people's systems.
A kind of, 'I got this idea from Access Programmers'.

I spend a lot time teaching my staff the methods I use when I would like to see more productivity.
It's a never going to happen situation, but as I said I think I and others probably spend too much time tinkering with the subject.

A good Sticky on the forum may help people out when starting out.

E.g. use tbl for a table in your threads
Don't put spaces in your Field names, it'll help us unravel your code etc.

That said, thank you for replying.

Cheers,

 

[The_Doc_Man]

Ian, Pat is absolutely right. It doesn't matter what you use for a naming conventions. It matters that you USE the naming convention. I hate to say this, but that is why DOCUMENTATION is written. You get to tell folks what conventions you used.

As to what I use?

It is a compromise between long names that are descriptive and short names that are easy to type. Where possible, I avoid use of "tbl" as a table prefix and "qry" for a query prefix. But my fields often have a 2-character datatype prefix. In my VBA, I always use a prefix for the datatype. I.e. stronger than just "often" when in VBA.

Here is the balancing act: Being hide-bound into a convention robs you of the flexibility you might need. It also lengthens the words you must type. If you are a really good typist, you don't care so much. If you are a thumb-fingered heavy-handed typist who needs a children's keyboard with extra big letters so you don't fat-finger every other letter, you might think twice about longer prefixes and longer naming conventions.

Here are some things to DO and NOT to do:

Don't use underscore in any name or as a visual separator between the prefix and the actual name. If you ever have to upgrade what you are doing, underscores don't translate well. Also, underscore is a SHIFT character. Slows you down when typing. Don't do that to yourself.

Abbreviations in names are GOOD things. So are SHORT prefixes. Make yourself a list of conventional prefixes and common abbreviations BEFORE you start using anything, though. Then stick to it.

Whatever you do, DON'T look up any U.S. Government publications on computer program data naming conventions. They come up with abbreviations like NAVRESINFOSYSOFF. That's the ABBREVIATION. Don't bother to guess what the long name means. (OK, I'll tell this one time - Navy Reserve Information Systems Office - a now-defunct echelon III command, defunct because its name has been changed to protect the typewriter-challenged and Mavis Bacon dropouts.)

 

[Pat Hartman]

I would add one thing to the abbreviation discussion. Be consistant. You'll drive yourself nuts if sometimes it's Cust and other times it's Customer. When I'm typing code, I don't want to have to think about what I've called something, or even worse, have to look it up. I have gotten really good over the years at standardizing my abbreviations of common words.

Also, I never prefix column names in tables, especially not with their data type. If you need to change a data type, the renaming effort can be extensive. I do prefix variable names with their data type. In the case of variables, their use is localized so a name change is easy to control. I agree that using tbl as a table name prefix is redundant but it then becomes necessary to prefix your queries. I just use q rather than the more standard qry. In my mainframe days, I always prefixed column names with a file identifier but I find that in VBA that isn't necessary at all. In queries, column names are generally qualified by their table/query name and in VBA, column names are prefixed by the recordset name - rs1!MyField. For this reason, I never use "With". I just use short recordSet names so I don't resent typing them over and over again.

 

[Keith Nichols]

Global edit of names and code?

Good topic throwing up some interesting issues.

I have been resolute in consistently applying my naming convention (qry, frm, fdlg, txt, lbl, etc - I can't recall the naming convention's name off the top of my head, but it is available from the internet via a google search I think).

I don't think I have seen any masive benefits yet, as I know what every single control, form and table etc is for. However, when the db is passed on to others to administer and maintain, I am hoping that they will appreciate this labour.

Here are some things to DO and NOT to do:

Don't use underscore in any name or as a visual separator between the prefix and the actual name. If you ever have to upgrade what you are doing, underscores don't translate well. Also, underscore is a SHIFT character. Slows you down when typing. Don't do that to yourself.

Argh_Oh_No : I have used the undersore on every single tag in my DB. To me, as a newbie, I find it is visually clearer and easier to read. Now that I read The_Doc_Man's comment (BTW Doc_Man, 2 undersores there in your name?!? ) I'm thinking that it might be a good idea to remove or change them.

Is there a way to do some sort of find and replace for the names of database items, tables, forms etc, and the controls on them, say, find "_", replace with "-"?

The code itself can be edited globaly with the find and replace (I think) so that should be easy enough to sort, although I assume you would have to open every object's vba sheet?

Does anyone have a suggestion as to the best way to tidy this up or is it a case of hand cranking the entre database?

Kind regards

 

[The_Doc_Man]

Keith, there are indeed underscores in my moniker. But I don't use it as either a table name or a field name or any other object name in my DB.

The biggest reason to remove underscores from a DB would be if you ever want to upgrade the DB from Access to something like SQL Server or ORACLE or some other product. Not all products accept object names with underscores.

If your DB is a limited use DB with limited or single user (yourself), don't worry about it so much.

 

[Keith Nichols]

Will I need to upgrade?

If your DB is a limited use DB with limited or single user (yourself), don't worry about it so much.

Hi Doc_Man,
I'm straying off topic here - sorry.

• My database will be used by up to 100 people over a local network with a FE/BE split.
• There is no particular trigger for most users, so the usage should be staggered over the working week.
• Most of these users will typically open, view, then maybe generate a report or edit a couple of records before logging out, say 5 minutes usage a few times a week.
• Around 10 users (managers & clerks) would access the database prior to the weekly meeting, although they would all be updating different records if any updating is required.
• 1 user (the Planner) will typicaly spend half a day updating the database 2 times a week, once prior to the weekly meeting and once after.
• There is a time out feature to close any inactive sessions to keep the numbers down.

The database is currently in a limited 'beta' release to managers, the Planner and clerks and works pretty well with no reported problems relating to the database being accessed by too many users.

Given the above, do you think that the database would cope with the full planned user set? If the answer is no, I have 2 options:

1. Limit the database access to the current users - not ideal but workable
2. Upgrade - I see difficulties in getting the multi-billion dollar company I work for to pay for new software

I think I may have answered my own question here:

group_________________Users_____Usage (H)_____Total (H)
Planner__________________1__________8___________8
Managers & Clerks________10__________2__________20
Users___________________90________ 0.25________22.5
Total usage____________________________________50.5


Even on the busy day before the meeting, there won't be sufficient numbers accessing the database to cause problems. I shall keep my underscores.

Kind regards

Keith.

 

[Ian Mac]

Doc Man

The biggest reason to remove underscores from a DB would be if you ever want to upgrade the DB from Access to something like SQL Server or ORACLE or some other product. Not all products accept object names with underscores.

Not sure what you mean by this? Oracle standards-ish are generally all WITH_UNDERSCORES no tbl etc. With Tables pluralised and fields not. Every Oracle DB I have interigated to Excel/Access is like this.
I'm pretty sure SQL Server is the same to.
I know you would want to consider spaces in the mix, as it becomes a nightmare when coding.

Also worth a mention, I try (and fail many times) to find out what the reserved names are in the package I'm using e.g. DateTime, Status, Workbook and avoid them.

I am with you on the I don't generally use spaces mind IMuchPreferCamelNotation.
One reason why I started the topic is to see what other people may be doing, see if it's a good idea.
I mainly asked about the tables (queries, form, reports are the same) because when you've got 50-100 tables knocking around I find it easier to start the names differently so alphabetically, temporary data tables, actual temporary tables, lookup tables all sit together.

 

[Bat17]

Is there a way to do some sort of find and replace for the names of database items, tables, forms etc, and the controls on them, say, find "_", replace with "-"?

There are a few programs that you can buy that will do this for you, speedferret springs to mind. But don't do it!
"-" is worse than "_"

if you type My_Field in a fuction in a query it will bracket it for you:- [My_Field]
My-Field gets you [My]-[Field ] which is a real pain.
just drop the punctuation and use MyField

HTH

Peter

 

[The_Doc_Man]

Keith, your usage analysis is perfectly logical - and imperfectly founded.

You don't care about the average users. (well... maybe you do for overall capacity planning.) What you care about is how many users are going to try to update things at the same time. The issue isn't averages - it is the probability that two users will try to update the same record at the same time. Your planner will sometimes compete with the folks that have something to do just before your meetings. Just be sure you have the record locking set correctly for all queries and forms that don't do any updating. Otherwise you have a true nightmare ahead.

Also, you don't care about the fact that 5-8 users are running some sort of query at the same time. You care about the size of the underlying table.

Access performs all queries on the workstations of its users. The place where the .MDB file resides is, for all intents and purposes, just a file server. The workstations, if they are all looking at the same bit table, can swamp your network if you aren't careful. How? Because remember the query is run on the workstation but the file is on the server. So EACH WORKSTATION has to read the entire table to do the required record filtration. If it is a 100 Mb table, every user has to read 100 Mb to complete the query. If you have a 100 Mbit/sec network, that is 8 seconds per user per query. Which has some effect on the probability of transmission collisions and other things that your network guys would hate to see too often.

The bit about upgrading to something else like MySQL or SQL Server or some other package is because when you do that, the query is processed on the server and only the result set is passed over the network. If the table is over 100 Mbytes but the expected result is less than 1 Mbyte, you just cut your network traffic by a factor of 100.

It is considerations like the above that strongly contribute to whether you get a product like SQL Server or MySQL or some other package. And when you do that, you can run into the issues about underscore and other characters that have special meaning (or NO meaning AT ALL) to a give DB. I have heard of DB packages that would see my moniker as TheDocMan because they EAT the underscores transparently. This is why I suggested earlier to avoid the confusion factor they represent.

 

[Keith Nichols]

Thanks Doc_Man and Bat17 - thought provoking stuff.

First things first. The very useful tip that "-" is worse than "_". I guess that you get used to all the concatenations of tags. It is clumsy to read but doesn't sound as bad as the possible problems described.

There are a few programs that you can buy that will do this for you, speedferret springs to mind. But don't do it!
"-" is worse than "_"
Peter

Keith, your usage analysis is perfectly logical - and imperfectly founded.

Usage: I think my only issues would be the queries. Virtually all users will open and view the same form. This has subforms and various fields, all driven by queries. Reports query the same tables.

Also, you don't care about the fact that 5-8 users are running some sort of query at the same time. You care about the size of the underlying table.

The main table is not that large at present (I'm not at work now so can't say exactly) but it has about 260 records of 20 odd fields, a bout half of which are foreign keys to other tables. This is likely to grow by about 150 records per year until year 5 when old projects will be archived out of the database, or at least that is my plan, but I won't be here then to enact it!

Your planner will sometimes compete with the folks that have something to do just before your meetings. Just be sure you have the record locking set correctly for all queries and forms that don't do any updating. Otherwise you have a true nightmare ahead.

The various users all have different data responsibilities. The planner records and maintains all the project details, status, stage, comments etc. Nobody else (except his deputy) has these rights. The managers and their clerks all have the same authority to update the various personnel assignments but would normally only be interested in personnel under their leadership. Other users can only add comments. So, there is very little likelihood of data conflict and the locks have been set loose (no Locks) to account for this.

I am a little confused by the last bit of that quote. Are you saying that you can set the record locks differently for different uses? i.e. a query for a report would be "no locks" but a query that allows an update could be "Edited Record" or "All Records". I thought that locks were set globally i.e. No locks, Edited Record or All Records.

Kind regards,




+-

 

[Pat Hartman]

If you search help (maybe) and the knowledge base (for sure), you will find lists of reserved words for Access and Jet. I think these links are current:
http://support.microsoft.com/kb/q286335/
http://support.microsoft.com/default.aspx?scid=kb;en-us;321266