• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Relelationship

Hello All,

When does it make sense to do a one – one relationship in DB? Can anyone explain in layman’s terms?

Thank You
0
Rayne
Asked:
Rayne
6 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you have entirely different entities that can only belong to another single, unique entity. For example, a Drivers License can belong to a single Person. The "Person" and "DriversLicense" are distinctive entities, and they have different attributes, but they really shouldn't be listed in the same table.
0
 
Rey Obrero (Capricorn1)Commented:
an example is an employee table..
you can divide the information into two parts
one part is the common information, like names, location, tel (non sensitive Info)
the other part contains the sensitive information that will only be available to certain people

you then join the two table using the employeeID
0
 
Roger BaklundCommented:
Use it for optional data, i.e. when some column(s) are rarely used.

For example, if you have a person table with many persons, and for a few persons you want to store some notes and a pic:

Person:
  person_id INT
  name VARCHAR
  addr VARCHAR
  zip INT
  city VARCHAR

Person_notes:
  person_id INT
  note TEXT

Person_pic:
  person_id INT
  pic BLOB

Putting the pic and note columns in the main table works too, but if the person table is large, it will be a waste of disk space (even empty columns consume some space).
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
KCTSCommented:
Relationships are used to 'link' using a field that's common to two different sets.

Suppose you have a list of employees
EmployeeID
EmployeeSurname
EmployeeFirstName
EmployeeDOB
SiteWorkedAt

and list that records the company sites
SiteID
SiteAddress
SitePhoneNumber

You would create a relationship between the two tables linking SiteID to SiteWorkedAt

This means that you only record the site details ONCE no matter how many people work there - this makes the database more efficient and smaller -  and if something changes - like the SitePhoneNumber, you can change it in ONE place.
0
 
Paul SauvéRetiredCommented:
To further illustrate LSMConsulting's comment:
>>If you have entirely different entities that can only belong to another single, unique entity. For example, a Drivers License can belong to a single Person. The "Person" and "DriversLicense" are distinctive entities, and they have different attributes, but they really shouldn't be listed in the same table.

This Person can have 1 drivers permit and three vehicles. He needs 1 drivers permit and 1 registration for each vehicle.

If the person changes address and there was no 'Person' table, then the address would have to be changed in FOUR records, thus increasing the possibility of error. By separating the "Person" information, you only update ONCE!

Also, you will have a 1:1 relationship between the "Person" and "DriversLicense" entities and a 1:n relationship between the "Person" and "VehicleRegistration" entities... This process is called NORMALIZATION of a database!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In my opinion ... never.
At a minimum, it complicates most other aspects of a design. Queries are more complicated, Record Sources are more complicated and so on.
0
 
RayneAuthor Commented:
Awesome Guys, This is awesome. Thanks  a million.  I was confused.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now