Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    overnormalized database? (2000)

    I have attached a zip file containing just the tables. I may have gone a little Table-Creation crazy, but I'd like to know if there is some way to reduce the number of tables. The whole design was based on following an individual rat throughout it's lifetime. There are four ways to take images : MRI, MRS, PET, CT. Four types of tissue analysis : Histology, Molecular (Western and Gene Arrays), Elisa, Electrophysiology.
    I created tables like 'Workgroup' because a rat could be in multiple studies, or used by multiple people in various studies. I particularly struggled with 'Surgery' and 'Drug Treatment'. Drug Treatment keeps track of every injection the little guys have ever had. A surgery may or may not involve an injection, and an injection may or may not be part of a surgery. The problem is that I don't want any null entries in my tables, and that seems hard to avoid. I wanted to put an InjectionID in 'Surgery', but of course that would leave many null entries. Similarly with Drug Treatment, I wanted to put a SurgeryID in, but most injections would not be part of a surgery, and I would have many null entries. Trying to find out how many injections have been part of a surgery currently is going to suck. Any help or advice is appreciated.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: overnormalized database? (2000)

    Why are you so against Null entries?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Aug 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: overnormalized database? (2000)

    I thought it unnecessarily increased overhead for the database.

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

    Re: overnormalized database? (2000)

    No, null values don't really cause much bloat in a database. As to your original question regarding your database structure, I took a peek and think you have made it more complex than it probably needs to be. Three or 4 of your tables have a link to nearly all of your tables, which will really impact performance, and many of your tables are essentially a note field with a series of lookup indexes as the key. I think you might want to think more in terms of an event table with a series of things that can happen, and a table that defines when such and event occurred and any notes that need to be related to it. I've worked on Access databases since version 1.0, and have never seen an ER diagram with as many relationships as you have, so in my mind I immediately suspect that the structure is more complex than it should be. Of course the structure must deal correctly with the problem you are trying to solve, and I don't have enough details to suggest a more appropriate one. Hope this provides some insights.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: overnormalized database? (2000)

    >>I thought it unnecessarily increased overhead for the database. <<

    I've never heard of this. A null field should have no effect on the database.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: overnormalized database? (2000)

    Which shouldn't encourage people to litter their tables with records that are largely null, however.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: overnormalized database? (2000)

    >>Which shouldn't encourage people to litter their tables with records that are largely null, however.<<

    True, except I had the feeling he was jumping thru hoops merely to avoid even a single null field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: overnormalized database? (2000)

    That would, indeed, be a waste of time and effort.
    Charlotte

Posting Permissions

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