none
What are good names for database objects? RRS feed

  • General discussion

  • One of Codd’s rules (I paraphrase) says that all the data should be in columns, not in the column names.

    But what about the tables themselves? What are good names for them? And the rest of the objects? The “prefix rules” don’t help, and specifying ‘tbl’ to indicate a table breaks the rule, and ignores the obvious intelligence provided by the Access icons.

    Well, tables are usually easy to name because they deal with entities. The going gets a little tougher with queries, as they are ‘virtual tables’ involving filters and joins and what have you. Form names are even tougher.

    I’m thinking at this point that I should use tables of object names like those Access uses to manage the database. I would wind up with an additional table for each database and simply name the objects T1, T2, T3, … Q1, Q2, Q3, … etc. but even those names are an encoding of data. Thus, I’d need a ‘type’ column, and I could name each object numerically: 1, 2, 3, … because the name is irrelevant. I suspect, however, that this idea would make a database unusable.

    Is there a better way?



    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, October 18, 2016 3:44 AM

All replies

  • Are you trying to pick a fight? Unusual for sure! In my company this would be cause for a serious talk.

    My first rule is "be consistent". whatever your naming convention is, PICK ONE and stick with it.
    Second, your names should be descriptive of their content. qryJobs_Active. qryJobs_Active_parCustomerID

    Form names are not harder - Apply the same standard. "frmJobs_Active", etc.

    I'm thinking you're too much focused on naming convention. Pick one, and move on to focus on the important stuff, which is a user-friendly UI and solid business rules.


    -Tom. Microsoft Access MVP

    Tuesday, October 18, 2016 5:27 AM
  • Is there a better way?

    Hi Peter,

    Interesting. There are many ways to name all the items in a database. The “best” way in my opinion is one that is structured and consistent, in accordance to your “housestyle”. In my opinion this also holds for code: code should be as structured and “normalized” as data is in a relational database.

    I can only tell about the systematics that I use to manage more than 90 completely different applications. Each applications is based on the Entities, e.g. “Person”. I prefer the singular form of the word. From this are derived two other names: Person_tbl and Person_id. I prefer the subfixes, because that is more in line with my natural language.These Entity names (Items) are collected in a metadata table Item_tbl. When a new Item (e.g. Person) is added, a new table Person_tbl is created in the BE, with the field Person_id as primary key (autonumber). With these simple definitions all relations can be generated quite easily.

    The names of the fields in these table are short, understandable and powerful, without any strange characters.

    The names for the forms that I use are systematic. That is, as soon as a new Item is defined, a copy of a generalized form is made: Person_formx, to display all kind of queries around the Item. I have some other generalized forms for display of a single record, to create a new record, or to make a kind of (dynamic) combobox to select a record. These forms take the Item as parameter.Besides of that, I can define Tasks, again with a simple and understandable name, which generates a huge multitude of different “forms”, all based on a same generalized form and subform. The name of this dynamic form is the name of this Task, with the details in an analogues name Module.

    I have no names for QueryDefs, as I do not use them. All queries are generated at runtime. You can see a query as consisting of four parts: the Select-part, the Join-part, the Where-part, and the Order-part. Depending on the context in  a query is needed, the different parts are collected, and the query-string is constructed.

    For the names of the variables in the code I do not use the prefix with the datatype of the variable (it was handy in the old COBOL-times). The datatype of the variable follows mostly from the proper chosen name, or else it is easily remembered by reading the declaration of the variable at the beginning of the procedure.To distinguish I can use a prefix indicating the special use of the variable.

    The same holds for the global variables. I use quite a lot as a result of the generalization. The global variables have a prefix, not indicating the datatype, but the context in which the variable is used. For instance I have a generalized function Active_set, that handles any recordset, including all error handling. All necessary globals will start with “active”, among them “active_sql” to contain the to be used query-string.

    A little bit different …

    Imb.



    • Edited by Imb-hb Tuesday, October 18, 2016 9:23 AM
    Tuesday, October 18, 2016 9:19 AM
  • I approach naming database objects as I would anything in programming. Table, Query, Report, Stored Procs, Triggers, Functions.. they are all just essentially coding objects. When you can, avoid noise words, encodings, and make names distinguishable and descriptive. Yes it sucks to type a long name while coding, but it also sucks to see Q1, Q2, Q3 in 6 months from now when your spending the time wondering what the heck you were talking about. Not to mention you may probably make anyone working with the system in the future very confused. Don't be afraid to invest time renaming objects later if it makes more sense. And if it makes development more efficient. Be consistent!
    Tuesday, October 18, 2016 1:26 PM
  • Tom!

    Who, me!?  Fight!? I’m just an ignoramus looking for information!

    I am trying to un-think the naming convention, because so far there is no convention that works consistently. To be guaranteed consistent, it should be automatic. And if it is automatic, the machine should do it, probably with an autonumber field. That is consistency: a number. That number could be an ID in a TableOfContents table, which contains a “complete” description of each object. Perhaps call it the meta-table, which goes beyond what I know as “the data dictionary”. I’d need an ObjectType table for lookup.

    When I gin up a demo database using  numbered objects, it seems that what I’m doing is making the Access Navigation pane irrelevant and useless. The table name ‘2’ tells me nothing.

    BUT …

    When I look at Codd’s rules 0-2, having a consistent naming convention is implicit. Everyone has agreed about the consistency part, and then invents names that violate that consistency. In fact, the naming conventions proposed so far in this thread are INconsistent with those rules.

    qryJobs_Active, for example, violates the rules on its face because it encodes the type of the object, and a specific attribute of Jobs.

    Imb-hb speaks of data names, but in this thread I’m interested only in the database object names.

    HTHP also argues for consistency. What I’m saying is that it is impossible to be consistent with long names, too, because that info belongs in a table, like Codd said.

    The take-away for me so far is “consistently violate the rules of relational databases”.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 19, 2016 12:35 AM
  • Imb-hb speaks of data names, but in this thread I’m interested only in the database object names.

    Hi Peter,

    I will explain a little more on my use of object names.

    In my systematics everything is parameterized, so for me it makes no difference what the name of the object is. Most important object is the Form, that is passed from one routine to the other as object variable:  cur_form As Form.

    "cur_form" stands for current form. To distinguish between different form objects I use something like prev_form, last_form, more or less indicating the meaning or intention of the object than its name.

    In the same way I handle the Table, Field, Subform, Module, ... objects. I do not use QueryDef objects, but I would handle them in the same way.

    I do not use Macro's, nor Report objects. Any reporting is done directly to an output file.

     

    Does this clarify enough?

    Imb.

    Wednesday, October 19, 2016 7:32 AM
  • Naming of database objects should follow 2 rules. First, the name should describe what the object does or what data it holds. Second, the naming should be consistent within the application. (If you are an in-house developer creating multiple apps, then your naming should be consistent across the apps).

    So your idea of generic names just doesn't cut it. Also your interpretation of Codd's Rule #1 doesn't mean that you shouldn't use descriptive names. It means that each datum should be described in the record. For example, you might have a table of payments. Each payment is described by the payor, the amount, the date and what the payment covers. So fields like Payor, Amount, PayDate, Account, or similar would be good.

     

    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Wednesday, October 19, 2016 9:17 AM
  • But what about the tables themselves? What are good names for them? And the rest of the objects? The “prefix rules” don’t help, and specifying ‘tbl’ to indicate a table breaks the rule, and ignores the obvious intelligence provided by the Access icons.
    My guiding rules for table and column names are simple:  

    1.  Keep as close to plain English (or whatever your native language is) as possible.

    2.  For table names use plural or collective nouns to reflect the fact that a table represents a set, e.g. Employees.  For tables which model a relationship type I will often use a composite noun which reflects the entity types in the relationship type, e.g. EmployeeProjects, or which describes the relationship type , e.g. OrderDetails.  

    3.  For column names use singular nouns as far as possible to reflect the fact that a column represents an attribute, e.g. EmployeeID, FirstName, LastName etc.  For keys use the same name for a foreign key as that of the candidate key which it references, e.g. CustomerID in an Orders table.  This is not always possible, however, e.g. in a table which models a bill of materials as an adjacency list by having two columns, each of which reference the primary key of a Parts table.  In cases like this name each foreign key column to clearly differentiate their functions, e.g. MajorPartID, MinorPartID.

    Remember that SQL is a declarative language; you declare the problem and the database engine does the rest.  So keeping as close to plain English in the naming of tables and columns makes it easier to write or read SQL statements, particularly complex ones.  I do not use 'tags' for table or column names as I find these merely interfere with the semantics.  I do use tags for other object names, e.g. 'frm', 'qry' etc. as these are mostly referenced in procedural code where they aid the semantics.

    However, one should not be proscriptive about these things.  What suits one person might not suit another.  The important thing is to be comfortable with whatever naming conventions you use so that, as Tom says, you can get on with the important task of designing the application.

    I'm not sure why you think using tags violates Codd's Information Principle (Rule #1).  Its definition, taken from Date here rather than from the horse's mouth, is:

    'The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in rows in tables'.
    C J Date - Introduction to Database Systems; 7th Edition; 2000

    It says nothing about the names you give to tables or columns.


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, October 19, 2016 12:53 PM Typo corrected.
    Wednesday, October 19, 2016 12:50 PM
  • Interesting!

    You have given the objects names, but then don’t deal with them because everything is abstracted away from them into programming variables. If I understand correctly.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 19, 2016 6:51 PM
  • Scott –

    Whence these two rules for databases?

    The Names Rule: If I were programming classes, I tend to agree. But in the Access MSysNameMap, the table is Named, and it is also given an ID and a GUID. I’m guessing Access manages the database using either the ID or the GUID.

    The Consistent rule: we all seem to agree on this. I’m still learning what consistent means.

    --snip--

    So your idea of generic names just doesn't cut it.

    --snip--

    How so, if the generic names are consistent? Access is using generic names internally already.

    What I’m seeing in my own use of Access is that the DATA are well described, as you exemplify, and they are tracked by Access. It is left to me to track the objects ‘by hand’. What I want is the precision provided by the database itself to track them.           


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 19, 2016 7:19 PM
  • Ken –

    I like your conventions, no doubt established after finding out in the previous millennium that ‘Buffy’, ‘Fido’, and ‘Clancy’ don’t make much sense as table names in a Naval ship database, for example. But I’ve seen, and used, worse.

    I’m not trying to be difficult, here, but as  you say, a name like OrderDetails contains the information of a relationship type. By using such a name we are stepping outside ‘The entire information content of the database’ when we name objects, since the names are NOT ‘explicit values in column positions in rows in tables’, but are ad hoc qualities. Thus, I would amend Date’s statement to something like ‘The entire information content of the database, with the exception of the names of objects, is represented …’


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 19, 2016 7:42 PM
  • Because generic names don't explain what the data is. You come back to an app 2 months later are 7you going to remember what T1 is? Or even worse Q1? Or is someone has to take over your apps, they will be pulling out their hair!

    Frankly, your whole concept here is foreign to me. I really don't understand what precision you are expecting from Access in terms of tracking objects. When developing a database, you use meaningful names for database objects so you know what they contain.



    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Wednesday, October 19, 2016 8:47 PM
  • Interesting!

    You have given the objects names, but then don’t deal with them because everything is abstracted away from them into programming variables. If I understand correctly.

    Hi Peter,

    If the above is an answer to my reply, yes, the names of the objects (from meta data tables) are converted to object variables that are handled in generalized procedures.

    This also means that, apart from very special application specific things, each application is just the same. You have fields in a record, records in a table, and relations between records by their FK, and all this is generalized (or abstracted).

    Imb.

    Wednesday, October 19, 2016 10:00 PM
  • You’re exactly correct! That is why it seems to me there is more info required than a name. The name only exists as a pointer into the date, whereas, it should tell us everything, but it can’t because it’s too short. Pick up one of your databases from 6 months ago and tell me you know what all the well-chosen names mean.

    Yes, this seems to be foreign to everyone, so far, except perhaps to Imb-hb.

    What I’m trying to say is that the information that is being encoded in the Names, ought to be in a table, so that the Description of the object, and any other pertinent info, can carry the intelligence which is at this time desired of the name. Even well chosen names, which are arbitrary and depend on the dev, are irrelevant. 

    When I posed this question, I did not realize the conversation would go in this direction, but it is helping me understand what I’m thinking, so thanks for hanging in.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 19, 2016 11:17 PM
  • What I’m trying to say is that the information that is being encoded in the Names, ought to be in a table, so that the Description of the object, and any other pertinent info, can carry the intelligence which is at this time desired of the name. Even well chosen names, which are arbitrary and depend on the dev, are irrelevant. 

    Hi Peter,

    I don't agree with you. I think names are relevant!

    The names are important in the connection of the program to the real world, or of the real world to the program. It all depends on the level of programming that you use in your applications. If you have generalized (or abstracted) some part of a process in the application, for instance the handling of forms, resulting in a black box or some kind of method, than the inside names will be (more) systematically. The connection to the real world still must be made, but now through parameters or metadata tables.

    Imb.

    Thursday, October 20, 2016 8:03 AM
  • Imb -

    I am going to have to agree with you, and everyone else, as well … the ‘zero position’ doesn’t seem like a good naming option.

    However, … given that there have been a multitude of threads here associated with normalizing data, and adhering to ‘the rules’ of how a database should be constructed, that we are willing to ‘break the wall’ of encapsulation and assign names to the objects in the database according to conventions that rely on human memory for their interpretation.

    One thing I’ve learned from this conversation is that I shall henceforth provide verbose descriptions of my objects using the description field/property.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Thursday, October 20, 2016 7:18 PM
  • I am going to have to agree with you, and everyone else, as well … the ‘zero position’ doesn’t seem like a good naming option.

    ...

    One thing I’ve learned from this conversation is that I shall henceforth provide verbose descriptions of my objects using the description field/property.

    Hi Peter,

    The ‘zero position’ IS a good naming option (and in my opinion the only one) when you work with abstract objects, but you need a translation to the outside world, for instance to the field names in a table.

    For me object names are meta data, thus belonging to the software part: short and powerful. These meta data are used by the programmer of the application. It is not necessarily exposed to the end user. Of course, it makes a difference if you make a development tool for the end user or a fixed non-development tool with all necessary flexibility. I only make the last.

    In my applications the end users never see the navigation pane with all the objects of the application. They see forms, where the "names" (control names, field names, or more general object names) are “displayed”  the way they need. It makes not so much difference whether this is short or verbose, English or Chinese, because it is just a display format.

    Imb.


    Thursday, October 20, 2016 8:55 PM