Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Address book normalisation (All)

    I'm interested to see what others think, but IMHO you've described the dilemma adequately.

    When I started to work where I'm now, they had a very normalized address/contacts database that had more or less been abandoned because the secretaries found it very difficult to work with it. Despite the normalized structure, and checks built into the forms to enforce it, the same information had been entered over and over again. After consultation with the secretaries and others who had to work with these data daily, I created a much simpler partly denormalized database. It may not satisfy the strict rules of database design, but users find it very pleasant and intuitive to work with, and it is used intensively (relatively speaking) without many problems.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Address book normalisation (All)

    As Hans indicated, you've summarized things pretty well. In contact database where you don't have huge numbers of people, the moderately de-normalized type of database works well. In a more complex situation where we were tracking 100K+ people, with multiple address, both postal and electronic, we did go to a pretty well normalized scheme, but we didn't try to allow people to share addresses. ln other words, if two people lived at the same home address, we entered a home address for each, even though it was the same. The challenge in trying to normalize beyond that is that people move, split up and so on, and that maintenance task becomes horrendous with a fully normalized design.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Address book normalisation (All)

    Hey,
    I wonder, as address books are probably the most common database types ever: does anyone know any free Access databases/examples with a very normalised table structure (like Leszinsky's example below)? Or did anyone already develop such database already and found it worth the effort (did the added value of the normalisation,... outweigh the effort spent in developing the user interface,...)?

    I'm curious as I already saw so and started some examples (1), but few that could 'generally' satisfy the needs. Either do they have simple table structure but limited possibilities, or they have more possibilities but a heavy user interface to develop. Probably this is just a natural law, nothing comes for free, and can't you avoid to adapt your data model each time to the circumstances, but... I'ld be happy with any links or tips about developing a fairly normalised address book, before I accept my faith (probably temper my needs too) and start developing again...

    (1) My limited experience...
    - Most address books are flat databases in which you need to enter too much duplicate information.
    - At my previous job, I started an address book for my own use, which included a fair number of private addresses. I had a tblAddresses with all names & address information of organisations & private persons, and a tblContacts with the professional contact info (IDaddress + function, telephone & e-mail address at work,...) of the employees/board/council members of those organisations. This allowed a many-to-many-relationship between persons & organisations (as private persons could have more than one function at different organisations). Further, the enforced person's referencial integrity between both tables, avoided that employees could entered in the contacts table which weren't registered as private persons yet.
    In my current job, however, this shouldn't work as for most of the people, you don't have even a minimum of private information. Thus, you won't be able to find out whether you're dealing with the same person when you enter a contact with an already existing private person's name, etc. As such, I needed to denormalise and broke up relationship between the employee info in tblContacts and the few 'personal' addresses in the tblAddresses table, and added the employee names to the contact information table (too). This database will be less consistent (as the link between private persons & employees is gone) but easier to build (simple forms, less 'not in list' event handling,...). In both cases, however, I will need to use union queries to present a complete list of phone numbers or e-mail addresses (organisations + contacts).
    - In Access 97 Expert Solutions, Stan Leszynski showed a very shattered/detailed model for contact information & address books. In this modell, all telephone numbers/e-mail addresses/... were gathered in the same table (with, I think, some referring key fields + 'type' = e-mail/tel/fax + 'value'=address/number). Here, a union query to unite those information in a single list wasn't needed no more. Moreover, it provided a solution for having an indefinite number (no/one/several) of 'contacts' (telephone or fax numbers, e-mail addresses) per employee (which in the previous examples must be solved by entering multiple values in one field or using multiple fields which mostly remain empty and require quite havy queries to list or search those fields simultaneously). However, I never used this model, as it appeared to me loads and loads of work to design forms & procedures to make all this user friendly & available for my collegues' use. (Finding proof here that sometimes denormalisation is needed to achieve a 'workable' database...)

    p.s. I'm not really satisfied with my question ( <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15> perhaps) but I hope I made myself clear enough <img src=/S/confused.gif border=0 alt=confused width=15 height=20> and didn't waste your time...

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Address book normalisation (All)

    Hi

    Just to pick up on Hans' point on entering the same data over again. This is a real problem in our organisation and I'm personally at a loss on how to minimise it. It seems users always manage to invent ways to miss finding an existing record, and re-enter it again eg Acme NZ Ltd, Acme New Zealand Ltd, Acme New Zealand Limited all being one in the same, and heaven help us if there is a dot somewhere in the company name eg pty. Ltd

    I know the easiest solution is robust training such as entering the fewest number of characters of a company name before using a wildcard. However, users always seem to forget to do this. So we (meaning I in a number of instances) have to fix things up, as by the time it is picked up, the duplicate company is already linked to other tables with a number of records

    Well that's the moan of the day.

    Regards
    WTH

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

    Re: Address book normalisation (All)

    One approach is to use a function that removes all punctuation like commas, periods, etc., as well as multiple contiguous spaces, from the record and not allow those to be reinserted. Users are very creative about adding a space to the beginning or somewhere in the middle of a name to get around the duplicate key or index message, and I've dealt with that problem in lists that contained hundreds of thousands of names. It was a constant battle and required a great deal of effort on my part as developer and administrator, but I learned early on that trying to use third normal form in an address table was simply not going to work ... particularly so since there are zip codes in the US that are shared by more than one community, while some buildings have their own zip codes.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Address book normalisation (All)

    Thanks Charlotte

    I think you hit the nail on head when you stated it is a constant battle!. I agree clever functions assist, but they can only go so far when operating against your typical 'lets find an inadvertent way around the system' user!

    On the bright side, without problems we would be out of work!

    Regards
    WTH

Posting Permissions

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