Volunteer data and volunteer time tracking 3

[Panhandel]

New to the forum. I volunteer with Habitat for Humanity and I am looking for a volunteer tracking system. I have created a basic data entry form but I am over my head. Need to enter volunteer’s hours, for project 1 or project2 or etc.
Any help is appreciated.

Thanks Panhandel

 

[dhookom]

I don't like multi-value fields and would consider creating fields like the following. Try to avoid storing any data in field names. Data belongs in your tables. Note, I use a very structured naming conventions which helps me identify primary and foreign keys. No two fields in a database have the same name.

[pre]tblVolunteers
===================
volVolID autonumber primary key
volFirstName
volLastName
volAddress
volCity
volState
volZip
volHomePhone
volCellPhone
volEmailAddress
volStatus
volComments

tblSkills
======================
sklSklID autonumber primary key
sklTitle values like computer, carpentry, plumbing, accounting
sklComments
sklStatus

tblVolunteerSkills
======================
vskVSkID autonumber primary key
vskVolID link to tblVolunteers.volVolID
vskSklID link to tblSkills.sklSklID
vskLevel values 1 to 10 for low to master
vskComments

tblProjects
================
prjPrjID autonumber primary key
prjTitle title of project
prjStartDate
prjEndDate
prjStatus

tblProjVols
====================
prvPrVID autonumber primary key
prvPrjID links to tblProjects.prjPrjID
prvVolID links to tblVolunteers.volVolID
prvSklID links to tblSkills.sklSklID
prvFromDate
prvToDate
prvComments[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[Darrylles]

This is not to be argumentative; simply for discussion.
I can honestly say that my many years experience has been fraught with decisions on 'naming conventions'.

Can I ask why you prefix every field name with a table name acronym?
What is the point?

E.g. When creating SQL - you MUST know the table that the field is coming from, because it is declared in your FROM clause.
You ALWAYS know the context of the field, therefore - there is no need to include it in the field name.

If you think a little deeper - I have many applications, I sometimes copy whole table structures across these applications.
That being the case - why haven't you prefixed every table name (and thus every field) with an acronym for the application?

After all:
The application is a container for tables, just as the table is a container for fields.

Neither do I ensure that the pk field name is prefixed with the table name acronym: I call ALL autonumber primary keys 'PK', that is because - by it's context (it's in a table) - I ALWAYS know which table it is PK for.
What I DO do, is name every foreign key field (in other related tables) with the related pk field table name (because, there is NO WAY of knowing which table.pk it is referring-to - unless it has the table name in it's name).

E.g. 'volunteer_fk' which links to the 'pk' field in tblVolunteer.

This ensures that the viewer is aware that this foreign key links to the PK field in tblVolunteer.

I also name tables in singular fashion (as it is a definition of ONE object). It MAY contain zero, 1 or many records, therefore making it's name plural isn't really accurate. A table name is based on a single DEFINITION of a single object - not an indication of many records.

Also, in SQL, an alias can be used to rename field references that are identical (such as PK in my case). THIS, is where I'd prefix the table name to the PK field to ensure that there is no ambiguity - that is why aliasing was enabled:

E.g.

SELECT v.pk          AS volunteer_pk,
       s.pk          AS skill_pk,
       p.pk          AS project_pk
FROM   tblVolunteer  AS v,
       tblSkill      AS s,
       tblProject    AS p
WHERE  ...   ;

Again, I'm not 'picking' - merely 'discussing'.
(It'd be great for us all in this forum to define a 'standard' on naming conventions don't you think? There simply isn't one out there atm).

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[dhookom]

Darrylles,
I appreciate your comments. I have never second guessed my naming convention and have appreciated its simplicity, self-documenting, and perhaps the best I don't ever spend time figuring out what to name fields. To me it's almost automatic.

I primarily use SQL Server and type field names in without using any query designer. Primary and foreign key field names are self-documented and short. I don't have to type table names inside my joins. When an SQL database contains tables from multiple applications, I actually begin table names with three characters designating their application but I didn't think it was pertinent to a seemingly Access only question.

I don't waste my time with "PK" or "FK" since my field names clearly indicates this.

To support my solutions, you only need to know a few simple rules.
- field names in a table begin with 3 lower-case letters identifying the table
- Primary key fields repeat the 3 characters (camelcase) and add ID
- Foreign key fields repeat the 4-8 characters of the related primary key

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

(It'd be great for us all in this forum to define a 'standard' on naming conventions don't you think?

Who's to decide which convention we should all use?, I don't use Duane's convention because I was taught differently at university.

I was taught to create a CDM including ERD which documents all relationships including FK/PK, all field / column names should be lower case with underscore as word separators, all table names should be singular (EG Contact not Contacts) and use camel case with no spaces.

So is the open university wrong? is Duane wrong?

I use Hungarian notation for all my variables when writing code, I wasn't taught that by the Open University, I just prefer it, so is that wrong?

Unless it is a serious breach in best practice such as spaces in column names or the use of special characters, as long as your code is clear, concise, documented and readable, does it matter?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

Good points 1DMF. I don't think it matters much which naming convention you choose. It is only important that you choose one that is understandable/explainable and sustainable. It should also avoid reserved words and perhaps be generic enough to use with Access, SQL Server, MySQL, Oracle, etc.

I doubt my convention is used by too many others but most developers could look at it and understand it in minutes.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

It made me think of someone's signature who posts on TT

"The great thing about standards is there are so many to choose from!"

And that's what I love about TT, there are so many great people to learn from, with different ways of doing things.

I think sometimes there can be just as many right ways of doing something as there are wrong, and as long as we all avoid the wrong ways, it's all good



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

There are a lot of published style guides and naming conventions and they differ by application. First of all it has to work for you and your team if developing in a group. Then it has to be understandable for those who may inherit it. IMO As long as it is consistent, someone can figure it out. There are a few hard rules out there (no spaces, special characters, etc) after that it can become a little more personalized.
Darryle has some suggestions that are in contradiction to published Oracle and Sql Server guides, but it works for him so that is OK. Both Sql Server and Oracle make table names plural and PKs are in the form "TableNameOrAlias_pk". I believe both suggest for the fk to include both tables in the name "ParentTable_ChildTable_FK".
Also different communities have different preferences. I think SQL Server recommends PascalCase (not camelCase) and Oracle goes with CAPITAL_CASE.
There are things I do specifically when working with Access. In Access I write a lot of code using field values. I often want to know if the stored value is a memo vice text, what type of numeric (auto, currency, double, integer, long, etc), and especially if it is a number stored as text (SSN, SN). So I will preface my fields with the same identifiers I would use in Code (strSerialNumber, currAmountOwed, memoComments, strComments, dblSortOrder). That is not recommended by any style guide, but saves me a lot of time and frustration. Although not recommeded, most people would be able to figure it out without problem.
I have also seen it taken to extreme with some coding guides. They got so complicated you needed a decoder key to figure out all the prefixes and suffixes.