+ Reply to Thread
Results 1 to 12 of 12

1 to 1 tables vs a large table

  1. #1
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    1 to 1 tables vs a large table

    So I've recently decided to try and make a database. At the moment I have 6 spreadsheets, but all of them are about employees. So when a new employee joins, I have to add them to 6 spreadsheets.

    Problem is, each of these spreadsheets could be fit into 1 because an employees name can only appear once. But they're currently seperated because infomation on 1 table, will have nothing to do with informaton on another table (eg. Home Address on one, training records on another, driving licence details on another etc.)


    I'm currently struggling in the designing phase. Currently what I'm trying to figure, is should I make one table with lots and lots of fields, or should I stick with many tables, and create one-to-one relationships. Then I suppose I can separate the information using queries.


    Thanks for any help. If anyone needs more info then let me know

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: 1 to 1 tables vs a large table

    You use the term spreadsheets so I wonder if this is an excel database or an Access database that you are referring to? If an Access database, then join your tables on a common field, ie. Employee ID. Create a primary form, create a subform for each of the characteristics. Insert the subform onto the main form joined on the common field. Look at this whitel paper on RDBMS development. I think you will find it helpful in designing your db.

    http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: 1 to 1 tables vs a large table

    Sorry when I say spreadsheet, I mean that my data is currently stored on several xls files, but each of them have been formatted as a table. So currently there's no computerised link/connection.

    Are you recommending I use multiple smaller tables then?

    -
    I don't understand what you mean by primary forms and subforms, I haven't gotten that far in my books yet
    I'll start reading the paper you provided now, thank you.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: 1 to 1 tables vs a large table

    Are you moving your data from the excel spreadsheets to Access Tables?

    After you read the white paper, here is a good starting tutorial on Access.

    http://www.accessmvp.com/strive4peace/

    to answer your question, the white paper will help you make that decision based upon your data. The key is to not duplicate any information in multiple tables. Make sure that your data is in normalized fashion.
    Last edited by alansidman; 10-21-2014 at 11:32 AM.

  5. #5
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: 1 to 1 tables vs a large table

    Yes I am moving from excel to access tables. So I will be importing many excel tables.

    I've read through normalisation rules. My excel tables are setup appropriately for access. But before I imported them I wasn't sure whether to combine all my spreadsheets into 1 excel table, then import. Or import individually then link them as one-to-one's.

  6. #6
    Registered User
    Join Date
    03-20-2013
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: 1 to 1 tables vs a large table

    It sounds like you should be storing your data into different tables. You can have a table for employee data, another for training records, additional tables for other purposes.

    1-to-1 table relationships are almost NEVER a good idea. You may want to consider the idea of junction tables instead. Google that term and find out if it would meet your needs.

  7. #7
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: 1 to 1 tables vs a large table

    1-to-1 table are almost never a good idea?? Should I make it one large table then.

    Most of my tables don't require many-to-many, so no need for junction table.

    For example:
    table1 is employees name, address, tel no.
    table2 is employees driver licence details
    table3 is employees health and saftey card details

    An employee cannot have multiple driving licences. And an employee can only have 1 of these health and safety cards. So I'm thinking table2 and table3 have a 1-to-1 realtionship with table1.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: 1 to 1 tables vs a large table

    Since no data is repeated and an employee cannot have many of the items listed, you can easily go with one table.

  9. #9
    Forum Contributor
    Join Date
    06-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    144

    Re: 1 to 1 tables vs a large table

    I've done a lot of googling on this now and I seem to come to the conclusion that there's no right or wrong method.

    If I choose to have 1 large table. Is it possible to (if I re-decide some time in the future), split the data somehow and create seperate tables with 1-to-1

  10. #10
    Registered User
    Join Date
    10-21-2014
    Location
    Derby, England
    MS-Off Ver
    2010
    Posts
    15

    Re: 1 to 1 tables vs a large table

    From the description you've provided in post #7, it sounds like a single table should be suitable. Just be sure to add an 'Autonumber' field as primary key, this will be how you'll identify each employee. Have separate fields for things like FirstName and LastName, and hold your address in separate fields too (Street, Town, City, Postcode etc...). Use a text field for telephone number, not a number field.

    Your project sounds pretty straight forward, so I wouldn't worry too much about needing to split the data in future - if you ever did want to split the table, then sure, it would be simple enough. You'll learn quickly once you begin working in Access.

    Gregg
    Last edited by GregThePeg; 10-23-2014 at 03:16 PM. Reason: Typo

  11. #11
    Registered User
    Join Date
    09-25-2014
    Location
    USA
    MS-Off Ver
    yes
    Posts
    2

    Re: 1 to 1 tables vs a large table

    I've got a database which will store profiles about individuals. These individuals have about 50 possible fields.

    Some are common things like, first name, last name, email, phone number.
    Thanks.
    interior
    www.blindsexpressonline.ca

  12. #12
    Registered User
    Join Date
    10-21-2014
    Location
    Derby, England
    MS-Off Ver
    2010
    Posts
    15

    Re: 1 to 1 tables vs a large table

    Hi matthepet,

    Please have a read of the forum rules (point #2 specifically).

    http://www.excelforum.com/forum-rule...rum-rules.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 07-17-2014, 07:32 AM
  2. [SOLVED] Need to get data from several small tables into one large table
    By Haze01 in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 11-30-2013, 12:02 AM
  3. Replies: 3
    Last Post: 12-06-2012, 10:51 AM
  4. create small tables from a large table
    By ruleworld in forum Excel General
    Replies: 4
    Last Post: 08-01-2010, 04:43 AM
  5. Identifying small tables in one large table
    By Nick_in_Dubai in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-25-2009, 12:01 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1