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

"raylopez99" <raylopez99_at_yahoo.com> wrote in message news:29efb735-3af9-4597-897e-ffa823f5d7f4_at_q77g2000hsh.googlegroups.com...
> On Dec 16, 3:45 am, "David Cressey" <cresse..._at_verizon.net> 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 value...as 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)
>

Here's where the single entry bookkeeping system appears in the version of Access I have:

You launch MS Access with no database to work on, and it gives you a screen with radio buttons for "Blank Database" and "Database Wizard". You then select the Wizard named "LEDGER". Then you answer the questions.

Don't let this deter you from building your own. See below.

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

Even when I was doing professional work, I would occasionally use MS Access for some purposes.

In one case, I was able to set up table links to the Oracle Database, and then use the Relationship tool to create a schema diagram of the Oracle schema I was building. There are tools that will do this. They are pricey, and they have their own learning curve. MS Access was on my dektop already, and the learning curve was virtually zero.

I was able to use the resulting diagram as a way of bringing my manager up to date on how the database design was coming along. It was both more effective and less time consuming than writing "progress reports".

One time, I was building a data mart that would compare, for every item purchased via the website, the selling price with the standard purchase price for the same item. The enterprise used a SQL Server package for their inventory system, and an Oracle package for their order processing systems. The two systems wuold not talk to each other. A programmer estimated two weeks to write a bridge progam.

I went back to my desk, and linked to both systems with MS Access. Within 20 minutes, I had the selling prices and the purchase prices next to each other, for about 20,000 products.

One time, I was gathering performance data on the real database, and I wanted to do some SQL like queries on the resulting data. There's a good reason why you don't want to do the analysis on the same system you are examining. So I used MS Access to do the analysis.

And so it goes.

The tool is a very humble tool, but you can use it to good effect if you know how. And all I did was without writing a single line of code!

 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 child-
> like "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: http://tinyurl.com/2kwwrs [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]

I love what the wizards can do for me in MS Access. But one thing I've noticed
is that, in almost every dialog, the first question is the killer. It's something like "So where's your data, bunkie?"

So it behooves you to have a real good dataflow mapped out, if only in your mind's eye, before you start asking the Wizards for help. It also behooves you to know what formats of data is going to be easy for Wizards to gulp down.

>
> >
> > 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:
> http://support.microsoft.com/kb/239114)
>

That's one of the reasons I stay with Access 97. It's too primitive to harass the customer!

> RL
Received on Sun Dec 16 2007 - 20:02:41 CET