building a database from scratch

[bigmac]

hi all, i have built several small datbases to date with the help of members on this forum.
one of the issues i have is it does not matter how i go about designin the DB i always seem to forget something (password setup ) for example,
do you have a sequence of setting up a DB ? do you decide beforehand what is going to be included in the design, just asking for your input as to how other people go about it and why they use that method

 

[Minty]

You need to document all the requirements before even heading off with pen and paper.

Without those and then building a clear list of what's expected out and what's therefore required to go in you are probably going to be building a chocolate teapot.

Think big picture, not minutiae at the opening stages.

 

[Mark_]

To add to Minty's post,
Start off by asking your client "What do you do", "How do you do it", "What do you want replicated in the database", "What additional requirements do you need supported"?

This is a basic "What do I need to build into the database" set of questions. Expect to spend a LOT of time going over what is being done, what needs to happen in the future, what needs to be included for future expansion, and what can be eliminated by doing this electronically.

For myself, getting a list of what reports they will need produced, what is on the reports, how they need to be generated and what they need to report on also helps define additional requirements not always listed originally.

 

[The_Doc_Man]

In the design stages, there are two rules that I apply.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Meaning, you must analyze you problem to the point that you can write out a procedure or a data-flow chart or SOMETHING that covers what you are going to do. You need to build yourself a "roadmap" because... When embarking a journey, if you don't have a roadmap, how will you EVER know you have reached your destination.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Meaning, Access is a "subject matter expert" in building infrastructure. YOU are the expert on content. Access gives you form. You give it substance. If you have a list of desired outputs, a necessary exercise is to assure that every field in your desired output has a valid source within the DB. Sometimes this means back-tracking from outputs to inputs one field at a time. There is also the issue that if you want XYZ then either you need a source of XYZ ... OR you need sources for X, Y, and Z, and the formula that brings them together for that output.

A side effect of having that roadmap I mentioned earlier is that it makes a great start for any after-the-fact documentation you need to build because it will include information on WHY you did something. Or why you didn't.

 

[Bullschmidt]

Since I've already created many databases, when starting a new project I almost always try to figure out which one that I've already done that it would be closest in design to and start with a copy of that and then add/edit/delete stuff as needed...

 

[MajP]

When building a DB the only thing of any importance is design of the tables. If the tables are designed correctly you can build code, queries, forms, reports to support user needs. Most people want to get to the "sexy" stuff first the GUI which is irrelevant..

 

[The_Doc_Man]

So true, MajP. My late father-in-law was a carpentry contractor. He was into doing things the right way in the right order. There is an old carpenter's rule: Measure twice, cut once. Not to dissimilar from our variants: Analyze twice, implement once.

 

[Mark_]

MajP,

I've found that doing a mockup of the screens helps drive some of the data requirements. You can sit down with a client, work out everything they say they need at the table level and completely miss pieces as they can't "See" what they will be working with. This can be little items, such as "Where's their next due data and amount owed?" to "But we need some way for us to remind all sales people of what promotions we have right now!".

This helps avoid misunderstandings later, the kind of thing that can sour an otherwise good relation with a client.

 

[Dreamweaver]

I use this https://databasedreams.createaforum.com/time-and-billing/screenshots-20/


To help stucture what I need I must admit it works better when there's a client as I would normlly have a lot of paperwork to help structure the project before It's actually started

 

[jdraw]

bigmac,

Here is a link to various articles in several formats re Database Planning and Design and related info. The "knowledge nuggets" from BA_Experts are humorous as well as informative.

I agree that getting the tables and relationships designed and vetted to support the business is critical. But getting the "client/customer" to identify key outputs and to describe processes is also , but less, important.


UPDATE
I had "but less" originally, but following Doc's comment further down in the thread, I have removed that. User requirements are key to getting the right application. Properly designed and vetted tables and relationships are key to the application right.

 

[The_Doc_Man]

Actually, jdraw, we would be quibbling about minor differences here, but I think getting the customer's input is crucial to getting the tables properly defined, since only the customer can advise you of true requirements. Which is why I place customer feedback 1st and peer review 2nd. To be honest, nobody EXCEPT the customer can really tell you what you need and that is why we have that old phrase, "Even if he's an idiot, the customer is always right."

Haven't heard the first part that often? Yeah, we usually don't say that around customers. Bad for business.

 

[Mark_]

LOL @ Doc...

P.S. how often did your late father in law tell you about customers who decided that things were omitted only AFTER they had seen the final product? Something like "But there's supposed to be a glass window in my door so I can see who's outside! Don't you KNOW THIS???" I've been reading http://clientsfromhell.net/ lately.

 

[jdraw]

Yes Doc, I agree - you have to get and vet the client(s)'s requirement.
I was just trying to extend MajP' comment

the only thing of any importance is design of the tables

.

I have edited that previous comment.

In effect, the tables and relationships have to support the business of the client. And that takes effort and patience; and vetting that design with sample business scenarios often is an eye-opening experience.

We used to work with prototype designs and stub processing to get/confirm/extract "real requirements". A mock up of tables and relationships along with stump the model.
Stubs identifying major steps and instead of detailed logic, we used messages to relay eg What goes here or what to do (verify Contact) just to make sure the order of events aligned with customer needs. Often explicit errors were put into these stubs to get client to show us "how we got it wrong" in order to solicit details. Sometimes a change in processing sequence was experienced by the client and incorporated into interface design.

 

[adrianscotter]

I personally like to spend a couple of days with the client, find out what they want to get out of it, get samples of paperwork and anything they use in the course of their real world work at this moment in time. The usual non disclosure stuff applies... Once I know what they want out of it, I know what they need to put in. From there I can suggest what may or may not be useful in the future, ask relevant questions about what they expect and for suggestions of what else could be useful. I have a core ‘customer’ database that I use. This contains a simple database of name, address, telephone etc., etc. It also contains a user table and login with username / password plus a navigation screen that I can add to. Basically my main building block, it saves me a couple of days on every project. I also like to make my forms look like the paperwork systems they’re used to.

 

[Mark_]

I also like to make my forms look like the paperwork systems they’re used to.

Oddly, this is one piece I often don't do. Rather, I prefer sitting with a few employees to see HOW they fill out the form (order for fields) rather than see what manager expect them to use. It has often been very entertaining to see the first field on a form filled in last (and finding out it isn't actually USED anyplace else) and be told "Its just how we do it".

 

[adrianscotter]

Oddly, this is one piece I often don't do. Rather, I prefer sitting with a few employees to see HOW they fill out the form (order for fields) rather than see what manager expect them to use. It has often been very entertaining to see the first field on a form filled in last (and finding out it isn't actually USED anyplace else) and be told "Its just how we do it".

Always with the employees, never the manager except for long enough to be polite

In my experience, managers seldom know what’s really required...

 

[The_Doc_Man]

To be brutally honest, at the managerial level, they don't CARE what is really required (at a level beneath them). They care about being able to deliver an (answer, report, diagram, whatever) to their boss. As long as they can do that and stay within budget, they are going to stay (as we sometimes say from the trenches) "fat, dumb, and happy." Oh, 'scuse me - my cynicism crept out...

But seriously, regarding that you watch employees fill out a paper form IS a good idea - but not for the obvious reason. What you need to do while watching is to ask the employee, "How could this form be improved?" And then, if it doesn't diminish the quality of the data capture but does provide some facet of improvement, get permission to implement the improvement. In government offices where I worked, it was often the case that paper forms were generated because of someone's flat-file thought process. Paper IS, after all, the ultimate flat file. But Access allowed me to implement ways for people to look up something (say, via combo box) rather than having to write something in from frail memory.

Therefore, during evaluation, it should normally be OK to watch for opportunities to do someone a favor by making their job easier.

 

[adrianscotter]

To be brutally honest, at the managerial level, they don't CARE what is really required (at a level beneath them). They care about being able to deliver an (answer, report, diagram, whatever) to their boss. As long as they can do that and stay within budget, they are going to stay (as we sometimes say from the trenches) "fat, dumb, and happy." Oh, 'scuse me - my cynicism crept out...

But seriously, regarding that you watch employees fill out a paper form IS a good idea - but not for the obvious reason. What you need to do while watching is to ask the employee, "How could this form be improved?" And then, if it doesn't diminish the quality of the data capture but does provide some facet of improvement, get permission to implement the improvement. In government offices where I worked, it was often the case that paper forms were generated because of someone's flat-file thought process. Paper IS, after all, the ultimate flat file. But Access allowed me to implement ways for people to look up something (say, via combo box) rather than having to write something in from frail memory.

Therefore, during evaluation, it should normally be OK to watch for opportunities to do someone a favor by making their job easier.

Absolutely right! I've also noticed through my long and lustreless career that managers are often appointed from the jobs that they are now overseeing because they were useless doing those jobs themselves and it's cheaper in the UK to promote than it is to fire them sadly. I personally know 3 managers within 5 miles of where I live that can neither read or write to save their lives and their titles all include 'Manager'. Quite laughable really and very sad that this still goes on.

 

[The_Doc_Man]

Adrianscotter, you are describing something called "The Peter Principle" which is found in private industry but is MOST commonly found in government: "A person will rise to his own level of incompetence."

The USA Civil Service rules make it darned difficult to dump a schlump but it is relatively easy to sideline someone into a dead-end track. So when you get a mid-level person who is given some duty that requires a manager but is not technically challenging, you know they were about to "screw the pooch" in their former position and got put on the fast track to nowhere. They make a career of being shuffled around from one niche to another, never quite learning enough to overcome their uselessness at that level. Sad, in a way, but not unknown.

 

[adrianscotter]

Adrianscotter, you are describing something called "The Peter Principle" which is found in private industry but is MOST commonly found in government: "A person will rise to his own level of incompetence."

The USA Civil Service rules make it darned difficult to dump a schlump but it is relatively easy to sideline someone into a dead-end track. So when you get a mid-level person who is given some duty that requires a manager but is not technically challenging, you know they were about to "screw the pooch" in their former position and got put on the fast track to nowhere. They make a career of being shuffled around from one niche to another, never quite learning enough to overcome their uselessness at that level. Sad, in a way, but not unknown.

It seems the US and the UK are quite similar in this respect. Be safe TDM, Barry looks like he’s going to side swipe you.