Results 1 to 10 of 10
  1. #1
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112

    Primary and Secondary Keys when dividing a table

    Hello,


    I have divided a very large table by sectioning off 4 smaller tables; they each have fields relating to a common topic AND the primary key of #1, which is an automatic-numbering type. I kept the pared down #1 with the basic information; it will be used in almost every query.

    At the moment, each of the 4 new tables has their own (auto-numbered) primary key and, as a secondary key, #1's primary key (CoreID). (I copied the original table 4 times and deleted the unwanted fields, so #1s primary key is correct when the tables are matched.)

    These would all be in a one-to-one relationship with each other.

    But, now, I have a couple of questions:
    - I now see that I don't have the secondary keys set up in a way that will allow me to add/delete records and have the rows adjust in the other tables. Can you tell me how I should back up and handle this? Is this a referential integrity issue, or a structural problem?

    - Should every table relate to every other table? or all only to #1?

    Thanks for help and suggestions!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Louise,
    Tell us about WHAT your table represents. That is, in simple English what is the subject of the "larger table" and what did you use to carve it into 4?
    What is the "business" that this database will support?
    Last edited by orange; 02-01-2021 at 12:16 PM. Reason: spelling

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Have to agree with Orange. If all 4 tables are in the same db then I can't imagine what there is to be gained from splitting into four 1 to 1 relationships - aside from more work and frustration. 1 to 1 relationships in the same db are usually considered taboo.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112
    Well . . . it seemed like a good idea at the time. But I would be happy to be advised to drop the idea.

    I came to the idea because I had corruption warnings (due, I think, to the size being 2+GB) and Access was "not responding" when backing up to my external drive.
    Those seemed solved by editing and removing attachments, bringing the size to 650,000 KB.
    I thought that splitting the table might help with that too.

    My guess now is that dividing the tables as I described wouldn't have helped much anyway.

    So, I will happily drop the idea, unless you have more to suggest.
    And I thank you very much for helping me avoid an unnecessary mess.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One huge table with 300,000 records in 12 fields will take up less space than 4 tables with 300,000 records in 3 fields - in the same db. Each object has it's own overhead, and the 2GB limit includes all overhead - which includes space Access will need for some temporary operations. Removing attachments (not sure what editing will help) is the way to go. Linking control images (such as those used on command buttons) is better than embedding them if you can ensure the images are distributed with the application. You can end up with attachments in a system table even if you've removed them from the main table they were in.

    Another thing that contributes to file size is bloat, so periodic compaction is advised, but it's something that should be researched before going down that route. Having multiple objects that do basically the same thing is another. An example of that would be several reports that only differ because of date ranges, applicable departments and so on. Same with forms - one form for editing/adding/viewing records, not 3 because there are 3 ways you want to open one. At some point you can outgrow Access regardless of doing all the right things. Then it's time to think of something like SQL Server to handle the volume of data.

    If you stick with what you're doing, I'd say tables 2, 3 and 4 should only relate to #1 where #1 contains the main entity and the other tables contain the characteristics/attributes. However, you will only complicate queries and might even find that creating new records is a real chore.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112
    Thank you, Micron,
    Your comments were very helpful. I am dropping the idea of multiple tables. You have given me some good suggestions on not near-duplicating forms and reports.
    I appreciate your help!

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. I see it all the time here with posted db samples. I don't look at those objects too much as it would require me to understand and follow a process that I don't want to get into beyond helping someone with their immediate issue. However, it's fairly certain that those objects are just variations of the same thing. Names like
    rpt1stQuarter
    rpt2ndQuater
    Sales for Joe << spaces in names - yuk!
    Sales for Sally

    Queries seem to be the best example of the worst of this issue. There might be 6 pieces of criteria and in order to run a query for only one criteria field, there are 6 versions of the query. If this is the sort of approach you've taken, it's one reason why you file is too big, or at least bigger than it needs to be.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Louise,

    Can you tell us about the business this database is intended to support? It may help you get more focused comments.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would add indexing also takes up space. In principle, one table one index, 4 tables 4 indexes

  10. #10
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112
    Hello Orange,
    I am going to drop this multi-plan, and I think I have in mind some ways to save more space. (At the moment I am not pushing the limit.)
    So, I won't get into the detail of the business...until necessary.
    Thank you for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-19-2018, 06:53 PM
  2. Replies: 14
    Last Post: 10-24-2018, 04:37 AM
  3. Replies: 3
    Last Post: 04-09-2014, 12:48 PM
  4. Replies: 11
    Last Post: 06-11-2012, 12:23 AM
  5. Primary and Secondary Keys...
    By LittleOleMeDesigns in forum Database Design
    Replies: 5
    Last Post: 07-24-2009, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums