Bruce Bray on Database Design

Answers to database design questions by Bruce Bray, Phoenix, Arizona
Date: 3 January 2015

Please explain (or briefly describe) the general philosophy behind your database design approach.

     Basically, I create prototypes of forms and reports FIRST. The inputs and outputs determine what kind of operations and data will be needed to create such an application as you have spoken about. Then I determine which applications will handle which processes. For example, template letters and mail-merge documents are created in MS Word, but the choices and selections for what is printed is done inside of the Access database. Then the application is prototyped quickly so as to get a feel for how the different components will work together. I use custom queries and sql statements for data updates, batch processing for updating data offline, and I write my own custom functions inside of Access, Word, and Excel for any operations that I need.

What is something you have seen done repeatedly in database design work done by others people which you find to be particularly annoying or detrimental?

     The most annoying part about coding in VBA is when you trap an error, and the error returned by Microsoft is erroneous. I have had this happen on numerous occasions in the past couple of decades.

Given that you are database specialist, how does what do you while creating your database architecture save clients time and/or money?

     I think the biggest two advantages I have are that 1) I have been programming in Access and VBA since the very beginning, so I have already been through the trenches, and 2) I have a complete understanding of how Microsoft Technologies work together.
     I am a complete MS Access / VBA / MS Office / SQL Server developer. I have been working with MS Access all the way back to the days of 16 bit software. I have built numerous databases for county governments and small businesses in the state of Arizona. I have complete expertise and can code with ease in modern current MS Office applications. I have built the kind of system that you are speaking several times. I can also hook your MS Access database up to a SQL Server database, if that is what you desire.

Briefly describe your pertinent work and/or educational background.

     I have been working in the computer industry since the 80's. I began with DOS and dBase / Lotus and just learned database programming from there until now. I did go to the SQL Server 6.0 weeklong class and pass the exam for that as well as Windows NT 3.51. Outside of that, all of my other skillsets have been developed by programming for all of these years.

What are the industries you have the most experience working within?

     Government County Recorder offices. I supported two (2) counties here locally for a number of years. Wrote a Microsoft Access application with a SQL Server backend that did all of their voter registration. Also used Visual Basic to develop a Indexing application.
     Investment Firm Industry - I wrote an application that managed all of the contacts, contracts, payments, future values, call logs, word mail merges, excel reporting using Access as a front end and again SQL Server as a backend.

Your rate is higher what other programmers and freelancers charge. How does what you do with database design save clients and companies money in the long run?

     I have been doing this type of programming so long that I can actually do it faster than other people. So perhaps you pay some guy $15 an hour, but it takes him 3 times as long as it does me. Plus, it is one thing to program in Access. It i another to have experience programming and integrating word and excel as well as DLLs, OCXs, and COM objects from outside vendors.

You have special expertise in database design. How is what you do different from what a generalist computer programmer might do when it comes to creating a database back end?

     A lot of programers I talk to merely create tables and use them as a data store, which is really not much different than a file system at that point. I actually use aspects of SQL such as user defined functions, stored procedures, indexes, views and triggers to create efficient applications as well as enforce business logic.

How is the database design work you do distinct from and also integrated with other aspects of overall software/technology systems?

     I find that working in Access is a little more inclusive than working in Visual C or Visual Basic for example. Even though some of the tools defined inside of the application are less robust than something outside of the application, for the most part every thing you need is inside of Access.
     As far as being integrated, outside of VBA with the other applications, I can usually find some sort of app object library to connect with other systems. For example, I bought an Active X object with allowed me to connect with Peachtree accounting for reporting purposes.

Describe how the database design work you do now is similar to or different from "textbook database design," such as one might learn in college or read in a text book or software manual.

     Well, since I did not go to college, I could not accurately answer that question. But i will say that most of programming stems from mountains of books that I read on Microsoft Programming and TSQL coding.

What is the biggest example of a "rule" you routinely break? (Or another way of putting it is: what popular tradition are you most likely to depart from, if any?)

     Probably storing objects inside of a database. I still prefer to store objects in the file system and create indexes to find these objects. It just makes everything cleaner and faster.

What RDBMS have you used extensively, or are at least somewhat familiar with based on first-hand experience?

     SQL Server has been my bread and butter for sure. And since Microsoft starting distributing end user versions so that the customer doesn't have to pay full price, it is easier to use than ever.

Given an assignment to create a website from scratch for Company X, what RDBMS are you using? (website specs: principle table has 10,000 rows and will grow to 200,000 over next five years; admin back-end, general browsing, user logins; no more than 100 concurrent users)

     Well, I thought this was a desktop application, not a web application. Access would really be a poor choice for this type of application. Most of the actual WEB database connections i have made, I did in PHP using MYSQL. But it would be the same for a .NET application using SQL Server on the backend. The only thing about that is, some of the web hosting companies have very limited versions of SQL Server running. So you would want to make sure it was robust enough. For example, one particular hosting company I used would not allow UDFs to be created. I could never do that with SQL Server as that is such a great technology to use.
     As far as general browsing, I am comfortable doing CSS and HTML5. I have done a little Wordpress, but really have no interest in programming in Wordpress.

What other technologies are you using for this website?

     Again, I am a bit confused. I thought this was a desktop application. But again, if you are doing an web based app, you would use Windows NT with IIS, .NET, SQL Server and you are pretty good to go (outside of backups and maintenance functions)

What tools would you be using? (IDEs, code editors, database design tools, if any)

     MS Access has it own IDE and container toolset. But I do also use MS Visual Studio and NUSphere PHP as my primary IDEs when I am doing that type of programming. And of course I use SQL Client tools such as Query Editor to create all of my MS SQL Server function procedures and the like.

Aside from the database design, how much other development do you typically do? How much would you prefer to do?

     I do web design as well. But on a very limited basis. My particular process for that is, find a HTML5 with CSS based template that a customer would like, change it to a visual layout the customer would need, do any PHP and Javascript programming I need to do, create a MYSQL database on the backend, hook it all up, test it, and be done.

Table names: plural? singular? or something else? why?

     I name all of my tables according to their functions. Examples : Customer, Invoice, Asset, AssetOwner, etc., etc.

How do you name your primary identity fields?

     I name all of my identity fields with the name of the table, added the Uppercase ID at the end. For example CustomerID for the Customer table (not to be confused with a counter field or other indexed fields which I have a different naming system for)

Describe your relationship (if any) with WordPress?

     I supported a Wordpress website for a sister business at my office. I didn't really care for it, and really don't have interest in learning that particular programming set. I understand why it's popular, I know how it works, and i can install and configure templates and the like, but it just is not my expertise.

If you use stored procedures, how do you decide to use them or not use them? What are your alternatives?

     I use Stored Procedures and UDFs a lot. I prefer to use stored procedures for action type sequences of code. For example, user updates data in a form, and data needs to be updated in several places at once. I find this to be a particular great use of Stored Procedures. Or when doing batch jobs.
     I know some people like to return recordsets using them. And I have done that. But I actually prefer using UDFs for this because you can just call it as a table passing a parmeter and then further filter it using a SQL Statement. I use that a lot for reports.

Why would a client who needs database design work done be making a wise move to hire you (if you're available)?

     Because in this particular field, Access / Office / VBA / Visual Basic / SQL Server programming, there is just nothing that I cannot do. I hope you don't find that statement arrogant, because I'm not that way at all. As a matter of fact, I continue to learn constantly. It's just that I have been doing this so many times for so many years, I have a ton of confidence in this area.