Re: Planning A Database

  • From: Andreas Stefik <stefika@xxxxxxxxx>
  • To: programmingblind@xxxxxxxxxxxxx
  • Date: Tue, 2 Mar 2010 09:53:21 -0600

In lay terms, first normal form means the following:

"all entries in the database must be single fields, not collections of
data." You can wax academically about E.F. Codd's proofs on this stuff, but
in the end, that's all it means.

So, for example, if I had a database that represented a car, a first normal
form database would not have a single column that had both headlights and a
headlight manufacturer id in it. Speaking more abstractly, all of the normal
forms (1-6) are derived to essentially minimize the database space required
to store something and also to facilitate the creation of database languages
that can query that information (e.g., relational algebra, sql, tuple
calculus).

Now, with that said, I'm not sure I agree that first normal form is the most
important thing. Indeed, the foundation of modern database theory is really
relational algebra and SQL. From a practical perspective, beyond any shadow
of a doubt, I would focus on learning SQL over learning about the normal
forms. Normal forms are an optimization, under typical database load
conditions. However, they, provably, do not always work well for given
situations (e.g., think google or hadoop with what are called NoSQL
architectures). More importantly, SQL lets you actually build something
useful right away, and is designed to work well even if you don't completely
understand it (e..g, Np-complete optimization of query trees comes for free
in any modern database).

And I don't know if it helps, but when I teach university classes on
database theory, we usually go over at least the following:

1. Relational algebra - essentially the math behind using SQL
2. SQL and some design work
3. Optimization and normal forms

The point is, if you just want  a quick and dirty tutorial and want to
create a database the best you can, definitely study SQL. SQL is designed to
automatically optimize your queries and give you a fighting chance of making
a reasonable database. If you want to dig deeper, studying relational
algebra is by far the most helpful in understanding how "fast" your queries
will go, while looking at the normal forms and optimization often help with
an understanding of design trade-offs. Those design trade-offs are
important, but not nearly as important as SQL, unless you are creating
peta-byte scale databases.

Stefik

On Tue, Mar 2, 2010 at 9:31 AM, DaShiell, Jude T. CIV NAVAIR 1490, 1, 26 <
jude.dashiell@xxxxxxxx> wrote:

> That will be in most of the other data base books that advocate
> extensive planning.  It may even be in the book you're now reading.
>
>
>
> Rot47: <;F56]52D9:6==@?2GJ]>:=>
> -----Original Message-----
> From: programmingblind-bounce@xxxxxxxxxxxxx
> [mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of Homme, James
> Sent: Tuesday, March 02, 2010 10:26
> To: programmingblind@xxxxxxxxxxxxx
> Subject: RE: Planning A Database
>
> Hi,
> Where is good to go to learn about "First normal form?"
>
> Thanks.
>
> Jim
>
> Jim Homme,
> Usability Services,
> Phone: 412-544-1810
> Skype: jim.homme
> Internal recipients,  Read my accessibility blog
>
>
> -----Original Message-----
> From: programmingblind-bounce@xxxxxxxxxxxxx
> [mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of DaShiell,
> Jude T. CIV NAVAIR 1490, 1, 26
> Sent: Tuesday, March 02, 2010 9:32 AM
> To: programmingblind@xxxxxxxxxxxxx
> Subject: RE: Planning A Database
> Importance: Low
>
> I'll go a little further than that.  It's best not to attempt design of
> a working data base until the first normal form is thoroughly
> understood.  Not that that will be the form you use necessarily, but you
> will have it as a possible goal to consider during your design work.
> Second, there are always two fields that can be good to put into each
> table of a data base.  Those are comments and errors.  Any other fields
> you need or want in addition are fine, but by adding these two fields up
> front, you just bought yourself an organized place to document ideas you
> come up with as time goes on and errors that may happen and you can keep
> each separate from all other data and they're on each record so you can
> reference problems with specific record's other fields in these two
> extra fields if needed.
>
>
>
> Rot47: <;F56]52D9:6==@?2GJ]>:=>
> -----Original Message-----
> From: programmingblind-bounce@xxxxxxxxxxxxx
> [mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of DaShiell,
> Jude T. CIV NAVAIR 1490, 1, 26
> Sent: Tuesday, March 02, 2010 9:21
> To: programmingblind@xxxxxxxxxxxxx
> Subject: RE: Planning A Database
> Importance: Low
>
> Certainly planning as you go for a strictly personal data base can be
> done.  It's just best done with the understanding that you may export
> the data into an improved data base structure you design in the future.
> It's also a good idea to document your first working structure and then
> document problems you discover that prompt you to improve the structure.
> Each time you do an improvement which may mean moving to a new version
> or changing an existing version, it's a good idea to document what you
> do too.  This way you'll accumulate a journal of lessons learned as a
> result of your data base experience.  Possibly in future when you design
> a completely different data base, you'll do things differently and have
> well documented reasons for doing so at that time.  The extensive
> planning is only partly relevant for organizations and not even
> completely for them either.  Large organizations have both official and
> unofficial data bases in use at any time.  The unofficial data bases
> perhaps hold data in ways that are useful for what's held in them, but
> the technique for that unofficial data base for auditing requirements
> among others would not be acceptable for official data bases.  As and
> when information is needed from unofficial data bases, it can be and is
> often shared from unofficial to official data bases as a one way
> process.  The reverse is not true and the unofficial candidate data is
> cleaned up before it goes into the official data base if data base
> administrators are wise.
>
>
>
> Rot47: <;F56]52D9:6==@?2GJ]>:=>
> -----Original Message-----
> From: programmingblind-bounce@xxxxxxxxxxxxx
> [mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of Homme, James
> Sent: Tuesday, March 02, 2010 9:04
> To: programmingblind@xxxxxxxxxxxxx
> Subject: Planning A Database
>
> Hi,
> I got the book called Database Design For Mere Mortals. I liked it
> because it doesn't use jargon. It advocates planning the database as
> much as possible before you build it.
>
> Here's my question. Can you plan as you go? I'm asking because I'm not
> sure that I can figure out what all I need in my database. The learning
> curve is so steep that I'd like to break it down into small parts that I
> can bite off. Besides, the database is just for me, so I don't think I
> want to create the loads of documentation they advocate you create in
> the book. Does anyone have guiding thoughts on this?
>
> Thanks.
>
> Jim
>
> Jim Homme,
> Usability Services,
> Phone: 412-544-1810
> Skype: jim.homme
> Internal recipients,  Read my accessibility blog
>
>
>
> This e-mail and any attachments to it are confidential and are intended
> solely for use of the individual or entity to whom they are addressed.
> If you have received this e-mail in error, please notify the sender
> immediately and then delete it.  If you are not the intended recipient,
> you must not keep, use, disclose, copy or distribute this e-mail without
> the author's prior permission.  The views expressed in this e-mail
> message do not necessarily represent the views of Highmark Inc., its
> subsidiaries, or affiliates.
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
>

Other related posts:

  1. Home
  2. programmingblind
  3. Archive
  4. 03-2010