Difference between Designing Relational & Non-Relational Database

[Cicada]

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.

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!

 

[Guest]

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.

 

[Jerry Whittle]

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
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.

 

[Ron2006]

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

 

[Tony Toews]

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
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.

<shudder>

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

 

[Ron2006]

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

Hope you had a pleasant 4th.

Ron

 

[aaron.kempf]

listen fucknut

you're not good at designign databases in Excel.


Excel is a piece of shit format; who gives a **** about Excel?

go and read a database design book and flush the past 10 years of your
life down the drain.

Excel is for babies.

relational databases are the ONLY databases. Don't kid yourself.
Excel is a disease.

Spit on people that use Excel.

 

['69 Camaro]

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
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/conradsystems/accessjunkie/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/expert_contributors2.html for contact
info.

 

[Tony Toews]

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

 

[Ron2006]

Well, at least you celebrate "box day" close enough to call it
Christmas. (Or is it we celebrate Christmas close enough to call it
"box day". )

Our daughter-in-law is from VanCouver, BC. I love the way she
pronounces "out" and "about" and "Mum" isn't too bad either.

Ron

 

[Tony Toews]

Well, at least you celebrate "box day" close enough to call it
Christmas. (Or is it we celebrate Christmas close enough to call it
"box day". )

Well, we call it Boxing Day but I figured any place that celebrated
Christmas also celebrated Boxing Day. Many businesses just close that
entire week and expect you to take the days out of your holidays.

Our daughter-in-law is from VanCouver, BC. I love the way she
pronounces "out" and "about" and "Mum" isn't too bad either.

You folks down south do pronounce words wrong. Especially the letter
Zed. Took us a while to get a pint sized family member who learned
their ABC...Zsss from American software to get back on track on that
one.

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

 

[david epsom dot com dot au]

I learned pretty quick: a substitute teacher in 5th Grade
told me I didn't know my alphabet.

(david)

 

[Ron2006]

Boxing vs Box........

Ooops. I think Erin and my son would be boxing my ears for that one.

And just think..... it is only 167 more days till "Boxing day"

Have a great day.

Ron