Hand-written notes: Database design/theory

[Leo_Coroneos]

Hi everyone!

Last year while I was studying my so-called "Cert III" in IT and Digital Media, I thought it would be a good idea to hand-write notes on database design and the theory behind relational databases. Attached are some notes from my so-called "PHP/Databases/SQL/Web Design" exercise book; most of you experts won't find this useful at all, but I'm posting it for the beginners and fellow-intermediates who may need a quick refresher.

I am working hard to perfect my methodology of and approach to database design, hence my hard work in trying to sell databases to small and medium businesses. (See my other threads for more info on this; to hereby spill the beans, it's a music store/hospitality outlet for which I am working on my "WakesDBs.")

I really did well in our MySQL unit at SR TAFE (Western Australia). I am totally open to whatever anyone wants to add to the few "stating the obvious" notes I am attaching to this new thread. I want to be able to pass on what I already know after a solid year's study and graduation in Certificates III and IV in Information Technology.

I like this site a hundred times better than Stack Overflow & I am thriving on the attention and encouragement I've been getting on these forums. Keep up the good work guys! You're really making AWF a good place for someone like me... a naive, know-it-all intermediate-level newbie who needs a few intelligent words to get on the right track & go ahead to kick goals in business!

Despite being on these forums for only three days, I would LOVE to help the beginners on AWF, since I feel I have a solid grasp of the theory behind relational databases and RDBMSes, as well as a good skill-set in applied, practical database design. (Incidentally, please no-one snap at me if you don't like my notes If you do, I'll post more and maybe one day even write a guide of my own on relational database theory!)

 

[isladogs]

Here's a thread you might find useful to read

https://www.access-programmers.co.uk/forums/showthread.php?t=253162

 

[arnelgp]

i love it! Zip it and i will leech them all.

 

[isladogs]

And here's another good one to read ...
https://www.access-programmers.co.uk/forums/showthread.php?t=172363

 

[Leo_Coroneos]

Thanks ridders, just xferred to Word .docx the notes Tfurnivall posted on that first thread you cited, here they are in this attachment (see attached). I'll give that second thread a read in a few minutes' time.

To change the subject, people tell me I have neat handwriting. If this is true, it's because my mother told me to practice, practice and practice more. Nowadays I like how my handwriting looks and I hope everyone else does too.

@Arnelgp -- Coming right up! I'll just scan them in and post you the final product, my own notes on RDBMSes and DB-design theory, on this thread. Give me a few minutes and I'll get the notes online.

 

[Leo_Coroneos]

"To the experts in RDBMSes I speak my word." Here we go... This is the bulk of my handwritten notes on database concepts, with a cheeky paragraph on the end going off on a NoSQL tangent (definition of NoSQL ripped off--ahem, cited--from Wikipedia).

Five pages altogether, scanned from my book of arcane lore. Hope this helps, and do let me know if I should type all this up into a simple kind of guide book for the absolute beginner.

N.b. I'd really like to contribute to these forums and keep them alive and healthy, with all of us buzzing like Access-mad bees around the honeycomb of relational database theory.

 

[Pat Hartman]

@Leo_Coroneos,
Thanks for sharing your notes but the first note you took is actually inaccurate. Flat-files are significantly more common than relational databases. Just look at all the spreadsheets out there that are used as databases. Your professor should have qualified the statement.

 

[The_Doc_Man]

Concur with Pat, but with the following addendum:

Not only do many folks use Excel or other spreadsheets - or even text flat-files - as databases, but they wish they knew more so as to get away from the flippin' spreadsheets that don't really do what they wanted.

 

[Leo_Coroneos]

I think what I omitted to write was that relational databases are more commonly used than flat-file databases within the context of database design. Outside that context, Excel spreadsheets are more commonly used, even when Access would provide a better solution--correct?

 

[The_Doc_Man]

Well, ... maybe.

In the "real" world, "better" is a business term that includes technical, personnel, and economic factors.

If you limit yourself to technical issues, Access databases (vs. Excel worksheets) are far more versatile and powerfult.

If you have to train everyone and there is a learning curve, you find many cases where it is cheaper to use Excel. Also if there is a licensing issue because the boss bought the scaled-down version of Office and has to now buy a bunch of Access licenses, you have added economics to the fray. And there is the issue of maintenance costs. A simple-minded spreadsheet is far cheaper to maintain because you are less likely to need someone whose daily tasks include maintaining the spreadsheet. With Access, you will almost certainly need someone conversant with Access internals - i.e. a (semi-) dedicated maintainer.

So like ALL non-trivial things, there are multiple factors going into the word "better." I am actually on your side most of the time because I have a solid love-hate relationship with Access. But to be fair and accurate, I have been in offices where the other factors were more important.

Don't let me be a wet blanket here and smother your ideas, though. I happen to be a firm believer in the idea that when we share ideas taken from different viewpoints, we ALL benefit. SO... thanks for starting this thread.

 

[Leo_Coroneos]

I am actually on your side most of the time because I have a solid love-hate relationship with Access.

Heheh... most of the time, eh?

But to be fair and accurate, I have been in offices where the other factors were more important.

Don't let me be a wet blanket here and smother your ideas, though. I happen to be a firm believer in the idea that when we share ideas taken from different viewpoints, we ALL benefit. SO... thanks for starting this thread.

Wow, my pleasure. Did you happen to look at the other pages I wrote?

P.S. I've been wanting to share a resource on Access database design, but it's a PDF of 244KB. I might have to just copy/paste it into Word, citing the source, of course, too. Give me a moment, fellers, and I'll see what I can do.

 

[Leo_Coroneos]

Ok, here's the last page of this PDF reference simply entitled "Database Design Basics," copied and pasted into Word format with appropriate citation.

After all, the first Commandment of Access Database Design is: "1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin."

 

[The_Doc_Man]

I'll share with you a couple of my "Old Programmer's Rules"

Rule #1: If you can't do it on paper, you can't do it in Access.

Meaning: You have to understand the problem well enough to be able to lay out the transformations, operations, and gyrations you need to get the job done. If you can't lay out a road map, you aren't ready to implement. Which isn't to say you can't do some preliminary table layouts and such. You just have no hope of getting where you want to go without that roadmap.

Rule #2: Access won't tell you anything you didn't tell it first (or at least tell it how to figure out what you wanted).

Meaning: Access is not a subject matter expert. It cannot just "go out and find" something that isn't in your database (unless you teach it how to web-search, and if you ever do, PATENT that code instantly.) If you want X as output, there has to be some source of X (monolithic or individual components) on input. Which is why when you have laid out your list of things you want to accomplish, it pays you to STOP and backtrack every item in your wish list to see how the database app will acquire each output. This is actually an inverted corollary of "garbage in, garbage out."

These are not theoretical rules. They are the rules of a pragmatist who has experience in software engineering, the fine art of banging on code to actually MAKE that square peg fit in the round hole and then do something useful with it.

Heheh... most of the time, eh?

Yep, most of the time. When I'm on the "hate" side of that love/hate relationship with Access, .. I'll leave it to you to guess.

 

[Leo_Coroneos]

Yeah, for this particular project (Music Accounts and Orders DB) I did write a whole bunch of notes down as to what tables I wanted, what fields would go into the tables, and how the tables would be connected. Then I got an ER diagram happening in Draw.io (though I didn't have to, Access does it for you in the Relationships window, as you would know well). Then I created my Data Dictionary while getting the tables into Access. Anything I've missed?

Rule #2 is a good one for me; I often expect too much of Access only to realise I've omitted something seemingly insignificant. And damn, I hate having to start things over again, but that seems to be the way it works, unless perchance one is an expert. I guess you get better over time.

Thanks for those tips (or, "Rules"), Mr. Yankee Doc Man. There's a lot of smart guys on these forums, yourself inclusive.

 

[Pat Hartman]

I hate having to start things over again, but that seems to be the way it works

Don't blame Access for this. It doesn't matter what environment you develop in, flaws in design are more costly to fix the further into development you get. If something might cost $10 to fix if all you are doing is changing a mis-statement in a spec document, it could easily cost $10,000 to fix if you don't find the problem until user testing.

I've been designing and developing apps since 1968 (with the scars to prove it) and I can tell you that it is always easier to fix something correctly than to add workaround after workaround for the entire lifetime of the application. Always find the actual problem and fix it as early on as you can.

 

[Leo_Coroneos]

Yeah, that's what I'm working on now: revising the design. Hell, it's just a (very) small database and I can afford all the time in the world, since I'm on a pension from the Australian government--yes, at my age hahah. However, I was convinced I'd got the design right until errors cropped up in my reports. Long story. A few sympathetic people have been attempting to help me, which makes me realise just how stubborn I can be when offered help or advice.

"No, no, NO! I want to do it MY way!" And so on and so forth.