{"id":102,"date":"2012-09-10T07:51:44","date_gmt":"2012-09-10T07:51:44","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=102"},"modified":"2019-05-10T22:05:06","modified_gmt":"2019-05-11T02:05:06","slug":"database-standards","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=102","title":{"rendered":"My Database Standards…"},"content":{"rendered":"

There is a discussion, *Database Standards when using outside Contractors*, in a group\u00a0that I have been watching\u00a0and it got me to thinking\u2026 \u00a0While I have a set of standards (and a Model Database<\/a>), I have not written them down.\u00a0 So every time I need to ask a Subcontractor to do some work for me I have to rattle them off and hope I remember them all which always results in, \u201cOh, yeah, I forgot that one\u2026\u201d.\u00a0 So, why not just type them up?\u00a0 Ahem, no answer for that except that I am doing it now!<\/span><\/span><\/span><\/p>\n

You may think this is a waste of time and why bother\u2026 but consider the Developer coming in behind you.\u00a0 I get a lot of business from people that already have a database that needs some upgrading or tweaking AND, I spend more time trying to figure out what everything does and\/or where it does it then implementing their request because no Standards were followed.\u00a0 And for you folks that think spending the time to fix the Captions on Fields and Labels is wasted, well,\u00a0let\u2019s look at the example below\u2026<\/span><\/span><\/span><\/p>\n

(c) Toon-a-Day.com<\/p><\/div>\n

If Text15.Value = 0 And Text16.Value = 0 And Text19.Value = 0 Then<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0nbr = Text17.Value<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0x = modRound.RoundToNearest(nbr, 10, vb_roundup)<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0Text193.Value = x<\/address>\n
Else<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0nbr = (([Text15] + [Text16] + [Text19]) * [Text17]) * (1 – ([Text181] * -1))<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0x = modRound.RoundToNearest(nbr, 10, vb_roundup)<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0Text193.Value = x<\/address>\n
End If<\/address>\n

\u2026enough said!<\/span><\/span><\/span><\/p>\n

Now you\u2019re asking if all my databases follow these Standards\u2026 the answer is *No*.\u00a0 However, the deviations were out of necessity not laziness.\u00a0 Standards are great and help everyone, especially if multiple Developers are working on the same project, but sometimes there are legitimate reasons to digress.\u00a0 When this happens make sure you tell everyone involved and document why and what you did.\u00a0 And finally, the Standards\u2026<\/span><\/span><\/span><\/p>\n

General\u2026<\/span><\/span><\/span><\/p>\n

    \n
  1. Use UNC Mapping<\/span><\/span><\/span><\/li>\n
  2. Refresh Links\/Relink Module with a *Persistent Connected* form<\/span><\/span><\/span><\/li>\n
  3. Uncheck *Track name Autocorrect info*<\/span><\/span><\/span><\/li>\n
  4. Set Subdatasheet to [None] but not before Step 3 or it will not stick<\/a><\/span><\/span><\/span><\/li>\n
  5. Check *Use four-digit year formatting*.\u00a0 You can change this on the form for data entry should the Client want two digit years<\/span><\/span><\/span><\/li>\n
  6. Uncheck *Show Smart Tags on Datasheets*<\/span><\/span><\/span><\/li>\n
  7. Uncheck *Compact on Close*, this has been known to cause corruption<\/span><\/span><\/span><\/li>\n
  8. When all done designing hide the Navigation Pane<\/span><\/span><\/span><\/li>\n
  9. Document any passwords and any other pertinent information (and honestly, we can skip the one on the Code)15<\/sup><\/span><\/span><\/span><\/li>\n
  10. No ActiveX controls14<\/sup><\/span><\/span><\/span><\/li>\n
  11. All Databases must have Version included the Caption of the Main Menu or Switchboard<\/span><\/span><\/span><\/li>\n
  12. All hidden fields will use #FF99CC as the Back Color<\/span><\/span><\/span><\/li>\n
  13. Uncheck *Enable Layout View*<\/span><\/span><\/span><\/li>\n
  14. Uncheck *Enable design changes for tables in Datasheet View*<\/span><\/span><\/span><\/li>\n<\/ol>\n

    Code (VB Editor)\u2026<\/span><\/span><\/span><\/p>\n

      \n
    1. All Modules must contain *Option Compare Database*, as well as, *Option Explicit*.\u00a0 (I have seen databases without either!)<\/span><\/span><\/span><\/li>\n
    2. Comment code<\/span><\/span><\/span><\/li>\n
    3. Error Handling and not<\/strong> *On Error Resume Next* across the board<\/span><\/span><\/span><\/li>\n
    4. No Wizard use2<\/sup><\/span><\/span><\/span><\/li>\n
    5. Use UPPER CASE for all SQL keywords<\/span><\/span><\/span><\/li>\n
    6. Indent code for easier reading<\/span><\/span><\/span><\/li>\n
    7. Use continuation marks so I don\u2019t have to scroll all the way to the right to see all your code<\/span><\/span><\/span><\/li>\n<\/ol>\n

      \u00a0<\/span>Tables\u2026<\/span><\/span><\/span><\/p>\n

        \n
      1. Must follow my Naming Conventions1<\/sup><\/span><\/span><\/span><\/li>\n
      2. Field Size convention and not the Access default3<\/sup><\/span><\/span><\/span><\/li>\n
      3. Every table must have a Primary Key<\/span><\/span><\/span><\/li>\n
      4. The Primary Key should also be AutoNumber AND mean nothing to anything except Access<\/span><\/span><\/span><\/li>\n
      5. Child and Junction tables\u00a0should also contain a Primary Key, as well as, appropiate\u00a0Foreign Keys<\/span><\/span><\/span><\/li>\n
      6. No spaces, underscores\u00a0and\/or other wildcard characters in names16<\/sup><\/span><\/span><\/span><\/li>\n
      7. Non-use of the *Field Caption* property at the Table level<\/span><\/span><\/span><\/li>\n
      8. Non-use of *Validation Rule\/Text* at the Table level<\/span><\/span><\/span><\/li>\n
      9. Non-use of Smart Tags<\/span><\/span><\/span><\/li>\n
      10. No data entry performed via the tables11<\/sup><\/span><\/span><\/span><\/li>\n<\/ol>\n