How much time spent planning the DB?

[LauriS]

One of the things I try to get through to my Access Basics students is that
planning is the KEY to a successful project.

I am curious to know(and I thought it might be helpful to those new to
Access that come here) from those that have developed full blown Access
projects (tables, forms, queries, reports, code ... the whole shibang) - how
much time did you spend in the beginning planning the database?

What percentage of the project time was spent in the planning stage (which I
would say covers everything up to the point you started Access to create the
first table).

 

[George Nicholson]

Planning stage would include "Requirements Gathering" and that takes place
well before you even turn on the computer.

Length of time depends (among other things) on the scope of the project, the
process involved (and how many process), the number of stakeholders and how
many levels/types of stakeholders (data entry, data analysts, supervisors,
execs, etc.)

You need a firm grasp of what *everyone* involved thinks is required before
you can create a viable design, much less start implemention (create first
table). Could be days, could be weeks. But without proper preplanning you
are guaranteed to spend a far greater amount of time doing avoidable rework.

A certain amount of rework is usually unavoidable. The trick is to minimize
it as much as humanly possible.

HTH,

 

[FredFred]

I do a lot a both creation of databases and managing creation of them by
newer folks. Other than echoing what George said, a couple more thoughts:

One way to give the data design rightful prominence is to document the
definition / rules for the tables (via what makes something eligable to be a
record) and the definition of what goes in each field. I'v not found
access't tools for the to be very good and usually do it in a table

Sincearley,

Fred


In addition to echoing what

 

[Allen Browne]

The planning stage is as crucial as completing the blueprints before you
start building the house. It varies depending on the project size, of
course, but this stage is generally measured in days (not hours or weeks)
for me. Perhaps 1 day for a small project, and 5 for a larger one.

That includes:
1. Spend time with the client. Understand what their needs are. Examine the
systems they currently use.

2. Start experimenting with a few data structures (i.e. defining the basic
one-to-many relations.)

3. Write a document that specifies the project: what the project's goal is,
what data is stored, what reports come out, what security is required, the
time and cost to develop it, and what support arrangments will be. While
writing this document, ask for clarification on anything you are unsure of,
and play with different data structures.

4. Meet with the client again to go through this document, explaining the
choices you made, and verifying that the design you have stated covers every
possible need, in the simplest possible way.

5. When the client agrees that this specification covers everything, begin
development.

Note that I typically have the tables built by the time the spec. is
complete. That's a little different from your assumption, but I find that
the best way to crystalize the project is to see the tables in place. Since
you have not built anything on this structure (no tables, forms, reports,
code, ...), you have not wasted any time by experimenting with different
structures, but neither have you missed any questions about where the
one-to-many "gotchas" may lie.

HTH.

 

[Jeff Boyce]

My approach runs in parallel with much of what others have said, but
diverges here and there.

Business folks (the ones who are looking for a system/application) usually
have an idea about what they want. And they usually don't have ALL their
needs figured out. And if they could get EVERYTHING they want and need,
you'd never get done. And needs and wants change over time, even during the
(hopefully brief) development period. And things change -- the environment,
the available tools, etc. Given all that, spending much time up front
getting a complete requirements document may prove to be counter-productive!

I absolutely agree that you need an idea of what the customer thinks s/he
wants, and you need an idea of how (if at all) they've been handling before
you got involved. But if you can get the customer to identify the single
most significant show stopper ("if we can't do this, we stop right now!"),
that tells you the most critical development issue AS OF RIGHT NOW! I
usually ask for the top 5, prioritized (and help them define these).

This approach also gives you a way to "chunk" the development into smaller
pieces, with a significant deliverable at each step. So, after you
demonstrate a potential solution (on paper, as a prototype, ...) for the
first-most-critical item, your customers can say "yes/no/tell me more". And
if "yes", and you generate an application that can handle that much, the
user has something s/he has agreed would be the most critical element of the
new system, no matter what else is done.

At that point, take it back to zero and ask again, "what are the five most
critical items, and which is #1?" After all, in the process of getting the
first #1 done, new things may have come up. Or, upon seeing a solution for
that first most critical element, the users may decide they really don't
need anything more (for the moment). Or ...

This approach can be frustrating for both sides, the customer and the
developer, because there is no absolute list of everything that must be
done, and set time frames for each piece, and an absolute known "cost"...
but where else in life do you actually expect big projects to have these?!

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dennis]

I am SO impressed with the multiple cogent answers to this question. The
bottom line is that in the real world, database design is an art, not a
science. I've been in database application design for over 30 years, and by
now, I can sit in a requirements meeting, and while listening, tables and
relationships begin to appear in my head. To me, the actual design of a
database cannot be "taught" (yes, I know I'm a bit off-topic, but please bear
with me). The only way to ever get "good" is to beat one's head against
enough walls, and for enough years, to finally understand (on an almost
visceral level) what's required.

So how long does it take to plan a database? Years and years and YEARS.

Once again, my sincere appreciation for the other great responses to the
question.

 

[Hoardling via AccessMonster.com]

I follow similiar rules to the rest of the postings, just adding a little
more detail.
Before even considering design we discuss with our clients their business
process and how they obtain the data and how long it needs to be kept.
This way we can determine the longevity of the database or it's data. We
also check on how they are currently storing the data be it electronic or
paper. After reviewing the data we can determine if it needs to be in SQL
Server or Access. Then we begin figuring out what items if any are priority.
This way if there is something they need NOW we can get it too them as soon
as possible, with the thought that future information is going to rely on
this. After discussing about the business process then we start beginning
the discussion on how to build the database. We like to use PADIS(Planning,
Analysis, Design, Implementation, and Support) as much as possible, but some
scenarios don't give much time for it. PADIS doesn't always work so we have
to use Reiteration and go over the parts that didn't work and then improve
them. So in all PLANNING is very important. Some projects take days and
others can take weeks. I would say about 50% of time is spent on planning.
If not enough time is spent on planning then prepare to spend most of that
time constantly fixing problems, rebuilding structures, creating MANY
complicated queries and telling clients "I need more time to do that."

 

[George Nicholson]

in the real world, database design is an art, not a science.

1st rule of database design (well, in my top 5): "Its the exceptions that
will kill ya" (short form: "Exceptions Happen")

If a client tells you something can only be black or white and *never ever*
anything else, you will *almost always* be very happy if you allow for the
possibility of gray.

Avoid painting yourself into corners as much as humanly possible

 

[Jeff Boyce]

George

My approach differs ever so slightly ... If I can get a customer to tell me
that s/he will NEVER need something, I make sure to design for that
possibility <G!>

Jeff Boyce

 

[Darren Bartrup]

First I'd sit down with the client and go through what they think they need.

Then I'd write them a statement of requirements - this will cover every
aspect in plain English. Why is the database to be built, what is the scope
of the database, who will use it, how many are likely to use it, does it need
to be secure? What reference numbers will look like, how they will be
formatted, are they updatable or fixed, where does the data come from, who
provides it, what colour is it, does an employee have more than one manager?
do they cover more than one area (is this ever possible?).

Next comes the fields - basically what fields will I need to get all this
stuff together.

Then comes normalisation - what fields can I get rid of as it will just be a
duplication of data elsewhere. I'll add to this which tables each field will
go in.

Next I'll draw an Entity-Relationship diagram - this will show how all the
tables join together (1:n, m:n, 1:1). I'll also the primary and foreign keys
(and restate what's in each table).
I'll add to this any constraints - e.g tickets aren't dispatched until
payment is received.
and any assumptions - e.g credit card details aren't recorded for security
reasons.

Finally I'll create an E-R model using relational algebra which will define
how everything pulls together and can be translated directly into SQL
statements to buid the database.

Errr.... or that's how I'm meant to do it anyway