Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Difference between Designing Relational & Non-Relational Database

Discussion in 'Information Technology' started by Cicada, Jul 28, 2006.

  1. Cicada

    Cicada
    Expand Collapse
    Guest

    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!
     
  2. Loading...

    Similar Threads Forum Date
    Difference between us and the Gurus? Sikh Sikhi Sikhism Dec 25, 2014
    Hinduism Difference between Sikhi and Bhakti Hinduism Interfaith Dialogues Mar 5, 2014
    Islam What Is Difference Between Sikhism And Islam - Dr. Zakir naik Interfaith Dialogues Jun 16, 2013
    1984 Vast differences between Jallianwala Bagh and 1984 Bluestar History of Sikhism Aug 1, 2012
    What is the difference between a knife and kirpan? Questions and Answers Jul 3, 2012

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

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

    Guest
    Expand Collapse
    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.




    "Cicada" <patwwh@hotmail.com> 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.
    >
    > 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. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    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.

    --
    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
    > 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!
    >
    >
    >
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

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

    Tony Toews
    Expand Collapse
    Guest

    "Ron2006" <ronnemec@hotmail.com> 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
    >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
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

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

    Hope you had a pleasant 4th.

    Ron
     
  9. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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

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


    "Cicada" <patwwh@hotmail.com> 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.
    >
    > 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!
    >
     
  10. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "Ron2006" <ronnemec@hotmail.com> wrote:

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

    Ron2006
    Expand Collapse
    Guest

    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
     
  12. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "Ron2006" <ronnemec@hotmail.com> wrote:

    >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
     
  13. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

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

    (david)

    "Tony Toews" <ttoews@telusplanet.net> wrote in message
    news:r66db2d9dau8okqogqth4jqdiqp0i986k5@4ax.com...
    > "Ron2006" <ronnemec@hotmail.com> wrote:
    >
    >>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
     
  14. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     

Share This Page