Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Requesting help with naming TSQL functions.

Existing function names (see below for table and relationship descriptions)

I currently have functions named as follows:

  • CustomerYearsGetByYearRangeOffset: For each CustomerID, return all of their CustomerYears records, over a specified begin/end range
  • CustomerYearsGetLatestByYearRangeOffset: For each CustomerID, return their latest CustomerYears record, over a specified begin/end range
  • ObjectRevisionIDsGetByYearRangeOffset: For each ObjectID, return all of their ObjectRevisionIDs records, over a specified begin/end year range
  • ObjectRevisionIDsGetLatestByYearRangeOffset: For each ObjectID, return their latest ObjectRevisionIDs record, over a specified begin/end year range

Note: All accept a caller-specified begin/end year range offset (relative to the current year). For example, begin/end year offsets -1, 0 would indicate the year one-year prior and the current year.

Two new functions

The two new functions will implement more sophisticated logic, as follows:

  1. For each CustomerID, return their latest ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range

  2. For each CustomerID, return all their ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range

What to name the new functions?

Some drafts:

  1. ObjectRevisionIDsInLatestYearGetLatestByYearRangeOffset
  2. ObjectRevisionIDsInLatestYearGetByYearRangeOffset

Other ideas?

Am I thinking about this wrong? For example, abandon the explicit naming approach above and name according to the higher level need it meets?

I see myself calling these new functions frequently, so I prefer to get something that seems "right" each time I use it. ;o)

Thanks for any assistance. Usually when I get this far in writing a question the answer presents itself, but not this time.


Background: tables and relationships

To keep things simple for this question, the database contains four tables (in reality many more tables and fields):

  1. Customers (CustomerID)
  2. CustomerYears (CustomerID, Year)
  3. Objects (ObjectID, CustomerID)
  4. ObjectRevisions (ObjectRevisionID, ObjectID, CustomerID, RevisionNumber, Year)

In a nutshell: each year the Customer participates, they get a new record in CustomerYears. In each year, they will have one or more Objects, each of which can have one or more revisions.

For example:

Customers (CustomerID)
1
2

CustomerYears (CustomerID, Year)
1|2011
1|2012
2|2012

Objects (ObjectID, CustomerID)
A|1
B|1
C|2

ObjectRevisions (ObjectRevisionID, ObjectID, CustomerID, RevisionNumber, Year)
1|A|1|1|2011
2|A|1|2|2011
3|B|1|1|2011
4|A|1|1|2012
5|B|1|1|2012
6|B|1|2|2012
7|C|2|1|2012
8|C|2|2|2012
share|improve this question

closed as too localized by Aaron Bertrand, Derek Downey, Mark Storey-Smith, JNK May 25 '12 at 20:15

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Apologies, but I voted to close this as too localized, as naming schemes and related decisions are far too subjective to be useful to anyone else. –  Aaron Bertrand May 15 '12 at 21:49
    
Thank you for explaining your vote; much appreciated. I am of two minds on this--on the one hand, agreed, very localized question. On the other hand, in the process of asking it, it raised a more useful (in my opinion) question of the pros and cons of implicit versus explicit "group by" fields in object names.... So, thank you! If the universe considers it too localized, so be it. : o ) –  schultkl May 18 '12 at 15:56

2 Answers 2

Where are these functions being called? Are they being called often? How complex is the logic, does it even warrant creating a new database object?

They are very verbose, and the new ones match that style so I guess they're ok. I would prefer to start with Get then I know right away its more of a 'getter' than a subroutine.

I would also pick more terse names, though I have a weakness for clutter free code.

share|improve this answer
    
Thanks Vince. The logic, in this case, does warrant a new object, due to the frequency with which I see them getting used. I call them currently from other queries but they likely will get used from .NET code eventually as well. –  schultkl May 11 '12 at 22:57
up vote 0 down vote accepted

I think I identified the source of my confusion as well as several solutions:

  • My confusion seemed to stem from existing function names implying the fields used for grouping
  • For example, existing function ObjectRevisionIDsGetLatestByYearRangeOffset groups by ObjectID implicitly over the year range...however, the function could group by CustomerID or CustomerYear over the year range--the function name leaves this ambiguous
  • A rewrite of the existing function name, explicitly stating the grouping: ObjectRevisionIDsGetLatestByObjectOverYearRangeOffset
  • The {BY} clause now explicitly names the grouped fields and the {OVER} clause indicates the function considers a range of years

Solution A

Explicitly name the groupings in the new function names:

  1. ObjectRevisionIDsGetLatestByLatestYearByObjectOverYearRangeOffset (latest ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range)
  2. ObjectRevisionIDsGetAllByLatestYearByObjectOverYearRangeOffset (all their ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range)

Solution B

Modify existing functions to group by CustomerYear, then by Object--that is, leave the grouping implicit but change it to suit the most frequently used business case for the range functions:

  1. ObjectRevisionIDsGetLatestByYearRangeOffset (modify so existing function groups implicitly by CustomerYear, then by Object, over a specified begin/end year range)
  2. ObjectRevisionIDsGetByYearRangeOffset (modify so existing function groups implicitly by CustomerYear, then by Object, over a specified begin/end year range)

Other solutions potentially exist...still chewing on which path to take.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.