G
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
*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