Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design help (2003)

    I am working on designing a database that will take usage statistics from numerous resources (normally counted once per month) and combine them into some sort of usable format for my co-workers (and boss!!). This is a huge project, involving statistics from approximately 100 different places. So far, however, I have been unable to think of the simplest way to design this database, since simply putting all the data in one table would not work due to the size.

    In my most recent attempt, I created a table for each resource and then put all of the statistics for each in that table. But, I've been unsuccessfully trying to display these stats in a combination ever since. I think what's hanging me up is relating the tables so that they will display properly in queries and forms. I've tried having one table with a list of all the resources and then linking that to each database, but that still isn't giving me what I want.

    The sort of display I have pictured in my head would look something like this:

    (User wants data for the month of June 2006)

    Resource Times Used Date

    Resource A 15 June 2006
    Resource B 22 June 2006
    Resource C 12 June 2006

    Anyone got any ideas? Feel free to ask questions if I haven't been descriptive enough.
    (And for the record, I have only created sample tables so far--no huge amount of data entry has been done!)

  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: Design help (2003)

    you wrote: >>since simply putting all the data in one table would not work due to the size. <<

    Size of what?

    You really need to describe the situation fuller. For example, are you reporting the same statistics from each resource?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2003)

    "are you reporting the same statistics from each resource?"

    Well, that's the goal. However, vendors can report whatever statistics they want. So, for one resource, I might have the number of times the resource was accessed, and for another, the number of times a search was run. My plan is to take two or three statistics that appear in most places and use those as much as possible. However, once I start asking for user input, I may have to add more types of statistics in the long run.

    So, if we're talking about 100 resources, each with statistical data for each month for say just the last two years (some of the data goes back further, but I'm not sure if we will use it or not), and if each month has it's own row in a table, that's 2400 rows--hence the reason I don't want to put all the data into one table.

    Does that help clarify?

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

    Re: Design help (2003)

    That's not alot of rows at all. I routinely work with clients that have in excess of 100,000 records in tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2003)

    Yipes! So, you think I should put all the data into one table, and it won't cause Access to run slowly?

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

    Re: Design help (2003)

    First of all, you should try to design your database so that you can conveniently use the data. Splitting the data into multiple tables and then having to somehow bring this data together would probably be far slower. If (and this perhaps should be a big IF) you find the database is slow after the proper design, then you do something about it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Design help (2003)

    I have a database where one of the tables has 1,500,000 rows, and it's indexed on appropriate fields and the response is brilliant.

Posting Permissions

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