Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55

    Need Help with Data Normalization


    tblSyncPairs is the "master table" as of now... I know I need to update them to the Primary Key IDs from the outlying first normalization tables... however, the repeating columns for Passive, Themes, Moves, and Items all need a second normalization set of some sort and I'm not sure what the best way is to structure it. Can somebody take a look at the attachment and give me an quick outline of how to feed it all through each other and connect them with relationships? Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by rawlstonb View Post
    tblSyncPairs is the "master table" as of now... I know I need to update them to the Primary Key IDs from the outlying first normalization tables... however, the repeating columns for Passive, Themes, Moves, and Items all need a second normalization set of some sort and I'm not sure what the best way is to structure it. Can somebody take a look at the attachment and give me an quick outline of how to feed it all through each other and connect them with relationships? Thanks
    @mike60smart is your man
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us in simple, plain English what is/are the business process)es) that you are attempting to automate?

  4. #4
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    right now it's mostly raw data to make a search engine database for a mobile game... will probably expand functionality later, but for now just wanted to get the tables and relationships structured correctly so I don't have to backtrack too far as I start adding queries and forms/etc

  5. #5
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    Watched a few more youtube videos on normalization.... I think I'm starting to get the gist of it... main question now is how necessary is it to use autonumber as a primary key for primary fields? Is it an intrinsically wrong to use text as a primary key if it's indexed with no repeating lines? I get the database size would be smaller if I did, but it makes the tables unreadable without running a query to piece them all together, which is an annoyance especially at this stage of development

    Current plan is to split up Passives/Multiple-line themes/Moves/Items into separate tables using Pokemon from tblSyncPairs as the primary key to normalize them... I could further normalize tblTheme[Types] (I think) by splitting off the redundant explanation data but I'm less clear on how to do that exactly.... the explanation data in each of the theme tables is dependent on whether the given theme is Strike/Tech/Support so it's kind of a many-to-many relationship if I were to split it up further than it is... I know it's doable my brain just isn't wrapping around it yet :P

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    FWIW I would always use an autonumber field for linking, then any other field can change, without issues?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    figured out how to do the second part..... just make a table with possible stats/descriptions for themes and link that with the autonumber to it with foreign keys... pretty simple... man this is gonna suck going through this and looking up all the autonumber keys :'(

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You do not lookup the autonumber fields
    . You define the relationships and Access does the rest?
    Bit like me knowing which drawer in your kitchen holds the knives after you tell me?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    So I currently have tblSyncPairs that needs a tblPokePassives linked table to normalize the data...
    tblSyncPairs is the main table listing most of the things...
    tblPassives lists all the possible Passives with decriptions...

    I'll be deleting the Passive1, Passive2, Passive3, Passive4 columns in tblSyncPairs to do them in rows on tblPokePassives to normalize that data... the data entry for tblPokePassives will have two columns (Pokemon linked to the Autonumber ID in tblSyncPairs and Passive linked to the autonumber field in tblPassives)... I was assuming I needed to enter the data as numbers from the corresponding primary key autonumber fields... which means I'd have to look each one up as they are currently text... is there a good way of pooling all of it and linking without manually looking everything up?

    I'm sure there's some query design here that would save me hours of garbage data entry I just don't know how to structure it

  10. #10
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    I figured it out.... redid the relationship between each cross-table column query and piled it all into an excel spreadsheet to copy/paste into access after it's fully compiled

    I'm sure I'll be back later to ask seemingly stupid questions and figure them out while I'm waiting for a reply :P

  11. #11
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You need to normalise the tables:-
    tblThemeType
    tblThemRegion
    tblThemeFashion
    tblThemeOther
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    What Table name would you give to these values:-

    Bug
    Alola
    Cape
    Adventurer
    ??
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    tblPokeType / tblPokeRegion / tblPokeFashion / tblPokeOther

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    It is these 4 tables, which are identical in structure that need to be sorted.

    tblThemeType
    tblThemRegion
    tblThemeFashion
    tblThemeOther

    Can you explain in a bit more detail what these tables are used for.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    rawlstonb is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    55
    they can all be thrown in a tblThemes I think now that I'm restructuring... I split them up originally so I could define them differently in the search engine to come... if you add a column to define them as Type/Region/Fashion/Other, that would remain intact... they are to be linked to tblSyncPairs and define the variables attributes of the Theme portion of that table.... I'm thinking a junction table to bridge where everything goes using autonumber IDs.... but I came here to get eyes on it that know how to do data properly....

    my thought was a table for possible theme description/stat variants... a theme table to list as primary keys... then link the repetitive data to that.... then a junction table to connect them with the appropriate trainers/pokemon? .... but if you have a more efficient way I'm certainly open to it

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-23-2018, 05:02 PM
  2. Replies: 16
    Last Post: 01-29-2018, 03:12 PM
  3. Organizing my data in table - normalization?
    By adame in forum Tutorials
    Replies: 8
    Last Post: 06-15-2017, 02:57 PM
  4. Normalization for large amount of data
    By kvollene in forum Database Design
    Replies: 8
    Last Post: 07-01-2016, 01:18 PM
  5. Data normalization, ie; duplicate records
    By snowboarder234 in forum Database Design
    Replies: 1
    Last Post: 08-05-2013, 04:50 PM

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