How to move from design to development more quickly?

[Guest]

I think my DB design process for all but very simple databases requires
*significantly* more time to implement the design than necessary. Are there
other methods and specific tools to "speed things up"? (This is not about
what the design is, rather how to do the design and get it implemented as
fast as possible.)

I think I'm an "intermediate" Access power user, whatever that might mean.
Fairly strong on database theory and how to use DBs for the analytical work I
do (financial, organizational, etc). I can set DBs up fairly well, but I'm
not much of a programmer (trying to learn).

Here's how I do it now. I start on paper. When I begin to think I've "got
it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed proceedures
I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in effect) a
data dictionary for each table. I name each worksheet's tab with the table's
name.

Here's a wrinkle: unlike "normal" database formating, the "records" are
columns and the attributes are rows. That is, the first row has all the
"FieldName"s in the table, the second indicates which fields are "Key"s
(Primary or Foreign), and so on for Description, DataType, DataDomain, Size,
Format, Lookup (that is, tied to a Lookup Table), Required, and Notes.

That puts the field names in the top row. When I think I've "got it", I
create a named range for each top row. I then open a new Access db and import
each named range from each table/worksheet to create the tables with fields.
But then I have to enter by hand all the characteristics for each field (data
type, desc, size, format, default, required, etc.)

I find it's faster to change the evolving design of tables in Excel than
Access itself because I can see and edit multiple tables' dictionaries at one
time.

I then use my Publisher-generated Entities-Relationships diagram to guide me
in creating the Table Relationships in Access.

I recently posted a related question, and got two helpful responses.

__
I found that the time it took me to think out (paper/pencil) a design, even
a preliminary structure, then define it better, then ... this process
slowed me down.

If by "efficient" you mean getting on with it, I found that I could create a
table structure (entities, attributes, relationships) in Access fairly
quickly, then step back and refine it.

Adding in a layer of paper, or a layer of "db definition table" slowed me
down.

But that is JOPO - just one person's opinion...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

___


Jamie Collins and Roger Carlson gave me good advice about how to use VBA
and/or SQL to grab my field definitions out of Excel to create the DB, but as
I say, I'm not very advanced at programming.
___

Suggestions are greatly appreciated. I'd much rather be canning my tomatoes
right now (32 plants in the garden!) than retyping stuff I've already typed.

Thanks - John D

 

[David Cox]

I find it's faster to change the evolving design of tables in Excel than

Access itself because I can see and edit multiple tables' dictionaries at
one
time.

You can have multiple windows open in Access, and one of those can be the
relationships window.

And one of those can be a datasheet view of a query that brings in just the
table data that you want (selected wit checkboxes) and you can see and edit
it just like you can in Excel.

And you can cut and paste fields from one table design window to another if
you change your mind,

and you can cut and paste an entire line from a query to another line or to
a new record...

Please explain what Excel can do in this application that Access can not?

 

[Guest]

You can have multiple windows open in Access, and one of those can be the
relationships window.

And one of those can be a datasheet view of a query that brings in just the
table data that you want (selected wit checkboxes) and you can see and edit
it just like you can in Excel.

And you can cut and paste fields from one table design window to another if
you change your mind,

and you can cut and paste an entire line from a query to another line or to
a new record...

Please explain what Excel can do in this application that Access can not?

David

Let me make sure we're talking about the same thing - I'm not talking about
after the database has been populated with data - I'm talking about
implementing a design. Unless I'm mistaken, I thought queries only access and
manipulate data living as records in tables. I didn't think a query could
access and manipulate field specifications such as size, format, data type,
etc. Am I wrong?

Is there a way to display and edit field specifications in a datasheet
format?

As to what Excel can do that Access doesn't as regards the design process
(at least what I know about Access at this point):

In Access the Table Design View has two areas. At the top you can see all
the fields, data types, and descriptions. But field size, format, input mask,
caption, default value, validation rule, etc. are displayed at the bottom
only for a single field that is highlighted at the top. I don't know how to
view and manipulate/edit all the attributes for a table in Access. It's kind
of clumsy to have to repeatedly highlight similar fields in different tables
so that you can see their attributes in the lower section of the view.

However, Excel displays rows and columns with each cell accessible and
edit-able. I can see all the design attributes for all fields in a table -
and by having multiple windows open with different table worksheets in each,
I can quickly browse the tables to make sure I'm defining similar fields
(such as OrganizationID) the same without having to highlight each similar
field.

Another thing Excel does that I don't know how to do in Access is print out
table definitions in simple tabular format. Tools/Analyze/Documenter "prints"
a "memo style" report that has each field's attributes as rows under the
field name. The report gets way too long (IMHO) very quickly.

NOW - there are several add-ins available from those great MVPs that do much
better - such as the one I'm using from Jeff Conrad available at

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

*Much Better* report - to use for example in reviewing design with a client
or a development team. Or training, say, a support person.

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

Thanks for your reply

John D

 

[tina]

well, i'm not an MVP, but i use a combination of pencil/paper and Access. i
think a lot of it is just accumulated experience, John. when i'm building a
database strictly for myself, my process analysis is an internal dialog, and
i usually write directly into table design view, modifying and restructuring
as i think through the process - until i have a normalized and correctly
related table structure. when i am analyzing somebody else's structure, i
take a lot of notes on paper that i use to build a clear, detailed *mental*
picture of the process and the entities involved; then i build the
tables/relationships. i tend to think with my eyes, though, so i often find
that building the tables raises additional questions that i must discuss
with my customer.

once i'm building tables, though, setting the field properties is mostly
automatic; i don't have to put a lot of thought into it. as you build more
and more databases, you'll get to the point where you know immediately, from
experience, what is the appropriate data type and field size for almost all
fields, when you need to make a field Required, or not, when to Index, or
not, etc. some things you'll probably set the same *all* the time - like
disallowing zero-length strings, removing the default zero value in number
fields, and so on - and then tweak those settings in the occasional field
that needs it.

hth

 

[Tim Ferguson]

Here's how I do it now. I start on paper. When I begin to think I've
"got it", I do two main things: develop a Data Dictionary and an
Entity-Relationship Diagram. If I can identify existing or needed
proceedures I try to document them as well.

I use Excel to develop the Data Dictionary. Each worksheet is (in
effect) a data dictionary for each table. I name each worksheet's tab
with the table's name.

You have two (or three) choices:

If you can make a business case for the investment, then you can buy a
tool to do this -- Embarcadero, Visio, etc etc;

If you don't have any money to invest, then you can invest your time
instead by creating a tool yourself. It's not very hard; it's just a
question of building one or more "CREATE TABLE..." commands and executing
them.

If you are very lucky, you may find that someone who has built such a
tool will be kind enough to give you his or her work for free. I seem to
remember that Jeff did exactly that.

Jamie Collins and Roger Carlson gave me good advice about how to use
VBA and/or SQL to grab my field definitions out of Excel to create the
DB, but as I say, I'm not very advanced at programming.

What can I say -- this is a programming group, go learn programming!

B Wishes


Tim F

 

[Jeff Conrad]

Hi John,

I think you'll find a wide range of viewpoints on this issue. I know some people who won't even go
near the power button on a computer before getting every specific detail of their table structure
down on paper. I know others who do all of their table design work within Access-refining as they go
along. Still others use a mesh of the two approaches. MVP John Viescas outlines in his book
Microsoft Office Access 2003 Inside Out his method for table design. He uses some Excel-made printed
sheets to do his design work on paper first. Once he has the basic structure down he begins to build
his tables, but then makes additional modifications within Access as he builds the relationships and
enters some sample data.

Personally I'm quite similar to MVP Jeff Boyce (discussed in your earlier thread) concerning this
topic. I tend to do most of my design work within Access itself. I usually initially create a table
for each entity with just one temporary field and throw it into the Relationships window. I tend to
visualize things much easier when I can see how their relationships interact within the Access
window versus sheets of paper laid out on the floor. That's just me.

You touched upon the following subject:

But I was wondering if anyone knows of some software product in which you
can easily design a DB and then easily transfer the table - field definitions
into Access. Or, do the MVPs doing complex designs just use what Access
provides?

I personally have not seen any such tool, but that doesn't mean one does not exist. With enough
time, effort, and VBA knowledge I'm sure someone could create something, but I don't know how much
of a need there would be. I don't know if it would save a whole lot of time actually. Remember that
for every field you need to set certain field properties. In Access you need to do this on a field
by field basis. If you did this same work in say Excel you *still* have to set these individual
settings in Excel so this special VBA tool could set the appropriate field settings within Access.
Follow me? I just don't see the time advantage here.

Don't get me wrong, I'm not knocking your method of table design. If it works for you and it helps
you to generate a solid base, then it's great. I just think that there really is no way to avoid the
down and dirty task of going through field by field and setting specific properties. Computers can
only guess so much. A tool to help bridge the gap may help the process, but you'll still have to
carefully go over the blueprint plans.

Don't think of the extra time spent on table design as a waste. MVP John Vinson likes to use the
analogy of building the foundation of a house for this process. Spending the extra time on getting
the table design right (or as darn close to right as possible) on the first attempt **will** save
time on the rest of the application development. Trust me. A good table design makes the interface
design a lot easier.

Think of it this way--I have to choose between two contractors to design and build a very large new
house for my family. The first contractor will spend about 60% of the cost on building a rock solid
foundation. Extra time will be spent carefully analyzing the soil around the area to determine where
the best place on the property to build. Rainfall patterns from past years will be studied to see
how drainage should be handled. Extra time and expense will be spent drilling farther into the
bedrock for more stability. Extra rebar and special concrete will be poured in increments to further
add stability. All of this added expense will leave less money for the rest of the house. Naturally
the house will have to skimp a little bit on the extra "goodies." The house will be nicely decorated
and adequately furnished, but certainly not a mansion inside.

The second contractor, on the contrary, will spend 30% of the total cost on the foundation. Just a
basic run-of-the-mill foundation job with no extra reinforcements. The rest of the house will look
like a palace inside. Rich mahogany hardwood floors, stainless steel appliances, every room
pre-wired with CAT 6 cabling, air conditioning, 42" plasma TVs in every room, etc. You get the idea.

You're the contractor for the first house. In your presentation to me you do an absolute fantastic
job of outlining the costs of the project **up front** to me. You carefully detail why you believe
your process is the best. You also detail why the furnishings of the house will not be top of the
line, but you discuss the benefits of the extra time and expense spent on the solid foundation. You
discuss the changing seasons and future resale value of the house. Before closing your presentation
you offer up one last comment, "By the way, your property is in an earthquake prone region."

If you have done an excellent job of outlining the costs to your clients, explained why the extra
design time spent reaps rewards later on as my business changes, and explain that extra "goodies"
and features could be added on as needed in the future, who am I going to choose to solve my
business need?

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

in message:

 

[David Cox]

or "You cannot build a skyscraper starting with a bungalow."

 

[tina]

some folks down here in Anaheim Hills probably wished they'd read your
analogy before the big rains we had year before last in So Cal.

Hi John,

I think you'll find a wide range of viewpoints on this issue. I know some people who won't even go
near the power button on a computer before getting every specific detail of their table structure
down on paper. I know others who do all of their table design work within Access-refining as they go
along. Still others use a mesh of the two approaches. MVP John Viescas outlines in his book
Microsoft Office Access 2003 Inside Out his method for table design. He uses some Excel-made printed
sheets to do his design work on paper first. Once he has the basic

structure down he begins to build

his tables, but then makes additional modifications within Access as he builds the relationships and
enters some sample data.

Personally I'm quite similar to MVP Jeff Boyce (discussed in your earlier thread) concerning this
topic. I tend to do most of my design work within Access itself. I usually initially create a table
for each entity with just one temporary field and throw it into the

Relationships window. I tend to

visualize things much easier when I can see how their relationships interact within the Access
window versus sheets of paper laid out on the floor. That's just me.

You touched upon the following subject:


I personally have not seen any such tool, but that doesn't mean one does not exist. With enough
time, effort, and VBA knowledge I'm sure someone could create something, but I don't know how much
of a need there would be. I don't know if it would save a whole lot of time actually. Remember that
for every field you need to set certain field properties. In Access you need to do this on a field
by field basis. If you did this same work in say Excel you *still* have to set these individual
settings in Excel so this special VBA tool could set the appropriate field settings within Access.
Follow me? I just don't see the time advantage here.

Don't get me wrong, I'm not knocking your method of table design. If it works for you and it helps
you to generate a solid base, then it's great. I just think that there really is no way to avoid the
down and dirty task of going through field by field and setting specific properties. Computers can
only guess so much. A tool to help bridge the gap may help the process, but you'll still have to
carefully go over the blueprint plans.

Don't think of the extra time spent on table design as a waste. MVP John Vinson likes to use the
analogy of building the foundation of a house for this process. Spending the extra time on getting
the table design right (or as darn close to right as possible) on the first attempt **will** save
time on the rest of the application development. Trust me. A good table design makes the interface
design a lot easier.

Think of it this way--I have to choose between two contractors to design and build a very large new
house for my family. The first contractor will spend about 60% of the cost on building a rock solid
foundation. Extra time will be spent carefully analyzing the soil around the area to determine where
the best place on the property to build. Rainfall patterns from past years will be studied to see
how drainage should be handled. Extra time and expense will be spent drilling farther into the
bedrock for more stability. Extra rebar and special concrete will be

poured in increments to further

add stability. All of this added expense will leave less money for the rest of the house. Naturally
the house will have to skimp a little bit on the extra "goodies." The house will be nicely decorated
and adequately furnished, but certainly not a mansion inside.

The second contractor, on the contrary, will spend 30% of the total cost on the foundation. Just a
basic run-of-the-mill foundation job with no extra reinforcements. The rest of the house will look
like a palace inside. Rich mahogany hardwood floors, stainless steel appliances, every room
pre-wired with CAT 6 cabling, air conditioning, 42" plasma TVs in every room, etc. You get the idea.

You're the contractor for the first house. In your presentation to me you do an absolute fantastic
job of outlining the costs of the project **up front** to me. You

carefully detail why you believe

 

[Guest]

Thanks folks for your comments.

I know a good local contractor who subscribes to the business imperative of
"better, faster, cheaper". That was the focus of my question - there must be
a "better, faster, cheaper" way of moving from design to development of an
Access DB than what I'm doing.

Incidentally, I absolutely buy the idea that it's important to take the time
to make sure the design is "right". For example, when you understand what's
happening in the Normalization process it's clear you need to do it. But in
context of my question that's part of the *CONTENT* of design, not the
*PROCESS* or way of doing it. It needs to be done, I just want to do it
"better, faster, cheaper".

Here's what I got from your responses:

1) There really isn't a "settled industry standard" about what tools and
process to use in designing Access databases; it largely depends on "what
works for you".

2) I combined a thought from both tina and David. I'm going to create a
"Boilerplate" Table of fields I'll use frequently already formated the way I
want, and then copy/paste from that boilerplate table into new tables I'm
building. Then I'll probably only have to change the names. Like, have the
"phone" fields formated as I will want, then copy it into tables and rename
to things like "WorkPhone" or "PersonPhone". That at least would save the
time of having to go through all the field attribute definitions.

3) The Visual Studio or Embarcadero stuff refered to by Tim is intriguing -
I'll look into it more.

Thanks again

John D

 

[Jeff Conrad]

in message:

2) I combined a thought from both tina and David. I'm going to create a
"Boilerplate" Table of fields I'll use frequently already formated the way I
want, and then copy/paste from that boilerplate table into new tables I'm
building. Then I'll probably only have to change the names. Like, have the
"phone" fields formated as I will want, then copy it into tables and rename
to things like "WorkPhone" or "PersonPhone". That at least would save the
time of having to go through all the field attribute definitions.

John,

One other thing to consider is a new feature with Access 2007 that follow closely with your
"Boilerplate" table of fields example. This new feature not only could save time on the design
process, but also the development process. In 2007 you can set up your own custom "blank" database
template that includes, for example, sample tables with field properties set as well as some sample
queries, forms, reports and even any code modules you wish to have. Once you set this up if you
click on new blank database in 2007 *every* new database would include all these elements! Pretty
slick, huh? Essentially your blank database already has a whole bunch of application objects already
created. You could even create several of these custom templates to use for various scenarios.

Something to think about.

 

[Guest]

John, when you have a field that has just the right settings, and you wish to
make another field that has the same, or nearly the same, settings, you can
just copy that field and paste it in a new row in table design window, then
renaming it to the other field name. It should maintain all of the settings
of the original field. That could speed up your development time.

 

[tina]

i'm beginning to think i'm going to have to pry open my wallet far enough to
squeeze out the cost of A2007 when it's released.

 

[Jeff Conrad]

You'll be surprised how much has changed Tina!
A lot of new things coming in Access 2007...

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

in message:

 

[Fred Boer]

Dear tina:

Prying open the wallet is hard enough, but prying open the 'ol brain to
learn lots 'o new things - that may be the hardest part!

Cheers!
Fred

 

[Jeff Conrad]

Keeps the mind sharp Fred!


--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

in message:

 

[tina]

LOL - you're right Fred, my brain creaks worse than my joints sometimes!
....and just when i thought i was getting good at this... <sadly waves
goodbye to comfort zone>