A database is the solution, not the problem

I’ve heard it too many times. Folks just don’t understand what a database is. They think it is some type of a computer program kinda like a spreadsheet. ‘taint!

In ancient times, and yes, I am ancient. a “database” was simply a collection of files.

example: address file

“Dorothy Gayle”, “132 main street”, “ 91390”, “ Emerald City”, “Kansas”

“Arnold Ziffol”, “145 Broadway Ln”, “12345”, “Dallas”, “Texas”

“Bubba Gump”, “1475 Darby Street”, “12345”, “ Dallas”, “ Texas”

And then some bean counter noticed, hey we are duplicating information there are two records with the same zip code, street address and state can we do something about that? And the very wise hacker said, oh golly gee shucks, its a violation of DRY (Don’t Repeat Yourself).

So the hacker split the file into two files keyed on zip code

new Address file

“Dorothy Gayle”, “ 132 main street”, “ 91390”

“Arnold Ziffol”, “145 Broadway Ln”, “ 12345”

“Bubba Gump”, “1375 Darby Street”, “12345”

New Zip code file

“91390", “ Emerald City”, “Kansas”

“12345”, “Dallas”, “Texas”

(oh by the way, you can download an actual zip code file like this from the post office)

And everybody said Hooray, we quit duplicating data!! YEAH!! Ya hoo!!

and then Mertyl Mae said ok, now I need my mailing list, how do I do this?

Duh?

And then some hacker came up with a scheme. He said let create another file, call it a schema, and it will tell all about the files and how to connect them. It looked something like this.

SCHEMA FILE (no, I’m not yelling, just emphasizing)

Address File {name:string, address: string, zip:int5}

Zip File {KEY;zip:int5, City:string, StateAbbr: string2)

Connection: “address file”:zip > “Zip file”.KEY

And then the hacker came up with a program, called a database management system that used this schema file to create the mailing list by using the connection in the schema file.

The report description looked something like this:

PRINT Address file:name, Address File: address, Address File: zip, Zip File: City, Zip File: StateAbbr

and Mertyl Mae got her address list

Hey, lucky the hacker put the names of the fields in the schema so they could be used in the report writer. Yeah? (come on, say good thinking. Hackers never get the credit they deserve)

Now there is a little problem with this three file system. What if you need to change a field, such as adding a salutation field (Mr, Mrs, Ms)

For years this was done by altering the Address file and the Schema file, and that was OK, but every once in a while things got out of kilter. Someone changed the address file (or the zip file, you know the post office) and forgot to change the schema.

So some hacker said enough is enough. We are going to combine all three files into one file! Ya dije (that’s Spanish for an exclamation mark)! AND If you want to change a field (a column) or a connection you gotta go through my schema changer.

So the modern database was born, requiring a modern Database Management System.

And…The Address file and the Zip file lost their status as “files”. They became “tables” within the Database. The Schema, is also stored in the database so the tables never get out of wack with the schema.

So you end up with a Database Management System, that maintains a Database that contains Tables and a Schema.

You can add all the records (rows) to the table you want without messing with the schema. But if you want to add a new field, well, that requires a change to the Schema so you gotta go through the rigmarole of the schema changer.

If you think Ok , I got most of this, but there is still something gnawing at me. Why change the schema for columns but not for rows?

Because every row has the same structure. name, address, zip, city, state. The records all have the same column structure. The data is different for each record (row), but the structure is the same.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.