Results 1 to 13 of 13
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48

    Why do we need 1 to 1 relationships? Is there an advantage to do that?

    Hi everyone,

    it seems like an easy question but I thought that can be challenging. Instead of 1 to 1 relationships between two tables, we can always create one unqiue table that includes all fields in itself. From this pespective, then we will never need to create 1 to 1 relations. What do you think that why do we need 1 to 1 relations?

    Thanks for the opinions

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The only time they seem vaguely useful is to deal with Memo or Long Text fields, by moving them into a separate table.
    It effectively splits the normal sized fields from the Memo one and stops errors with truncation in aggregate queries.

    I have also seen one used sensibly to "hide" sensitive data, so that the related table wasn't joined except in coded queries to get the related data.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Its for saving space and resources. There are times when you need a separate table to add fields that further describe a particular row of data, if you only need it for some records and not others it wouldn't make sense to store a bunch of null value fields.

    If you're familiar with object oriented programing I like to think of it like inheritance.

    Here's an example from the FDA's food database. They have a primary [food] table that lists every food in the db, then a secondary [branded_food] table that further describes packaged foods. There's 553k foods in the [food] table, but only 498k records in the [branded_food table]. If these tables were merged there would be 12*(553k-498k)= 660k null values or wasted overhead in the db.

    Click image for larger version. 

Name:	Untitled.png 
Views:	54 
Size:	9.5 KB 
ID:	44559

    It may not waste much actual disk space depending on the db engine and field types.

  4. #4
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Thank you Minty and kd2017. They were very good clarifications I get it.

    kd2017, in your example 1 to 1 relations is linked to each other from their PKs.
    Which one is right one to link? like in your Food example? or to create a unique integer FK in one table and then link it to the PK of other table?

    Because when they are linked to PK from PK, Lets talk over your example, I enter a branded food value for a food whose PK is 458. and if it is the first entry in Branded food table, then its PK is getting value "1". And then it matches the value in food table whose PK is 1 as well when you refresh the query. However in the beginning I entered it for the food with 458. id.

    It is how it worked in my case. Am I doing something wrong here?

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    [food].[fdc_id] would be your autonumber primary key. [branded_food].[fdc_id] is still set as primary key but is a long integer instead of an autonumber.

    When creating the relationship in access in the relationship window you would drag FROM the [food] table to the [branded_food] table. This order tells access that [food] is the "independent" table and [branded_food] is the "dependent" table.

    If you did it right you can add a record to the [food] table and nothing will be added to the [branded_food] table and no errors. If you add a record to the [branded_food] table it will require a record with matching ID in the [food] table to maintain referential integrity. If you build an updateable query that joins these two tables and use that for data entry then as you create a new record in the [branded_food] fields access will automatically populate the [food] table with a new record and grab an autonumber pk. I don't know much more of the nuances of how access handles 1-to-1 and there's a decent chance I've made mistakes in my explanation =)

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    For kd207's example, I'd advice a design, where you have a form, where user can register/edit all foods, and a subform, where user can enter brand info when this exists. I.e. user can't enter any brand info without registering a food article beforehand. A main reason behind this - you always must have open the possibility to port your database to different media in future, and it may not support editing different tables from same query.

    E.g. There is an unbound main form, with continuous subform for foods registering. On main form is an unbound hidden textbox, which is populated with fdc_id from current record in foods form by Current event of foods form. At right of foods form (or on another tab of Tab control in main form) is a single subform for registering brand info. Brand info subform is linked to unbound text box in main form (like Link Master Fields = "txtUnboundFdcId", Link Child Fields = "fdc_id"). You also have to make impossible to register more than 1 row of brand info per food article.

  7. #7
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by ArviLaanemets View Post
    For kd207's example, I'd advice a design, where you have a form, where user can register/edit all foods, and a subform, where user can enter brand info when this exists. I.e. user can't enter any brand info without registering a food article beforehand. A main reason behind this - you always must have open the possibility to port your database to different media in future, and it may not support editing different tables from same query.

    E.g. There is an unbound main form, with continuous subform for foods registering. On main form is an unbound hidden textbox, which is populated with fdc_id from current record in foods form by Current event of foods form. At right of foods form (or on another tab of Tab control in main form) is a single subform for registering brand info. Brand info subform is linked to unbound text box in main form (like Link Master Fields = "txtUnboundFdcId", Link Child Fields = "fdc_id"). You also have to make impossible to register more than 1 row of brand info per food article.

    Hello ArviLaanemets, Thanks for your comment.

    I have one question mark in my mind. When you make this form design like main form and sub form as you described, Then will not users see only one record in front of themselves?. And for each record to see and edit, they will need to push next record button. That is the problem as many users want to see this kind of 1to1 relations in a table design form like an Excel sheet in order to view all records at a glance.

    How can we enable this to users while we obey the database logic for 1 to1 ?

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    The form with general food info will be continuous one, i.e. user sees info about several different food articles (of course you can have it as single form too, when you want this). Brand info is displayed in single form, and contains info about active food article in general food form. I.e. whenever user activates another row in general food form, the brand info form displays brand info for selected article (and allows the user update it), or is empty. Whenever any brand info is entered into currently empty brand info form, when another row is selected in general form, an new entry linked to general foods table is created in brand info table automatically.

    Of-course it is possible e.g. to have a tab in main form, where on one page is displayed a continuous form with general food info, and on another also continuous form with brand info, but there is no way to link such forms together. You need a lot coding, to keep both of form synchronized, and even more of code to link brand info to specific food article whenever it is registered first time (probably some button in brand form is needed for this, when you are going with this approach). Too much hassle I think

  9. #9
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by ArviLaanemets View Post
    The form with general food info will be continuous one, i.e. user sees info about several different food articles (of course you can have it as single form too, when you want this). Brand info is displayed in single form, and contains info about active food article in general food form. I.e. whenever user activates another row in general food form, the brand info form displays brand info for selected article (and allows the user update it), or is empty. Whenever any brand info is entered into currently empty brand info form, when another row is selected in general form, an new entry linked to general foods table is created in brand info table automatically.

    Of-course it is possible e.g. to have a tab in main form, where on one page is displayed a continuous form with general food info, and on another also continuous form with brand info, but there is no way to link such forms together. You need a lot coding, to keep both of form synchronized, and even more of code to link brand info to specific food article whenever it is registered first time (probably some button in brand form is needed for this, when you are going with this approach). Too much hassle I think

    Hi ArviLaanemets, I have designed some forms when it is come to 1 to 1 relations But I wonder how you are designing it for your users.
    I prepared a small access with two tables (vehicles and plan table).
    One Vehicle can have one plan data and one plan can belongs to only one vehicle. So they have 1-1 relations.
    Could you prepare some example forms include all fields of both vehicle and plan table? it will be better for us to make it tangible what you are mentioning about.

    Many thanks for help.

    Access can be downloaded from here.
    https://www.dosya.tc/server33/4yt68f...ion.accdb.html

    I could not find if we are able to upload it to directly to Forum.

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

    See the attached
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by mike60smart View Post
    Hi

    See the attached

    Hello mike06smart,

    Thanks for the example

  12. #12
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    24
    I've seen 1:1 used when the design calls for too many fields (more than 255) for a single table. One table held the "job" information, and the other table held the "schedule" information which was 170+ columns of milestone dates (not a good design, but that's what the original dev did).

  13. #13
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by DTO. View Post
    Hello mike06smart,

    Thanks for the example


    Normally users are not satisfied with kind of subform and mainform design when it comes to 1-1 relations. Because they need to go forward and back for each distinct record to see which they dont like.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-21-2020, 04:59 PM
  2. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  3. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  4. advantage of foreign key in query
    By learning_graccess in forum Queries
    Replies: 1
    Last Post: 04-20-2012, 03:00 AM
  5. Access / SQl Server Advantage.
    By caljohn527 in forum Queries
    Replies: 0
    Last Post: 01-24-2009, 06:40 PM

Tags for this Thread

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