Re: Newbie question about db normalization theory: redundant keys OK?

From: raylopez99 <>
Date: Sun, 16 Dec 2007 09:33:12 -0800 (PST)
Message-ID: <>

On Dec 16, 3:45 am, "David Cressey" <> wrote:

> By an odd coincidence, I'm building a home accounting system in MS Access
> myself, as a hobbyist. I'm doing this as a hobby after retiring from a
> career in information systems.

The odd coincidence is exactly analogous to why the choice of a primary key is so important, and why tables should be normalized, since with 6 billion people (in 50 years to go to 9 billion) life is full of coincidences and unexpected/unplanned happenings.

> As nearly as I can tell MS Money and Quicken
> are both "single entry bookkeeping systems".
> In addition MS Access has a single entry bookkeeping system that one of the
> wizards can create for you right out of the box.

Really? I'll look for it right now...closest I found was "Transactions" under the Table Wizard and then a nice looking form by the Form Wizard would be "Justified" (looks like something that MS Money or Quicken would have). And I think you're right that Money / Quicken is single-entry. As a small businessperson I use double entry and appreciate it's I type this I wonder if double-entry violates any RDBMS normalization rules...maybe not, because of the way stuff is defined (Asset + Equity = Debit, so a duplicate is called something else on the LHS or RHS of the equation, so no violation)

> So why am I building my own, instead of using one of those? Well, I
> understand double entry bookkeeping, but I don't understand single entry
> bookkeeping. It is supposedly much simpler than double entry bookkeeping,
> but it just doesn't hang together, from my point of view. There are double
> entry bookkeeping products out there, like Quickbooks, but I'd rather
> build my own, after using some of them. Call it a quirk.

Nothing beats home made, beer, food, even code!

> MS Access is almost a toy for me, compared to the relational DBMS products I
> used professionally. (I include SQL DBMS products under the category
> "relational" although some of the regulars here object to that inclusion.
> All my database work was on SQL DBMS products.) However, unlike some
> regulars in here, I've got an appreciation for it, rather than treating it
> with mere scorn.

I like Access because of the front end. The back end (I just read the Wikipedia entry on JET) is below average to not bad from what I can tell (of course Access is Mickey Mouse, I think there's a 30 person limit on its networking capabilities), but the front end is superb. You can build a decent front end without touching a single line of code--awesome. And if you know SQL language you can check the drag and drop Relationships, and query wizard queries by hand to make sure they're what you want--a real time saver, the wizards are. As I'm learning Access and dBs, I'm learning how to code in C#.NET the front end for Access' successor, MS SQL Server 2005, using a book by Karli Watson, but I see what C#/SQL Server/ADO.NET calls "DataGridView" and "Detail View" is already (by and large) built into Access (of course they are all interrelated, since MSFT coded all of these family of products). And coding the front end for SQL Server in C#.NET is childlike  "drag and drop" with the wizards adding code, so unless you want to do other weird stuff I don't really consider this kind of coding much of a challenge, so I rather stick to the drag-and-drop non-code of Access. If I want to do real front end coding I'll do .Forms GUI (which I'm learning now as well). And any real program has at the core of it something that's all math and usually can be output as text in console mode anyway (my last such program, where for fun I proved Galton's point about family lines eventually all going extinct, is found here: [you need to have at least three boys for your personal surname to have a greater than 50% survival rate; you need at least 9 people with the same surname for your surname to have a >90% survival rate]

> And contrary to what one comment said, you can do data management with MS
> Access. At a very simple, desktop level, that is what it is for. It has
> limits. And some people have serious misconcpetions about databases based
> on their experiences with Access.
> But for what you and I are doing, it's plenty good enough. I'm only using
> Access 97. I won't even updgrade to 2000, nevr mind 2007, until I have a
> reason to. I mention this only because some things I say about Access won't
> be true in the version of Access you are using.

I plan to upgrade to Access 2007 just to get rid of the nag screen-- for older versions, apparently there's a Jet 4.0 Service Pack that needs to be installed, but already built into Vista and XP/SP2, but I still get annoying nag screens under Vista and XP (see here:

RL Received on Sun Dec 16 2007 - 18:33:12 CET

Original text of this message