Normalization Limitations

[Blake]

RE: Normalization Limitations

John,

It might not be a bad idea to post your simple example and see if anyone wants to take a shot at trying to do finds and sorts while the data is normalized.

Yeah. I'm pressed for time these days, but I'll try to work it in. (I'd be more aggressive if I thought we were going to come up with a different answer.)

Certainly an important question in your particular case is the potential size of the table - miilions? thousands? or what.

Nah. It's what I'd class as a "trivial" amount of data. My feeling is that access should be more or less instantaneous but then, that's my bias. :-) Multiple thousands of records could build up over several years--I'd say 10,000 would be pretty amazing.

[Blake]

RE: Normalization Limitations

There are those who would say this discussion is esoteric and overly technical. Well, they are probably right. Many users of Alpha Five have never heard of normalization or have no real clue what it means.

A5 is a technical product; it requires technical knowledge to use it. Many of the principles of normalization and relational databases are going to present themselves to people who work with datbases, whether or not they know the formal technical names.

To those who might object to a "technical" discussion, I say: We are all presented with much the same problems. The question is, when those problems arise, are we going to be aware of how they've been handled by others, or are we simply going to keep reinventing the wheel?

I am not a programmer by trade and have not studied programming concepts and conventions a great deal. I suspect many users here have a similar level of knowledge and experience.

Perhaps even most.

When I took computer programming many many years ago, the big concern was how to use as few lines as possible since creating punch cards was a pain.

Heh.

However, this is a very valuable discussion since many users don't consider the consequences of their methods. It seems some developers want to build forms, write code, and write reports and spend little time building the underlaying structures.

All of which present myriad "technical" issues to be resolved.

Some of the table and database structures I have seen suggest that there is minimal thought about how to design the structures in any consistent or logical pattern. The lack of any normalization is just one of the issues.

Another one, common in Access, is a penchant for making all string fields 255 characters long. That'd actually be okay, except for the "technical" fact that Access' underlying storage method is a fixed-file, just like the DBFs used by Alpha, and it doesn't handle large records very well.

My main concern is maintaining data integrity and storing the same information in more than one place just seems wrong and an invitation to disaster.

Yep. The more you work with any programming tool, the more the problems--and often the solutions--will present themselves to you.

I have spent some time trying to understand normalization rules and what I know suggests they are very good rules. But rules are arbitrary contructs. They just don't work in every situation.

This is true. But it's important, IMO, to establish where the problem lies. In this case, for example, the problem could be solved by providing hooks into the "find" function. There's really nothing wrong with the bulk of A5's handling of the situation.

For example, they work extremely well for such things as invoicing

Yep.

I could assign a "fixed" identifier and link each instance the value is needed on the identifier. But now I am still saving the same amount of data, just a different field.

Normalization is not about optimization. Indeed, where R-theory falls apart (IMO) is the inability of developers to follow the 12-rules to make a product that could actually be used for something. (AFAIK, all RDBMSes cheat.)

If I need the actual value, I either have to link the table in a set or use a lookup function. Both add complication, and lookup functions have a speed hit.

I would consider the added complication a weakness in A5. The speed hit, on the other hand, is a definite side-effect of some aspects of R.

Or,I can just forget normalization issues and save the actual value. If the value does not change often, updates across the various tables are infrequent and not difficult to control.

But you introduce integrity issues, as a side-effect of your choice. You also get a speed hit which might be inexplicable to the user (relative to the lookup). And you drastically increase the ilkelihood of a problem with record-locking in a multi-user situation.

Every decision should consider the tradeoffs. Rules should exist to help us make decisions, not force us to make bad decisions. I try very hard to normalize and don't like to compromise. At the same time, I am not afaid to break a "rule" if the result is just as secure and actually reduces complication. A rule is there to help. Don't ignore it, but if it doesn't work, chuck it.

Sound advice.

My personal practice has always been to push rules, theories and paradigms to their extremes. Apply them ruthlessly in all kinds of situations. That's how I find out where they break (or aren't applicable), and it also helps me understand the limitations of the tools I'm working with.

It's also important to get other perspectives so that you know: a) that you really "get" the rule in question, and; b) that you really understand your tool.

===Blake===