Solved

Relelationship

Posted on 2013-11-14
7
169 Views
Last Modified: 2013-11-14
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
Comment
Question by:Rayne
7 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 39648304
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 39648308
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
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 100 total points
ID: 39648323
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 70

Assisted Solution

by:KCTS
KCTS earned 50 total points
ID: 39648479
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
 
LVL 31

Accepted Solution

by:
Paul Sauvé earned 75 total points
ID: 39648481
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 75 total points
ID: 39648657
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
 

Author Closing Comment

by:Rayne
ID: 39648810
Awesome Guys, This is awesome. Thanks  a million.  I was confused.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now