Difference between Designing Relational & Non-Relational Database

#1 (**permalink**) 28-Jul-2006, 08:29 AM

I am very well in designing Database in Excel.
To optimize its speed, in my experience, Table Format usually should not be
"Normalized".
"Use Space to exchange Time" is often important skill.
Sometimes, I even need to create Index-Table myself to teach Excel to search
in order to get better performance.

However, I am new to Database.
Therefore, I have read books about Database (All talk about relational
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12444-difference-between-designing-relational-non-relational.html
database only, but not the traditional one) and find no problem in its
difference to Excel now.
I also understand the several "Theoretical" Normalization steps.

However, I feel very very struggle in how to optimize my 1st & real Database
design, and I have no time to "try and error" for this project.
Database program gives me too much choice in table design.
I can set the table to fully normalized, or partially normalized, or even
Not normalized.
Some people tell me that, in practical work, fully normalized table may not
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
offer the best optimization, and some skills in Excel may also be useful.
But I cannot further find this kind of information in detail.

Also, another critical point is:
My adopted development tools is for Pocket PC, which is not as fully
Relational as MS Access.
i.e. I must know the traditional database design concept also, to design
this partial relational database. (e.g. Skills of Data Searching, matching,
.......)
(Is it similar to Excel as I think?)

Could anyone kindly provide me some useful website or advice? Many Thanks!

#2 (**permalink**) 28-Jul-2006, 08:29 AM

Cicada wrote:
> I am very well in designing Database in Excel.
> To optimize its speed, in my experience, Table Format usually should
> not be "Normalized".
> "Use Space to exchange Time" is often important skill.
> Sometimes, I even need to create Index-Table myself to teach Excel to
> search in order to get better performance.
>
> However, I am new to Database.
> Therefore, I have read books about Database (All talk about relational
> database only, but not the traditional one) and find no problem in its
> difference to Excel now.
> I also understand the several "Theoretical" Normalization steps.
>
> However, I feel very very struggle in how to optimize my 1st & real
> Database design, and I have no time to "try and error" for this
> project. Database program gives me too much choice in table design.
> I can set the table to fully normalized, or partially normalized, or
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
> even Not normalized.
> Some people tell me that, in practical work, fully normalized table
> may not offer the best optimization, and some skills in Excel may
> also be useful. But I cannot further find this kind of information in
> detail.
> Also, another critical point is:
> My adopted development tools is for Pocket PC, which is not as fully
> Relational as MS Access.
> i.e. I must know the traditional database design concept also, to
> design this partial relational database. (e.g. Skills of Data
> Searching, matching, ......)
> (Is it similar to Excel as I think?)
>
> Could anyone kindly provide me some useful website or advice? Many
> Thanks!

Sorry but I would not know where to start. Proper optimizing -
normalization of a database is as much of an art from as a science. With
out knowing the data structure well, I would not try to begin to offer
suggestions. As for Pocket PC, I know of it, but I know nothing about it,
another unknown.

The only advice I can offer, is that if you are working with Access,
97.843% of the time you want to normalize for best performance and
flexibility.

--
Joseph Meehan

Dia duit

#3 (**permalink**) 28-Jul-2006, 08:29 AM

Fully normalize if possible.

Partially normalised or de-normalised databases are for

* read-only databases
or
* complex data structures only if the database engine fails.

"Cicada" wrote in message
news:e13QQ6enGHA.4364@TK2MSFTNGP05.phx.gbl...
> I am very well in designing Database in Excel.
> To optimize its speed, in my experience, Table Format usually should not
be
> "Normalized".
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
> "Use Space to exchange Time" is often important skill.
> Sometimes, I even need to create Index-Table myself to teach Excel to
search
> in order to get better performance.
>
> However, I am new to Database.
> Therefore, I have read books about Database (All talk about relational
> database only, but not the traditional one) and find no problem in its
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
> difference to Excel now.
> I also understand the several "Theoretical" Normalization steps.
>
> However, I feel very very struggle in how to optimize my 1st & real
Database
> design, and I have no time to "try and error" for this project.
> Database program gives me too much choice in table design.
> I can set the table to fully normalized, or partially normalized, or even
> Not normalized.
> Some people tell me that, in practical work, fully normalized table may
not
> offer the best optimization, and some skills in Excel may also be useful.
> But I cannot further find this kind of information in detail.
>
> Also, another critical point is:
> My adopted development tools is for Pocket PC, which is not as fully
> Relational as MS Access.
> i.e. I must know the traditional database design concept also, to design
> this partial relational database. (e.g. Skills of Data Searching,
matching,
> ......)
> (Is it similar to Excel as I think?)
>
> Could anyone kindly provide me some useful website or advice? Many Thanks!
>
>

#4 (**permalink**) 28-Jul-2006, 08:29 AM

If you go berserk and normalize your data to the 5th Normal Form, you can
almost guarantee that the database will not run. However most databases never
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
get anywhere near that level. I try to optimize to the 3rd Normal Form then
denormalize where there are performance problems or maintenance issues.

Now before you think that normalization isn't really needed, it's a rare day
that I find an Access, or even Oracle, database anywhere near the 3rd Normal
Form. Without a doubt the biggest problem with databases is the lack of
normalization which will support the business rules. That causes strange
"jumping through hoops" code and SQL.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Cicada" wrote:

> I am very well in designing Database in Excel.
> To optimize its speed, in my experience, Table Format usually should not be
> "Normalized".
> "Use Space to exchange Time" is often important skill.
> Sometimes, I even need to create Index-Table myself to teach Excel to search
> in order to get better performance.
>
> However, I am new to Database.
> Therefore, I have read books about Database (All talk about relational
> database only, but not the traditional one) and find no problem in its
> difference to Excel now.
> I also understand the several "Theoretical" Normalization steps.
>
> However, I feel very very struggle in how to optimize my 1st & real Database
> design, and I have no time to "try and error" for this project.
> Database program gives me too much choice in table design.
> I can set the table to fully normalized, or partially normalized, or even
> Not normalized.
> Some people tell me that, in practical work, fully normalized table may not
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
> offer the best optimization, and some skills in Excel may also be useful.
> But I cannot further find this kind of information in detail.
>
> Also, another critical point is:
> My adopted development tools is for Pocket PC, which is not as fully
> Relational as MS Access.
> i.e. I must know the traditional database design concept also, to design
> this partial relational database. (e.g. Skills of Data Searching, matching,
> .......)
> (Is it similar to Excel as I think?)
>
> Could anyone kindly provide me some useful website or advice? Many Thanks!
>
>
>

#5 (**permalink**) 28-Jul-2006, 08:29 AM

The primary places where I purposfully depart from the "Normalization"
scheme is where I have to creat history types of reports. For instance.
What did the invoice really look like then, even though the part
description has changed and the price is now twice as much. What was
the quoted price on the invoice at the time of printing even though it
has gone through 2 price increases since then?

What was the payroll check for and to whom was it made out to even
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
though Jane Rogers has since gotten married and is now Jane O'Leary.
That will drive an auditor crazy when he can't find the check for Jane
O'Leary (what a fully normalized report would say) because she was Jane
Rogers then.

I have seen some places where they have simply taken complete backups
of app and tables and saved them AS OF that time/date and any reports
for that time frame HAVE to be gotten from those files.

Just some thoughts to throw into the mix when designing the
application.

Ron

#6 (**permalink**) 28-Jul-2006, 08:29 AM

"Ron2006" wrote:

>The primary places where I purposfully depart from the "Normalization"
>scheme is where I have to creat history types of reports. For instance.
>What did the invoice really look like then, even though the part
>description has changed and the price is now twice as much. What was
>the quoted price on the invoice at the time of printing even though it
>has gone through 2 price increases since then?
>
>What was the payroll check for and to whom was it made out to even
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
>though Jane Rogers has since gotten married and is now Jane O'Leary.
>That will drive an auditor crazy when he can't find the check for Jane
>O'Leary (what a fully normalized report would say) because she was Jane
>Rogers then.

But as far as I'm concerned that's nothing to do with normalization.
You are simply storing the data, such as cost or price, as of that
moment in time. Quite reasonable and that does not violate
normalization.

>I have seen some places where they have simply taken complete backups
>of app and tables and saved them AS OF that time/date and any reports
>for that time frame HAVE to be gotten from those files.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

#7 (**permalink**) 28-Jul-2006, 08:30 AM

Thanks for the comment. It's good to know that I really wasn't breaking
any rules.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444

Hope you had a pleasant 4th.

Ron

#8 (**permalink**) 28-Jul-2006, 08:30 AM

Hi, Cicada.

> Sometimes, I even need to create Index-Table myself to teach Excel to
> search in order to get better performance.

That's because the organization of your data in a single flat table
structure is inefficient.

> Therefore, I have read books about Database (All talk about relational
> database only, but not the traditional one)

It's not clear whether the "traditional" database you are refering to is the
traditional hierarchical database or the flat file database, but since your
experience is with Excel spreadsheets, I'll assume you mean flat file
databases. It should be no surprise that it's difficult to find database
books that focus on anything other than relational databases. Relational
databases (or object-relational databases) are the most widely used in the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
industry, because these database structures prevent many of the data
problems found in other database organizational structures.

> I also understand the several "Theoretical" Normalization steps.

Do you also understand the reasons for each of the Normal Forms? Proper
normalization prevents data anomolies and duplicate records -- shortcomings
that are inherent with flat file databases. Proper normalization and
integrity constraints safeguard data integrity.

> However, I feel very very struggle in how to optimize my 1st & real
> Database design, and I have no time to "try and error" for this project.

Then you are doomed to repeat many of the mistakes that beginning relational
database developers make. After you gain more experience, you will need to
revisit this first database and fix the problems you will undoubtedly
create. Keep in mind that the further along in the design when mistakes are
eventually fixed, the more time-consuming and expensive they are to achieve,
so learning how to do it correctly the first time will be the most efficient
way to develop the database design and the database applications that use
it.

> Some people tell me that, in practical work, fully normalized table may
> not offer the best optimization

If you focus on "optimization" -- performance, I take it -- instead of data
integrity by avoiding normalization, then you, or your customers, will
discover data insertion anomolies, data deletion anomolies, duplicates,
"missing" data, or fan traps. When avoiding normalization, one needs to
fully understand why that particular Normal Form is required, why a certain
situation can't use this Normal Form, and how to ensure that the data
anomolies this particular Normal Form is designed to prevent are otherwise
prevented with business logic (i.e., procedures written in a procedural
programming language to do what that Normal Form does for the data).

> and some skills in Excel may also be useful.
> But I cannot further find this kind of information in detail.

In general, Excel skills and relational database design skills serve
different purposes. Excel skills generally manipulate and provide for the
presentation of the data, including number crunching, while relational
database design skills concern the architecture of how the data is stored
and retrieved. It's hard to see which specific spreadsheet skills you have
that may help you build the architecture of a relational database that
ensures data integrity, so I can't really offer advice on how to leverage
those spreadsheet skills for your first relational database.

> My adopted development tools is for Pocket PC

> (Is it similar to Excel as I think?)

You probably need to ask that question in a newsgroup dedicated to Pocket
PC, not this newsgroup, to get opinions from a better perspective.

> Could anyone kindly provide me some useful website or advice?

For relational databases in general and Access specifically, see Access MVP
Jeff Conrad's (AKA the Access Junkie) Web site for a list of resources to
get you on the right track:

http://home.bendbroadband.com/conrad...resources.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

"Cicada" wrote in message
news:e13QQ6enGHA.4364@TK2MSFTNGP05.phx.gbl...
>I am very well in designing Database in Excel.
> To optimize its speed, in my experience, Table Format usually should not
> be "Normalized".
> "Use Space to exchange Time" is often important skill.
> Sometimes, I even need to create Index-Table myself to teach Excel to
> search in order to get better performance.
>
> However, I am new to Database.
> Therefore, I have read books about Database (All talk about relational
> database only, but not the traditional one) and find no problem in its
> difference to Excel now.
> I also understand the several "Theoretical" Normalization steps.
>
> However, I feel very very struggle in how to optimize my 1st & real
> Database design, and I have no time to "try and error" for this project.
> Database program gives me too much choice in table design.
> I can set the table to fully normalized, or partially normalized, or even
> Not normalized.
> Some people tell me that, in practical work, fully normalized table may
> not offer the best optimization, and some skills in Excel may also be
> useful.
> But I cannot further find this kind of information in detail.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
>
> Also, another critical point is:
> My adopted development tools is for Pocket PC, which is not as fully
> Relational as MS Access.
> i.e. I must know the traditional database design concept also, to design
> this partial relational database. (e.g. Skills of Data Searching,
> matching, ......)
> (Is it similar to Excel as I think?)
>
> Could anyone kindly provide me some useful website or advice? Many Thanks!
>

#9 (**permalink**) 28-Jul-2006, 08:30 AM

"Ron2006" wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12444

>Thanks for the comment. It's good to know that I really wasn't breaking
>any rules.

Yeah, I was thinking that when I read your posting. Sometimes the
academic descriptions of normalizing don't mention some of these
details.

But then I've never taken any significant programming or database
design courses of any sort in my 27+ years of programming. Well, ok,
two. But one was a beginners course in PL/1 in about '76 or so. And
the other was Watfiv S in about '79 but I had previously taught myself
the material while in high school at the local college in about '75 or
so.

>Hope you had a pleasant 4th.

Nope, hard at work. But I had a great 1st of July.

Tony (Canadian)
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm