Constant Increments

[Jack Leach]

Ok ok, here's the deal on the normalization...


In the beginning, I had this set up on a one to many for required
documentation. I keep data regarding the company default requirements and
the actual purchasing requirements.

Anyway, at some point in time I decided that, for the company defaults, and
for the relatively little amount of data that had to be recorded, it would be
a bit less of a hassle to just store these directly in the company master
file (with integers, rather than yes/no, but thats a whole different bag of
worms).

In the past 15 years, these possible requirements have not changed, so I
thought maybe it would be a little quicker than running lookups on a seperate
table to store these couple of defaults. There's a number of different
sections of the app where these types of defaults are stored, but for each
group theres on average of five or six... the manufacturing process has about
10 possiblities but there's never been any requirement for more than 4 or 5,
and that at the very most. The rest are designed in just in case.

So anyway, at that point I figured, for the defaults (all *actual*
recordings of this information is still based on standard normalization, and
will continue to be)... but for the defaults I was basically keeping a bunch
of seperate tables for a few yes/no fields.

It's certainly much easier to check a field in the parent table than to set
up a relational table of one-to-ones just to hold these couple of yes/no
values. And myself being the only one that works with the app am intimately
familiar with it... and should I happen to fall out of the sky and come
screaming to my death, everything is well documented anyway. *If* there does
happen to be a requirement to add another field, it would take only a few
minutes to incorporate.

A month or so ago, I began to consider this setup once again, which
ultimately brought me to the original question I posted... thinking that I
still had no particular desire to hold these defaults in their own table, but
if I could contencate the values into a single field, it might work out well,
being a good way to store a large amount of information into a single field.

Granted, if the amount of defaults for a given group were to exceed 31
possibilities, I would have some major redesign to do. In the meantime, I've
got somewhere around ten seperate groups of yes/no fields with 4 or 5 fields
per group, that could be drastically cut back with this practice. And in 15
years in the business the requirements have not changed all that much...
certainly nowhere near a limit of 31 per group.

David, I agree with you 99.99% on never planning on limitations... in fact,
this is the only time I've ever broken that, and originally it was done in a
period where I was under pressure to implement a new requirement for
something else entirely, and seperate fields were easier to handle in my rush.

Probably what I *should* do now is scrap the idea and go back to storing
company defaults (and every other globally-scoped default) in a one to one
relationship. Because it's like the NY Lotto... Hey, you never know (New
York can certainly use a jackpot of about 6 billion right now...)

Thanks for speaking up... it gives me things to think on. In any case, for
years I've wondered about how these "incrementing constants" worked... until
today I had no idea how they were based, and this concept answers a plethera
of "I wonders".


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Banana]

Probably what I *should* do now is scrap the idea and go back to storing
company defaults (and every other globally-scoped default) in a one to one
relationship. Because it's like the NY Lotto... Hey, you never know (New
York can certainly use a jackpot of about 6 billion right now...)

I kind of got lost on that part mainly because I'm probably not
understanding why it has to be a one-one relationship.

Normally, when there is a need to store a default and record actuals, I
have two tables participating in a many-many relationship. The one-side
tables would be the entity that I need to track and the list of values
(in your cases, requirements) that may or may not pertain to the entity
being tracked.

In one many-many table, I store all defaults, while recording actuals in
other table.

In case where we have a actual record that should pick up the default, I
insert into the actuals based on the content of the default table.

Maybe example will help illustrate.

tblTrainees
-TraineeID (PK)
-TraineeName

tblCertifications
-CertificationID (PK)
-CertName

tblCertificationCategories
-CertCategoryID (PK)
-CertCategoryName

jctCertificationRequirements
-CertCategoryID (PK)
-CertificationID (PK)

jctTraineeCertifications
-TraineeID (PK)
-Certification (PK)
-DateAwarded

jctCertificationRequirements basically represents the "defaults" for a
given classifaction. Say we require a Database Developer to be certified
with the following certification: MOUS, MCT, MCP, and MCST.

We then create an entity for "Database Developer in the
tblCertificationCategories then add four entries in the
jctCertificationRequirements for each certification as required by the
position.

When we get a trainee, and this person is to be trained as a Database
Developer, we can have a form with a combobox to choose the Database
Developer from the tblCertificationCertification then use this SQL to
create the new entries in the jctCertificationRequirements:

INSERT INTO jctCertificationRequirements (TraineeID, Certification,
DateAwarded)
SELECT <Current Traine ID>, CertificationID, Null
FROM jctCertificationDefaults
WHERE CertCategoryID = <Database Developer ID>;

** See notes below.

Then you now have your defaults all loaded. There are certainly slightly
different ways to use it but the basic principle remains: This is just a
many-many relationship with some business rules which can be expressed
in the relational model just fine without needing to resort to coding.

Remember that while we can solve several problems with more coding, we
also have to think about whether we are making it easy to extract and
process the data as well maintaining the data. As I said earlier, we do
lose the compactability that bitwise flags give us, and this may be
actually useful in data warehousing, but we need to be careful
especially if we're working with "live" data which business may one day
change its requirement.


** Note: I should note that I violated one of my principles; adding a
record "just in case" by creating records in
jctCertificationRequirements without the trainee actually having taken
and passed any certification, and determining outstanding requirement or
completed requirement can be determined by just querying the
jctCertificationRequirements against jctCertificationDefaults using
frustrated outer join or inner join, respectively so there is no real
need to insert into jctCertificationRequirements for every trainee,
though this may make the form data entry a bit more straightforward.

I hope this kind of explain one reason why bitwise flags isn't as
commonly seen in this domain, as Stuart pointed out- it makes sense in
coding but not so much in data storage, especially when we desire to be
able generate complex queries and report upon those set of data. In case
of data warehousing where storage efficiency is much more important than
flexibility, this may be a different case, but we both know that Access
is hardly a data warehousing tool (not to say that it can't be a client
against such source but I digress).

I hope this helps...

 

[David H]

Yes scrape it, but consider it a learning opportunity as eventually you'll
find a use for it.

In terms of the defaults, the approach that I would take is to create a
child table to hold the defaults and then when a new purchase is created its
just a matter of copying over the defaults. So you'd have 4 tables that would
look something like this...

Products
ProductsDocumentRequirements
Orders
OrderDocumentRequirements

Although things haven't changed in 15 years, there's nothing to say that it
wont. This'll give you the flexibility to change as needed and allow new
Products to be added as needed. It will especially make life easier if you're
no longer there.