Mark Horner on Database Design

Answers to database design questions by Mark Horner, Bristol, England
Date: 3 January 2015

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

     My database design approach is involves taking a look at the requirements and ensuring the approach I take fits the user's requirements and is scalable. For instance if you've only got one person using this database then you won't need a feature such as an audit table, but if you've got multiple users using it over a network you'll want an audit table built in to each of your front ends. It basically revolves down to a) gathering your specific requirements, b) finding a solution to meet those requirements and c) ensuring the solution is scalable and won't cause issues when you have 100k rows of data.

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?

     Poor UI layout for the end user and not splitting the database when there is more than one user. Splitting the database involves having a seperate front end (i.e the application your staff will use) and a back-end which effectively acts as the 'database server' holding all the information in the tables, which the front-end links to. If you don't split the database with multiple users it's only a matter of time until corruption kicks in when someone's network drops or their PC crashes and takes a significant of time to re-build for something which is completely avoidable!!
     Another small gripe is in VBA code when people don't use boolean triggers for true or false they use a string such as "correct" or "false" which uses more memory than a boolean statement, it's not a massive drag on performance but it is a small gripe!

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

     As I have done databases of this scale and size before, it's extremely likely what you want done I have experience in doing, and some of that code will be available to me to re-use by having it on my personal laptop from freelance projects. This enables me to chop and change code where possible rather than write everything from scratch which can take longer, and cost the client more money.
     Further to this, I don't mess around and give unrealistic deadlines, if based on the spec I think it will take me 3 months to get a working copy out the door to you I will tell you that upfront as honesty is the best policy. I would never over estimate and under deliver just to get an extra few hours pay as I would class that as deceitful.
     Lastly, I always look for ways to improve and I do enjoy reading up on new theories and techniques, by browsing forums and checking solutions to people's problems. As you never know when the solution may be something you require whether it's on a personal or professional project.

Where do you live?

     Bristol, United Kingdom. My availability will be evenings UK time which works well with most outside EU time zones in terms of communication.

Briefly describe your pertinent work and/or educational background.

     I have been working with Access for the last 3-4 years. First for a large educational company, then on to Lloyds Bank and now for Computershare (where I am a contractor on a daily rate of 250GBP) focused solely on Access. I have learnt on the job essentially (and through a LOT of self study in finding best practice online etc in places like access-programmers or stackoverflow) and my current role at Computershare is purely Access focused in developing Access databases to use as an interface from the company’s in house extremely old legacy system to churn out reports client ready.
     My educational background, I left college (UK college that is) before I finished my A levels to start working as education didn’t interest me I wanted to work and earn money, and during work I discovered my natural talent per say for Access/VBA and all things technical. It’s for this reason I have started studying for my degree with the Open University in the UK for a BSc in Computing and Business Studies (half and half, as both areas are of great interest to me) and I complete this in my spare time, usually a few hours a week as it isn’t that difficult currently in my 2nd year.

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

     Education and Financial Services, my current and previous role involved working in the Financial Services sector and my role before that was with an education company. I have developed quite a strong knack for picking up new projects/ideas/theories quickly as when one is working in Financial Services it’s very fast moving with various products coming about.

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?

     A good question, I would say in the first instance it’s because what I build is going to be scalable, efficient and you aren’t going to require another person’s time to fix it 3 months later or hire another developer to build you a different database. I say what I see in terms of what is possible, commitment and timeframes re development milestones. Further to this I have experience in building what you are asking for, and with that comes quite a bit of re-usable code in places where we may need to do some complex looping (i.e flying some reports to specific email addresses based on certain logic automatically).

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?

     Well I tend to follow best practice – you may ask given my background how do I know what best practice is, I know because I have done substantial reading on relational database theories and going to the Nth degree with the pros and cons of each. I will look at the requirements, and see what data needs to be stored and work out the best way on how to store it and ensure it allows the integrity of the data to stay intact.

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

     Working with Access is brilliant thanks to VBA, as this enables me to integrate solutions across various platforms. For instance, as previously mentioned automating reports via email is not a problem but we can go one step further and do things like automatically populate a contract or a word template, to then print or password and email off to your client(s). It’s things like this that really make Access strong in what it does, and it’s something I really enjoy doing.

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.

     All of my knowledge has come from on the job learning and self study through various internet websites which gives me a good working overview of how to apply best practice – I can’t say for sure how this differs from a taught course at college but from the small touchings on it my Uni course had in the first year I can say for sure my knowledge is a LOT more in-depth.

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?)

     Hmm, N/A here

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

     What RDBMS have you used extensively, or are at least somewhat familiar with based on first-hand experience? Access all the way, some class it as just a DBMS but in an office environment with a split front-end and back-end it’s definitely worthy of a RDBMS title.

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)

     N/A here as we’re making an Access DB and not a website.

What other technologies are you using for this website?

     n/a

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

     n/a

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

     I do the database design, development and the UI development with Access and a lot of VBA to integrate the database with the rest of MS Office.

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

     Camel casing and it depends, i.e tblEmployees or tblContactDetails etc. It’s the way I have always done it but I always make sure the table name is identifiably relevant to what it hold.

How do you name your primary identity fields?

     fieldnameID so for tblEmployees, EmployeeID or ContactDetailsID etc

Describe your relationship (if any) with WordPress?

     n/a

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

     n/a – we have VBA functions/classes in Access!

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

     Because I communicate effectively, I don’t overestimate and under deliver and I spend a lot of my time working with Access or updating my knowledge browsing various forums for new problems. Further to this I always find a way to get something done and always see a new challenge as exciting if it isn’t something I haven’t overcome before and I don’t see at as tedious. Besides this, I have a ridiculous work ethic and make sure I am building what my client wants, not what I think they want (and yes this does involve quite some feedback from yourselves when it gets to UI design!).