Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    DB Structure options (A2K SR1)

    I am at a point where I have to add new data to one of my oldest databases, and am looking for some input on some alternate design structures. The existing database was set up with all the numeric fields using 'double' as the field size type. Over the weekend I rebuilt the table using more appropriate field sizes and was able to cut the overall size down by about 25%, but it is still one huge table with multiple indexes (2.7 million records, autonumber primary index, plus 5 other indexes). I need to add approximately 1.8 million more records this year.

    The options that I am looking at right now include, but are certainly not limited to:
    1. <LI>Continue adding to main big table -- completely unnormalized
      <LI>Create sub-tables for repetitive data and link to main via lookups
      <LI>Parse the data into a separate main table for each year, and use a union query to view the whole as a single table
    I used the second method with one of my other large databases, and was quite happy with search times and overall size. If I index the sub-table, do I need to index the field in the main table as well? My main concern is the speed that the data can be pulled from the table(s), followed by overall size.
    Attached Images Attached Images

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

    Re: DB Structure options (A2K SR1)

    The option you should be looking at is SQL Server. This large a database is just waiting to bite you as you approach the 2 GB limit in A2k.

    I'm not sure what you mean by your second option. Are you talking about things like multiple phone numbers associated with a record or about something like a lookup table of types of phone numbers?
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DB Structure options (A2K SR1)

    'Lookup tables' are pretty much what I had in mind. One field has only four possible values (single text character), another is a number/byte field with only 53 possible values... yet these values are being stored with each record. I can build an indexed lookup table for these fields and have them linked to the main table -- this will save a considerable amount of space -- but will I also need an index of these values in the main table to retain the lookup speeds (ie, both the TblPayFlag and TblPayHist have an index on their respective PayFlag field).

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

    Re: DB Structure options (A2K SR1)

    Are you storing numeric keys for the lookup items or their text values? Storing the text values lets you see what has been inserted into the table easily but it is also the slowest since it requires indexing a text value. My advice would be to use lookup tables with numeric keys and insert those keys into your main table. You have to index the primary key in the lookup table. The index on the foreign key is what gives you the speed in finding records based on that field in the main table.
    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
  •