Fog Creek Software
Discussion Board




Why you should normalise your data

This is what happens when someone builds a database table that lets you type free form text rather than select text from a combo box. This is actual data from the client's database:

    SELECT distinct supply
    from statistics

    supply

     GOOD
     HEAVY
     MOD
     MOD.
    .5
    .GOOD
    .L
    .MOD
    4.
    ADEQ
    ADEQUATE
    AVERAGE
    FAIR
    FOOD
    GOD
    GOIOD
    GOO
    GOOD
    GOODD
    GOOF
    GOOG
    HAVY
    HEAV
    HEAVY
    HEAYV
    HEVAY
    HEVY
    HGO
    HIGH
    LIGF=HT
    LIGH
    LIGHAT
    LIGHT
    LLIGHT
    LOW
    M
    MED.
    MEDIUM
    MEDIUMG
    MEDIUML
    MMO
    MO.D
    MOD
    MOD.
    MOD..
    MOD.A
    MODERAATE
    MODERAT
    MODERATE
    MOERATE
    NOD
    ODD
    ODD HIGHER
    OOD
    SG
    SLOW
    STRONG
    V. LIGHT
    V.HEAVY
    V.HEVY
    V.LIGHT
    V/H
    V/HEAVY
    V/HIGH
    V/I
    V/LIGHT
    VERY HEAVY
    VERY LIGHT
    VERY MOD
    VERYLIGHT
    VHEAVY
    VHIGH
    VLIGHT
    VLOW
    VV/LIGHT
    VVHEAVY
    VVLIGHT
    
    
    
    
    SELECT distinct demand
    from statistics
    
    
    demand
    
    .ADEQ
    10.
    ADEQ
    ADEQ.
    ADEQU
    ADEQUATAE
    ADEQUATE
    ADQ
    ADQUATE
    AEQUATE
    AVERAGE
    DAEQ
    DEQ
    FAI
    FAIR
    FAIR'
    FAIR.
    FIAR
    FLAIR
    G0OD
    GB
    GODO
    GOO
    GOOD
    GOOD'
    GOOD/STRON
    GOOOD
    HEAVY
    HIGH
    HIGHER
    LIGHT
    MOD
    MOD.
    MODERATE
    POOR
    SKOW
    SLOW
    SLOW'
    SLOW3
    SLWO
    SPECIAL
    STONG
    STORNG
    STR
    STRO G
    STRONG
    STRONG.
    V.SLOW
    V.STRONG
    V/SLOW
    V/STRONG
    VLSOW
    VSLOW
    VSTRONG
    WAK
    WEAK
    WEAL
    WEK
    WEKA
    WEWAK

Name changed to protect the innocent
Tuesday, August 13, 2002

hey... how'd you get access to the horrid manually entered data i'm forced to work with on a daily basis??  oh wait - can't be our databases...  we have column names spelled differently.  labor in some places, labour in others. 

Nathan
Tuesday, August 13, 2002

I'd say that your datebase design is INADEQUATAE!

Alberto
Wednesday, August 14, 2002

GIGO comes to mind - garbage in, garbage out.

Patrik
Wednesday, August 14, 2002

if free form text rather than combo boxes is your only gripe, then you have nothing to worry about.

MS-access is cool, but it is like giving a gun to a teenager. More often than not, the result is bad.... just a glorified spreadsheet.

What pisses me more, is the address1, address2, .... address7 type fields. They then get really clever and add 2address1, 2address2, 2address3 etc. try printing labels with that table.

Anyone who has not grasped the concept (and importance) of relationships and data normalizing in RDMSs should not be allowed near ms-access.

What's more amazing is the number of i-did-a-computer-course-at-school type who want to charge stupid money for a spreadsheet with a wizard-generated-forms frontend.

I feel like pulling out my machete and getting african on their ass*s

tapiwa
Wednesday, August 14, 2002

Problems like this are annoying but not very hard to fix. And this could still be considered to be at least second normal form.

Really ugly normalization problems surface when you get things like multiple instances of the same column, or even worse, multiple values in one column.

Another thing I come across from time to time is a column which means different things depending on the value of other columns. Never do this - it will lead to untold confusion!

James

James Shields
Wednesday, August 14, 2002

>>MS-access is cool, but it is like giving a gun to a teenager. More often than not, the result is bad.... just a glorified spreadsheet.

Lets hope you mean that the lack of knowledge is the enemy here, and not ms-access. (by the way, the original poster never mentioned ms-access...not sure why it came up??).

In fact, why is there is in general so much nastiness towards ms-access. There seems to be two issues:

1) If a person develops in ms-access, then it is not professional.

2) Ms-access is not a good development tool.

I going to take a crack at #2

Ms-access is a remarkable tool. It is absolutely excellent for a small to medium sized business to create and develop applications with. In fact, one might ask why develop a data centric application in VB?

Ms-access allows one to develop a data centric application in about 1/3 the time of VB. That means a $12,000 project in ms-access will cost $36,000 in VB. I often get asked by bushiness if a application should be developed in VB or ms-access? I usually ask why are you asking? The response I hear 9 out 10 times is that some company or developer told them that ms-access is no good? Of course that company giving the advice wants the $36,000 dollars of work as opposed to the $12,000 grand!

Like any software tool, in hands of a pro, it works wonders. It allows creation of class objects, and the language is VB anyway. It is a great front end tool that works with a variety of database engines.

About the only thing I can see here is that there are a lot of poorly written msaccess applications, and that gives ms-access a bad name.

However, the worst business applications I see are those of a VB front end to a database...they tend to be *real* bad. (no use of Referential Integrally,  horrible normalizing, no use of lookups/related tables, tons of code everywhere with values and all kinds of stuff in code that belongs in tables, no consistent look from screen to screen).

A good VB programmer does not necessarily make a good database design and developer.

Ms-access is incredibility popular because the lowers the cost of creating data centric applications for business. Why would one use VB?

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Wednesday, August 14, 2002

Access is king baby

Daniel Shchyokin
Wednesday, August 14, 2002

" Ms-access allows one to develop a data centric application in about 1/3 the time of VB. That means a $12,000 project in ms-access will cost $36,000 in VB. "

This is very true for $36,000 projects but it does not (in my experiance) scale. A $100,000 VB project could possibly be done more cheaply in Access, but not at a third the price.  Larger than this and the economies can go the other way dramatically.

The problem I have with Access in this regard is twofold:

1. It is not inherantly designed for multiple developers to work on a prodject. Certainly it can be done but there can be problems. Some of the tools and Wizards in access can actually cause problems in this regard.

2. What is easy in Access is very easy but what is difficult is often almost impossible.

I have had more than a few Access applications were the budget has been blown by just one feature being difficult to implement.

It comes down to choosing the appropriate tools for the job.

JB
Wednesday, August 14, 2002

JB, your points are excellent!

The scalability of having more than one developer on the same project is a excellent example of a limitation.

I also agree with the “size”, and how much the project will scale with more than one developer. Visual Source Safe is supposed work with ms-access, but when you get that far, you are not likely to be using ms-access!

In addition, stuff like having a click-able map, and a rich graphical interface is not going to work well at all in ms-access either.

The distribution ability of VB is also *way* better, and produces a independent result of ms-office (the package & deployment stuff for ms-access is no where close to what p&d for VB is).

If you are writing a job costing system, then access is right at home.

Right tool = right job.

well said....

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Wednesday, August 14, 2002

I agree with Albert.... right tool for the right job.

I do not have a problem with ms-access. I have done some interesting things with it myselft. However, you do find that most people's exposure to databases is via access.

Because of its 'ease' people are more likely to jump in and shoot themselves in the foot with access (and all the dbs from the major suites ... corel, lotus).

The cli crew are just more likely to have a grasp of sql and database theory before diving in, than the access gui-wizard lot.

tapiwa
Thursday, August 15, 2002

Access?  Does it do Row Level Locking yet?

Greg Kellerman
Thursday, August 15, 2002

I've had a lot of problems with people who are expert on ms-access trying to use SQL Server for the first time. I don't know whether to blame Microsoft (hell, I will anyway) for coming up with two database products that work entirely counter-intuitively to each other, or to blame the guys for not doing enough reading around SQL Server. Actually, the problem is they don't even realise that stuff they're used to doing in access has totally unexpected results in SQL Server (copying and pasting for example). So, yup definitely Microsoft's fault - maybe SQL Server should come with some kind of health warning? (By the way, we are forced to use SQL Server even though there's no-one in the company who knows enough about it to administer it properly - and a little knowledge is a very dangerous thing!)

helga
Friday, August 16, 2002

*  Recent Topics

*  Fog Creek Home