Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Class modules for a fragmented data structur (Access 97 and up)

    I recently posted 5 emails to an email based list that I am on. These emails explained how to use Class modules to handle a fragmented data structure. I am going to attach a zip file containing a word doc that has all 5 emails slapped together, along with the 'sample' database I used to show how it all worked together.

    It just seemed like a waste of a lot of typing to not spread it around a bit.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    Maybe you would like to explain what a "fragmented data structure" is for those who haven't encountered the concept before?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    Explain what a fragmented data structure is? Okay, here's Drew's tyrade on Relation Databases. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> ....just kidding.

    A relational database allows you to structure your data so that you do not repeat data. For example, if you had an Address list, and you had a family of 5 with the same address, instead of making 5 records with the same data, simply make 5 records with Names only, then put the addresses in a seperate table, linking the addresses with an ID field from the names table. You can then query your data so that all 5 names show up with the same address, but the address data is pulled from one record. There are many advantages to this method of data storage. Size for one, the less 'repeat' records you have, the smaller your database. Also consistency is nice. If the family of 5 moves, you only need to change one record instead of 5. As soon as that one record is changed, the address for all 5 people is automatically 'correct'.

    That example explains a semi-fragmented state. You have split the data across multiple tables, however, a simple query will pull up the data into one recordset. You can fragment your data even further. For example, in my original post's attachment, I describe a 'product' database. Instead of a 'prebuilt' product table, the structure is designed to allow for virtually any type of product. Products are grouped by a generic product type. That grouping puts products with the same 'specifications' together. In other words, you may sell cars, which would have specifications such as Engine size, Paint Color, Interior Color, Transmission type, etc. You may also sell Air Freshners, which would have specifications of shape, size, scent, color. These are two seperate 'product types', but you can list multiple cars under the 'car' type, and multiple Air freshners under their type. The table structure I explain in my attachment shows how to record both products in the same tables.

    To do this, there is a specification table, which lists the specifications for all product types. The product type is a field, so you can retrieve the specifications for each product type on their own. For the 'values' of the specifications, there are 5 tables (Currency, Date, Long, Double, and Text), which hold the data. The specification type determines which table holds the data for that specification.

    Is it possible to use this type of data structure without classes, yes, certainly. Is it a pain to do so? Absolutely. Using classes to store and retrieve the relevant data makes front end development a lot easier.

    Beware, reading my example will take you that much closer to the dark side of the force.......unbound forms! <img src=/S/cauldron.gif border=0 alt=cauldron width=20 height=20>

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    I would call this 'Normalized Form' rather than fragmentation. I guess something like '5th Normal Form', depending on how far one 'normalizes'.

    I come originally from a non-data-base background. Over the past couple of years i have seen much wisdom in the relational approach. Abiding by this wisdom directs me to do most of the DB work IN the DB, (using Access to write the DB native language, SQL), rather than in VBA, VBS, Delphi, etc.code. ISTM that using as little code as possible improves the portability of the DB.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    Correct, the 'industry' term is Normal Form. However, I figured that fragmented sounds clearer then NF. I wouldn't refer to a tuple, I would say field. (Even though field is an industry term, the relational term is tuple.

    I had been developing Access databases for a while before I had even heard of Normal Form. I had been using the 'theory', just didn't know the industry terminology.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    How is misusing a term 'clearer'?

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> What I meant by that, is that not everyone that works with Access knows what Normal Form is. If you say that term to someone who uses/develops in Access, but does not know the meaning from relational theory, they are probably going to think you are talking about a Form, and not a data structure. That's why I used the term fragmented data structure, since that describes higher levels of Normal Form.

    Does that make sense?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    I'm afraid I have to agree with Peter, Drew. I originally asked you to explain because I hadn't a clue as to what a "fractured structure" might be. Maybe an "atomic" structure might be a substitute description, since the records are being broken down into atomic parts? Unfortunately, if you provide a handy new phrase for people who don't understand what a "normal" design means, it just means that no one else who tries to answer their questions about "fractured structures" will understand what they're talking about either. At least "atomic" has some currency in the relational database world. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Class modules for a fragmented data structur (Access 97 and up)

    Atomic? Both Normal Form and Atomic can be taken in opposite directions from their meaning.

    Not trying to be argumentative, because I see where both of you are coming from.

    I'm such a rebel <img src=/S/jollyroger.gif border=0 alt=jollyroger width=29 height=18>. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    By the way, all these posts on my choice of words, and nothing on the sample database?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •