Solved

Relelationship

Posted on 2013-11-14
7
165 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 70

Assisted Solution

by:KCTS
KCTS earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Awesome Guys, This is awesome. Thanks  a million.  I was confused.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

744 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

17 Experts available now in Live!

Get 1:1 Help Now