[thelist] ERD tool

[rudy]

>So, Rudy, I understand you are Mr.DB, right?

you flatter me -- thanks, bill

more SQL than DB, though (there's a difference)

> By the sounds of your message here, it seems like maybe
> you don't spend a whole lot of time doing ERD's and such.

yes, right now i am a struggling freelancer (hint, hint)

> Either that or you're doing them mostly on paper?

actually, i'll use ERwin if i have access to it, because i've suffered the
learning curve (it has one of the most horrible user interfaces ever, after
homesite/cfstudio)

> I guess my question is, when it comes to planning and designing
> a new database, what tools (software or otherwise) do you or don't you
use?

i can do it in my head, and then pound out the DDL in a text editor

the "design" effort, if i can call it that, lies in knowing which tables
you need, how they should be structured (normalized), and which tables
relate to which other tables

ER tools simply automate these tasks

but let me repeat yesterday's advice -- i would not recommend getting a
tool and learning it that way

let me draw you a parallel -- if somebody came to you and said she was
interested in learning web design, would you tell her to go grab a copy of
frontpage or dreamweaver and get to work?

it's a crude analogy, i admit, but you need to know what to put on the web
page before you fire up dreamweaver and start building

you also need to know something about cleaning up after the tool, much like
the handcode edits you have to do after using a wygiwyd web editor

> I actually am just becoming conscious of the need to really have a
> good plan for a DB, which is why I'm looking for reading material,
> etc. on how to do it properly (schoolin'). Up until now, everything I've
> done has started with hen scratching in my notebook at a meeting,
> and ended in web forms and PHP.

that's not wrong

paper and pencil do produce useful database designs, just like hand-drawn
wireframe sketches can be used to design the site map and navigation
structure in a web site


some references --

Database Design Tips
http://www.datatexcg.com/Tips/DatabaseTips.htm
quick and dirty and covers the basics (includes the advice "Always design
the database on paper first. This one tip will probably save you an
enormous amount of time. ... You’d be surprised how fast an eraser
works." -- heh)

Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
a basic primer, which covers normalization, Primary and Foreign keys, and
indexes

Database Normalization
http://www.wdvl.com/Authoring/DB/Normalization/index.html
only goes to 3rd normal form, but the example of not removing zipcode=>town
dependency into a 3rd normal table is excellent, and illustrates that
normalization depends more on good common sense than theoretical dogma

Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2
Fwc060600%2Fwcblurb060600%2Easp
don't know if the powerpoint presentation is any good (presentations
usually aren't), but there are a number of useful links under "supplemental
reading"

Data Normalization - A Primer
http://www.prestwood.com/forums/database/info/normalize.html
for practical purposes, pretty useless -- its main appeal is that it
defines six normal forms, although what good this will do you is debatable

Database Normalization And Design Techniques
http://www.phpbuilder.com/columns/barry20000731.php3?page=1
very good article which walks you through to 5th normal form using clear
examples

A Simple Guide to Five Normal Forms in Relational Database Theory
http://home.earthlink.net/~billkent/Doc/simple5.htm
another good article, again taking you to 5th normal form, again with
excellent examples
(note that this article is 20 years old and still quite apropos)


when you're done the above, let me know, because you then need to
master the fine art of deciding whether or not a column should be declared
NULL or NOT NULL

;o)


rudy