Relationship reference: Relationship to Candidate: What Does It Mean

Опубликовано: August 13, 2023 в 9:56 am

Автор: alexxlab

Категории: Miscellaneous

Relationship to Candidate: What Does It Mean

If you’re applying for jobs, there’s a good chance that you’ve come across the term “relationship to candidate.” Typically, the term will be used on a job application in regards to job references.

This post will define what “relationship to candidate” means, talk about different types of reference relationships, and answer frequently asked questions around the term.

What Does Relationship to Candidate Mean on an Application?

Relationship to candidate means that the company to which you’re applying wants you to indicate how you know the person that you are providing as a reference.

From the company’s perspective, they want to understand the credibility of the person to speak to your qualifications for the job.

For example, a company will be much more interested in talking to a reference that was a colleague at a previous job that was similar to the one to which you’re applying than someone who is a personal friend.

Note that the term “relationship to candidate” is similar to “in what capacity do you know the applicant” so know that the two more or less mean the same thing if you see a company asking for either one.

Reference Relationship Types

In general, you want to maintain about three to five relevant references at any given time. Now, the term “relevant” is key in this case, as the company will want only references that can add value in their ability to vet you as a candidate.

Here are common types of references that can be provided for a job:

A boss from a previous employer: In general, previous bosses are going to be your most relevant references. A manager will have directly evaluated the quality of your work and will know you well. They’re uniquely equipped to speak to your experience for a role.
A direct report from a previous employer: If you’re applying for a management role, then having a previous direct report as a reference will be helpful. They can speak to your management practices and what it’s like to work for you.
A co-worker from a previous employer: Outside of people you worked for or that worked for you, a colleague is going to be your next best reference. Ideally, it’s someone that benefited from your work or that you worked closely with towards the same goal.
A teacher or mentor: Having a teacher or mentor as your reference is going to be most common for people that are in school or coming out of school. However, that doesn’t always need to be the case. Having a mentor who is respected and successful can be a great reference.
A person that benefitted from your volunteer work: If you’ve done previous volunteer work that is relevant to the role that you’re applying for, listing someone that benefitted from that work, or that you volunteered with, can be a fantastic reference.

In general, it’s best to avoid providing personal reference like friends or family members, as a company will be skeptical of their credibility.

You’ll also want to carefully vet your references, as there is certainly risk that a bad reference could cost you a job.

Lastly, this video from the UC Davis Internship and Career Center provides some additional tips and recommendations on the types of people that serve as good references:

We also have an article that dives into good people to provide as references when applying for a job if you’re looking to learn more.

What Do I Put For Relationship to Candidate?

When indicating the nature of your relationship with someone that you’re providing as a reference, you simply list as succinctly as possible how the reference knows you.

As an example, if someone was a former boss, you could list “Manager at XYZ Company.”

Relationship to Candidate Examples

Circling back to different potential types of references that were provided in the “reference relationships types” section, here would be some examples of what you could put in a “relationship to candidate” section:

Boss from previous employer -> “Manager at XYZ Company”
Direct report from previous employer -> “Direct report at XYZ Company”
Co-worker from previous employer -> “Colleague at XYZ Company”
Teacher or mentor -> “Professor at XYZ University”
A person that benefitted from volunteer work -> “Primary Liaison at XYZ Non-profit”

Frequently Asked Questions

How Do I List a Friend as Relationship to Candidate?

I do not recommend listing a friend as a job reference. Ultimately, you want to list relevant references that can speak to your professional credentials for a role. A company is likely to question the credibility of a friend to speak in an unbiased way about your ability to do a job.

We have a complete article on the topic of should you list as a friend as a reference if you’re looking to learn more.

How Would You Describe a Working Relationship for References?

The manner in which you describe a worker relationship is going to vary slightly based upon the nature of the relationship (i.e., a boss vs. a co-worker). However, assuming that you’re describing a co-worker, these would be variations of how you could list a working relationship with a co-worker on a job application:

Colleague
Co-worker
Work associate

Conclusion

It’s important to pick the right type of person to be your job reference. Ideally, you want a relevant reference that can speak to your professional qualifications for a job. But if you select the right references, and the company calls them during the vetting process, a job offer could be on the way soon.

8 Types of Professional References to Help You Get Hired

By: Erica Woods |
Comments | Related: More > Professional Development Job Search

Problem

Over the years, weve heard countless challenges and concerns when it comes to
providing references during the job search process. One of the major struggles
around the reference process is the first rebuttal/challenge below, i.e. I feel
bad mentality.

“I don’t want to bother people by asking them to be a reference.”

“I haven’t talked to my last Manager in a year. I don’t feel comfortable
reaching out to him/her to be a reference.”

“I’ve been at the same company for 10 years and they don’t know
I’m considering other opportunities, so I don’t have any references
to provide.”

“I want to transition into a different type of role, so I don’t have
any relevant references to provide. ”

“I’m not sure who to exactly to put as a reference.”

Another major challenge is not staying in contact with your best and most relevant
career references and then leveraging them accordingly during your job search.

Solution

Identifying quality references and then making “the ask” of a potential
reference causes many job seekers anxiety. A response or mentality of “I
feel bad asking” or “they’re so busy, I don’t want to bother
them” is one we hear more often than either of us ever expected. What
you need to tell yourself is simply, “Don’t feel bad! I did good
work for them, and they’d want to help me by taking 5 minutes to act as a
reference.” So, first adjust that “I’m nervous/scared/feel
bad to ask” mentality.

Second, think through who could be a strong reference for the positions you’re
now pursuing. With that in mind, here are the 8 key groups or types of people
that could be a potential reference.

People you have trained, mentored, and/or managed

This is one of the most overlooked types of references. Think about the
great people you have work for or been mentored by. Would you mind if they
asked you for a reference? Likely not, and in most cases, you would be more
than happy to! Having an inspiring, trusted boss / mentor / trainer can be
hard to come by, and we know we always want to show appreciation for those that
have made a difference in our professional lives. It is great to provide a
reference of someone you have overseen in a leadership capacity, especially if you
are pursuing/interviewing for a leadership role.

Past/Current Manager

You always want to try and provide at least one reference who has directly managed
you. What do you do if you have not spoken to them in a while? Do not
fret! Do not shy away from reaching back out to them when you start a new
job search. We have had employees we managed years ago reach out to us for
professional references and we have never turned them down! Keep in mind that
even though some time has lapsed, if you made valuable contributions and had a great
working relationship with them, they will likely remember and be happy to assist
you in your job search.

BEST PRACTICE – If you get in the habit of asking for a LinkedIn recommendation
in real-time, you will always have access to those and can provide them on the spot!

Director or another IT Executive

Have you worked closely with a higher up on a group/company initiative, steering
committee, etc.? Ask them if they would write you a brief recommendation,
which will likely highlight the impact you made to the group/company as a whole
through this effort!

Peer

Individuals you have worked alongside might be the best references when speaking
to qualities like work ethic, problem-solving, teamwork/team-oriented, communication,
being helpful/supportive, and other positive soft skill traits that any future Manager
would admire! Think through the individuals you have worked the most closely with
and ask 2+ if they would be a reference and/or write you a LinkedIn recommendation.

Mentor

Who has helped shape and develop you in your career? Often times, you have
mentors outside of your direct Manager(s). If you have worked with them closely
and they either have seen examples of your work or could speak to soft traits/leadership
qualities, solicit them as a reference.

End Client/Customer

You may often be building, enhancing, fixing, etc. projects/applications or other
solutions to help another group/team in your organization or for an end client.
Ask yourself “who has benefitted from my work/efforts?” and then ask
them if they would act as reference and/or write you a brief LinkedIn recommendation.
If it is an external vendor/end client, do your due diligence to ensure you are
able to make that request (i.e. it is against company policy or is not crossing
a line).

Professors

Are you an entry-level professional and/or a recent graduate? Professors
are a powerful reference, especially if you completed large projects or held an
internship. Target the professors of the courses you took where you’re
seeking jobs in that discipline (i.e. any Professors or Internship Mentors of Database,
Programming or Data Science courses if you’re pursuing that path).

Community Contact

Are you involved in a local tech MeetUp/User Group? Have you contributed
to the group at all (i.e. given a presentation, volunteered at an event, acted on
the committee, etc.)? Have you gone to, and contributed, to a SQL Saturday,
Code Camp, local Hackathon/Codeathon, or another local/regional tech conference or
Tech5Good event? Have you helped a nonprofit with a technology issue/project
or general advice/consultation? If you answered yes to any of these questions,
ask the leader of the user group, an individual that sat through your presentation,
or the nonprofit you helped solve an issue for, to write you a LinkedIn recommendation
and/or be a future reference.

Examples of Quality Community References

One of our candidates does the systems administration and technical support
for her child’s elementary school. Once we identified this, we encouraged
her to add that pro bono work to a ‘Community Involvement & Volunteer
Experience’ section of her resume and to get a LinkedIn recommendation
from an Assistant Principal.
A Lead Database Developer presents at one SQL Server User Group meeting
a year and has done this for the last 4-5 years. We encouraged him to
add that to his resume, upload a relevant presentation to his LinkedIn profile,
and get a LinkedIn recommendation from the User Group Organizer.
One of our Developers has participated in his local city Hackathon for the
last 4 years, so we suggested he add that to his resume with the projects/applications
he worked on and the technologies he worked with.

Next Steps

Requesting references/recommendations may seem daunting at first but always remember
that the majority of people out there are more than willing to assist! Never
shy away from simply asking “would you act as a professional reference?”
During your next job search, simply review the 8 types of references we’ve
outlined above, and jot down a list of relevant individuals you could solicit for
a recommendation or ask to be a reference.
Remember, focus on building a reference
list that’d be relevant (i.e. best speak to) the types of positions you’re
now pursuing!

About the author

Erica Woods has nearly a decade in the IT staffing world, an MBA, and is a member of the Professional Association of Resume Writers and Career Coaches.

View all my tips

why it is better to use links rather than keys to represent relationships in API / Sudo Null IT News

Hello, Habr!

We have a long-awaited second edition of Web Development with Node and Express.

While researching this topic, we found a concept article about designing a web API by model, where references to resources are used instead of database keys and values. Original – from the Google Cloud blog, welcome under cat.

When we are modeling information, the key question is how to define the relation and relationship between two entities. Describing patterns observed in the real world in terms of entities and their relationships is a fundamental idea dating back at least to Ancient Greece. It plays a fundamental role in the modern IT industry.

For example, in relational database technology, relationships are described using a foreign key, a value stored in one row of a table that points to a different row, either in another table or in the same table.

It is equally important to express relationships in the API. For example, in a retail API, information entities might correspond to customers, orders, catalog entries, shopping carts, and so on. The Bank Account API describes which customer a given account belongs to and which account each debt or loan is associated with.

The most common way API developers use to express relationships is by providing database keys or proxies for them in the fields of the entities associated with those keys. However, in at least one API class (web based) there is a much better alternative to this approach: using web links.

According to the Internet Engineering Task Force (IETF) standard, a web link can be thought of as a tool for describing relationships between pages on the web. The best-known web links are those that appear on HTML pages and are enclosed in link or anchor elements, or in HTTP headers. But links can also appear in API resources, and using them instead of foreign keys greatly reduces the amount of information that the API provider has to document and the user has to learn.

A link is an element in one web resource that points to another web resource, as well as the name of the relationship between the two resources. A reference to another entity is written in a special format called a “Unique Resource Identifier” (URI), for which there is an IETF standard. In this standard, the word “resource” refers to any entity pointed to by the URI. The name of the relation type in the link can be considered the same as the name of the database column that contains the foreign keys, and the URI in the link is the same as the value of the foreign key. The most useful of all URIs are those that provide information about the linked resource using the standard web protocol. These URIs are called Uniform Resource Locators (URLs), and the most important kind of URL for an API is HTTP URLs.

While links are not widely used in APIs, some very well-known web APIs do rely on HTTP URLs as a means of representing relationships. These are, for example, the Google Drive API and the GitHub API. Why is it like this? In this article, I’ll show you how API foreign keys work in practice, explain their disadvantages compared to using links, and show you how to convert a design that uses foreign keys to one that uses links.

Representing relationships using foreign keys

Consider the popular pet store educational app. This application keeps records to keep track of information about pets and their owners. Pets have attributes, in particular, a nickname, type and breed. The owners have names and addresses. Each pet is related to its owner, and the reverse relationship allows you to find all the pets of a particular owner.

In a typical key-based design, the pet shop API exposes two resources that look like this:

The relationship between Lassie and Joe is expressed as follows: in Lassie’s view, Joe is identified as having the name and meaning corresponding to “owner”. The reverse relationship is not expressed. The value of “owner” is “98765”, this is a foreign key. This is probably the real foreign key of the database – that is, we have the value of the primary key from some row in some database table. But, even if the implementation of the API slightly changes the values of the keys, it still approaches the main characteristics of the foreign key.
The value “98765” is not very suitable for direct use by the client. In the most common cases, the client needs to use this value to compose a URL, and the API documentation needs to describe the formula for performing such a transformation. This is typically done by defining a URI pattern, like so:

/people/{person_id}

– only stylistic, not substantive):

/pets?owner={person_id} /people/{person_id}/pets

APIs designed this way typically require many URI patterns to be defined and documented. The most popular language for defining such patterns is not the one specified in the IETF specification, but the OpenAPI language (formerly known as Swagger). Prior to version 3.0, there was no way in OpenAPI to specify which field values could be inserted into which templates, so some of the documentation had to be in natural language, and some had to be guessed by the client. Version 3.0 of OpenAPI introduced a new syntax called “links” to address this issue, but it takes some work to use this feature consistently.

So, while this style is common, it requires the provider to document and the client to learn and use a significant number of URI patterns that are not well described in current API specifications. Fortunately, there is a better option.

Representing relationships using links

What if the resources shown above were modified like this:

The main difference is that in this case the values are expressed using references rather than using foreign key values. Here the links are written in regular JSON, in the format of name/value pairs (there is a section below that discusses other approaches to writing links in JSON).

Note that the reverse relationship, i.e., from pet to owner, is now also implemented explicitly, since the field "pets" has been added to the view Joel .

Changing "id" to "self" is not really necessary or important, but there is a convention that "self" identifies a resource whose attributes and relationships are specified by other name/value pairs in the same JSON object. "self" is the name registered with the IANA for this purpose.

From an implementation point of view, replacing all database keys with links should be quite simple – the server converts all database foreign keys into URLs, so that nothing needs to be done on the client – but the API itself is greatly simplified in this case, and the coupling between client and server decreases. Many URI patterns that were important in the first design are no longer required and may be removed from the API specification and documentation.

Now the server is free to change the format of new URLs at any time without affecting the clients (of course, the server must continue to respect all URLs formulated earlier). The URL passed to the client by the server will need to include the primary key of the entity specified in the database, plus some routing information. But, because the client simply repeats the URL in response to the server, and the client never has to parse the URL, clients don’t need to know how to format the URL. As a result, the degree of coupling between the client and the server is reduced. The server may even resort to obfuscating its own URLs with base64 or similar encodings if it wants to emphasize to clients that they should not “guess” what the format of the URL is or infer the meaning of the URL from their format.

In the previous example, I used the relative form of the URI in the links, such as /people/98765 . It might be a little more convenient for the client (although the author didn’t find it very handy when formatting this post) if I expressed the URI in absolute form, e. g. pets.org/people/98765. Clients only need to know the standard URI rules defined in the IETF specifications in order to convert such URIs from one form to another, so the choice of a specific URI form is not as important as it might seem at first glance. Contrast this situation with the conversion from a foreign key to a URL described above, which required specific knowledge of the pet shop API. Relative URLs are somewhat more convenient for server implementers, as discussed below, but absolute URLs are probably more convenient for most clients. This may be why the Google Drive and GitHub APIs use absolute URLs.

In short, using links rather than foreign keys to express relationships between APIs reduces the amount of information a client needs to know in order to work with the API, and also reduces the number of connectivity options that can occur between clients and servers.

Pitfalls

Here are some things to consider before moving on to using links.

Many API implementations have reverse proxies installed for security, load balancing, etc. Some proxies like to rewrite URLs. When the API uses foreign keys to represent relationships, the only URL that needs to be rewritten in the proxy is the main URL of the request. In HTTP, this URL is split between the address bar (the first header line) and the host header.

An API that uses links to express relationships will have other URLs in the headers and body of both the request and response, and these URLs will also need to be rewritten. There are several different ways to handle this:

Don’t rewrite the URL in a proxy. I try to avoid URL rewriting, but this may not be possible in your environment.
In proxies, carefully find and reassign the format to them wherever they appear in the request or response. I’ve never done this as it seems complicated, error-prone and inefficient to me, but someone might be doing it.
Record all references in relative terms. Not only can all proxies have some URL rewriting capabilities built into them; moreover, relative URLs can make it easier to use the same code in testing and in production, since the code does not have to be configured and know its hostname for this. If you write links using relative URLs, that is, with a single leading slash, as I showed in the example above, then there are some disadvantages for both the server and the client. But in this case, the proxy only has the opportunity to change the host name (more precisely, those parts of the URL that are called “scheme” and “source”), but not the path. Depending on how your URLs are built, you can implement some path rewriting capability in your proxy if you’re willing to write links using relative URLs without leading slashes, but I’ve never done this because I think it would be difficult for servers to write URLs like follows.

Relative URLs without leading slashes are also more difficult for clients to use, as they must work with a standardized library rather than simply string concatenation to process those URLs, and carefully understand and store the base URL.

Using a standardized library to handle URLs is good practice for clients anyway, but many clients don’t.
When using references, you may also need to double-check your API versioning. In many APIs, it is customary to put version numbers in the URL, like this:0003

/v1/pets/12345 /v2/pets/12345 /v1/people/98765 /v2/people/98765

This is the kind of versioning where the data for a given resource can be viewed in more than one “format” at the same time – not versions that replace each other over time as they are edited.

This situation is very similar to being able to view the same document in multiple natural languages, for which there is a web standard; what a pity that there is no such standard for versions. By giving each version its own URL, you’re elevating each version to a full web resource. There is nothing wrong with “versioned URLs” of this kind, but they are not suitable for expressing links. If the client requests Lassie in version 2 format, it doesn’t mean that it also wants information about Lassie’s owner Joe in format 2, so the server can’t choose which version number to refer to.

Possibly, format 2 for the description of the owners will not even be provided. It also doesn’t make conceptual sense to use a specific version of the URL in the links, because Lassie doesn’t belong to a specific version of Joe, but to Joe as such. Therefore, even if you provide a URL in the format /v1/people/98765 and thus identify a particular version of Joe, you must also provide the URL /people/98765 to identify Joe himself, and it is the second option that you use in links. Another option is to define only the URL /people/98765 and allow clients to select a specific version by including a request header to do so. There is no standard for this header, but calling it Accept-Version goes well with the standard header naming. Personally, I prefer to use the header for versioning and avoid putting version numbers in the URL. but URLs with version numbers are popular and I often implement the title as well. and “versioned URLs”, since it’s easier to implement both options than to argue which is better. You can read more about API versioning in this article.

You may still need to document some URL patterns

In most web APIs, the URL of a new resource is allocated by the server when a new resource is created using the POST method. If you use this method to create resources and specify relationships using links, then you do not need to publish a template for the URIs of those resources. However, some APIs allow the client to control the URL of the new resource. By allowing clients to control the URL of new resources, we greatly simplify many of the API scripting patterns for front-end developers, and we also support scenarios where the API is used to synchronize an information model with an external information source. HTTP has a special method for this purpose: PUT. PUT means “create a resource at this URL if it doesn’t already exist, and if it does, update it.” If your API allows clients to create new entities using the PUT method, then you should document the rules for constructing new URLs, perhaps by including a URI pattern in the API specification to do so. You can also give clients partial control over the URL by including a primary key-like value in the POST body or headers. In such a case, no URI pattern is required for POST per se, but the client still needs to learn the URI pattern to take full advantage of the resulting URI predictability.

Another context in which it is useful to document URL patterns is when an API allows clients to encode requests into URLs. Not every API allows you to query your own resources, but such a possibility can be very useful for clients, and it is natural to allow clients to encode requests into URLs and retrieve results using the GET method. The following example shows why.

In the example above, we included the following name/value pair in Joe’s representation:

"pets": "/pets?owner=/people/98765"

The client does not need to know anything about its structure in order to use this URL, other than that it was written according to standard specifications. Thus, the client can get a list of Joe’s pets from this link without learning any query language for this. There is also no need to document its URL formats in the API – but only if the client first makes a GET request to /people/98765 . If, in addition, the ability to make requests is documented in the pet store API, then the client can compose the same or equivalent request URL to retrieve the pets of the owner of interest without first retrieving the owner himself – it will be enough to know the owner’s URI. Perhaps even more important, the client can also form requests like the following, which would otherwise be impossible:0003

/pets?owner=/people/98765&species=Dog /pets?species=Dog&breed=Collie

The URI specification describes a part of the HTTP URL called the “request component” for this purpose – this is the portion of the URL after the first “?” and up to the first “#”. The URI request style I prefer to use is to always put client-specific requests in the request component of the URI. But at the same time, it is permissible to express client requests in the part of the URL that is called the “path”. One way or another, you need to describe to clients how these URLs are constructed – you are actually designing and documenting a query language specific to your API. Of course, you can also allow clients to query in the body of the message rather than in the URL, and use the POST method rather than the GET method. Since there is a practical limit on the size of a URL – exceeding 4k bytes is always a test of fate – it is recommended to support POST for requests, even if you already support GET.

Because queries are such a useful feature in APIs, and because query languages are not easy to design and implement, technologies such as GraphQL have emerged. I’ve never used GraphQL so I can’t recommend it, but you might want to consider it as an alternative for querying your API. API querying tools, including GraphQL, are best used as a complement to the standard HTTP API for reading and writing resources, not as an alternative to HTTP.

And by the way… What’s the best way to write links in JSON?

JSON, unlike HTML, does not have a built-in mechanism for expressing links. Many people have their own understanding of how links should be expressed in JSON, and some of these opinions have been published in more or less official documents, but at present there are no standards ratified by authoritative organizations that regulate this. In the example above, I expressed links using regular name/value pairs written in JSON – I prefer this style and, by the way, the same style is used in Google Drive and GitHub. Another style that you are likely to come across is this:

 {"self": "/pets/12345",
 "name": "Lassie",
 "links": [
   {"rel": "owner" ,
    "href": "/people/98765"
   }
 ]
}

Personally, I do not see why this style is good, but some of its variants are quite popular.

There is another style of writing JSON references that I like, and it looks like this:

 {"self": "/pets/12345",
 "name": "Lassie",
 "owner": {"self": "/people/98765"}
}

The benefit of this style is that it explicitly gives: "/people/98765" is a URL, not just a string. I learned this pattern from RDF/JSON. One reason to learn this pattern is that you will need to use it anyway, whenever you want to display information about one resource nested in another resource, as shown in the following example. If you use this pattern throughout, the code becomes nicely consistent:

 {"self": "/pets?owner=/people/98765",
 "type": "Collection",
  "contents": [
   {"self": "/pets/12345",
    "name": "Lassie",
    "owner": {"self": "/people/98765"}
   }
 ]
}

For more information on how best to use JSON to represent data, see Terrifically Simple JSON.

Finally, what is the difference between an attribute and a relationship?

I think most readers will agree that JSON does not have a built-in mechanism for expressing references, but there is a way of treating JSON that allows you to claim the opposite. Consider the following JSON:

 {"self": "/people/98765",
 "shoeSize": 10
}

The convention is that shoeSize is an attribute, not a relationship, and 10 is a value, not an entity. True, it is no less logical to say that the string ’10” is in fact a reference, written in a special notation designed to refer to numbers, up to the 11th integer, which itself is an entity. If the 11th integer is a perfectly valid entity, and the string '10' only points to it, then the name/value pair '"shoeSize": 10' is conceptually a link, although no URIs are used here.

The same can be said about booleans and strings, so all name/value pairs in JSON can be treated as references. If this perception of JSON seems justified to you, then it is natural to use simple name/value pairs in JSON as references to those entities that can also be pointed to using a URL.

More generally, this argument is stated as “there is no fundamental difference between attributes and relationships.” Attributes are simply relationships between an entity or another abstract or concrete entity, such as a number or color. But historically, their processing was treated in a special way. Frankly, this is a rather abstract version of the perception of the world. So, if you show someone a black cat and ask how many objects there are, most people will tell you that there is only one. Few will say that they see two objects – a cat and its black color – and the relationship between them.

Links are simply better

Web APIs that pass database keys rather than just references are harder to learn and harder to use for clients. Also, APIs of the first kind bind the client and the server more closely, requiring more detailed information as a “common denominator”, and all this information needs to be documented and read. The only advantage of APIs of the first kind is that they are so common, programmers get used to them, they know how to create them and how to consume them. If you’re looking to provide clients with high-quality APIs that don’t require tons of documentation and are as client-server independent as possible, then consider exposing references rather than database keys in your web APIs.

Table Relations Guide – Microsoft Support

One of the goals of good database design is to eliminate redundancy (repetition) of data. To do this, you need to distribute the data into several separate thematically organized tables so that each fact is presented once. Access will provide a way to collect scattered data – this is done by putting common fields in related tables. To correctly perform this action, you need to understand the relationships between tables and describe these relationships in the database.

Introduction

After you create a table for each topic in your database, you need to provide Access with the means to combine information as needed. This is done by creating common fields in related tables and setting relationships between tables. You can then create queries, forms, and reports that display information from multiple tables at the same time. For example, the following form contains information from several tables:

1. This form contains data from the customer table,

2. orders,

3. goods

4. and order details.

Customer name in field Payer is obtained from the “Customers” table, the order code and order date values are from the “Orders” table, the product name is from the “Products” table, and the price and quantity are from the “Ordered” table. To be able to submit data to the form, these tables are related to each other in several ways.

In the example shown, the fields in the tables must be matched to display information about the same order. This reconciliation is carried out by establishing relationships between tables. A table relationship establishes relationships between values in key fields—often between fields that have the same name in both tables. In most cases, the primary key of a single table, which is the unique identifier for each record, is associated with foreign key another table. For example, to associate employees with the orders they are responsible for, you can create a relationship between the Employee ID fields in the Employees and Orders tables.

1. The “Employee Code” field is displayed in two tables: as a primary key…

2. and as a foreign key.

Top of page

Types of relationships between tables

There are three types of relationships between tables in Access.

One-to-Many Relationship

Consider a database that records orders, including the “Customers” and “Orders” tables as an example. The customer can place any number of orders. Therefore, any customer represented in the “Customers” table can have many orders represented in the “Orders” table. Therefore, the relationship between the Customers and Orders tables is a one-to-many relationship.

To create a one-to-many relationship in the database structure, add the primary key on the “one” side to the table on the “many” side as an additional field or fields. In this example, you need to add a new field – the “Code” field from the “Customers” table – to the “Orders” table and name it “Customer Code”. Access can then use the Customer ID number from the Orders table to find the customer for each order.
Many-to-many relationship

Consider the relationship between the tables “Products” and “Orders”. A single order may include multiple items. On the other hand, one product can be included in several orders. Thus, for each record in the “Orders” table, there can be several records in the “Products” table. Thus, for each record in the Orders table, there can be multiple records in the Orders table. This relationship is called a many-to-many relationship. Note that in order to determine the existing many-to-many relationship schema between your tables, it is very important to consider both sides of the relationship.

To represent a many-to-many relationship, you need to create a third (junction) table that splits it into two one-to-many relationships. The primary keys of two tables are inserted into a third table. As a result, all instances of the relationship are stored in the third table. For example, the Orders and Products tables have a many-to-many relationship defined by creating two one-to-many relationships in the Ordered table. There can be many products in one order, and each product can be listed in many orders.
org/ListItem”>
One-to-one communication

In a one-to-one relationship, each entry in the first table can have at most one associated entry in the second table, and vice versa. Relationships of this type are not often used because information related in this way is usually stored in a single table. A one-to-one relationship is used to partition a table that contains many fields in order to separate part of the table for security reasons, and to store information related to a subset of the records in the main table. After defining such a relationship, both tables must have a common field.

Top of page

Why create relationships between tables?

Relationships between tables can be created directly using the Data Schema window or by dragging a field from the Field List area . Access uses relationships between tables to decide how to link tables for use in a database object. There are several reasons to create relationships between tables before creating other database objects (forms, queries, reports).

Relationships between tables provide information for structuring queries

To work with records from multiple tables, you often need to create queries that join tables. The query matches the values in the primary key field of the first table with the foreign key field of the second table. For example, to get rows that list all orders for each customer, you can create a query that joins the Customers table with the Orders table based on the Customer ID field. In the “Data Schema” window, you can manually specify the fields for the connection. But if the relationship between the tables already exists, Access uses a default join based on the existing relationship between the tables. In addition, when you use one of the Query Wizards, Access uses information about already defined relationships between tables to provide the user with a choice and substitute appropriate default values in the property settings.
Relationships between tables provide information for structuring forms and reports

When you create a form or report, Access uses information about already defined table relationships to provide the user with choices and prepopulate property settings with appropriate default values.
Relationships between tables are the basis by which data integrity can be ensured so that there are no lost records in the database. A lost record is a record with a link to a non-existent record (for example, an order record with a link to a missing customer record).

When a database is created, information is distributed across tables, each of which has a primary key. After that, foreign keys that have links to primary keys are added to the related tables. These foreign and primary key pairs form the basis for table relationships and multi-table queries. Therefore, it is important that the foreign key-primary key references remain in sync. Data integrity, which depends on the relationships in the table, ensures that the links stay in sync.

Top of page

Concept of data integrity

When you create a database, information is distributed across many thematically organized tables to minimize data redundancy. Access then provides the means to collect scattered data by creating common fields in related tables. For example, to create a one-to-many relationship, add the primary key from the table on the “one” side as an additional field to the table on the “many” side. To join data, Access substitutes the value from the table on the “many” side into the corresponding field in the table on the “one” side. Thus, table values on the “many” side are related to the corresponding values on the “one” side.

Suppose there is a one-to-many relationship between the Shippers and Orders tables, and you want to delete a shipper. If the shipper you want to delete has orders in the Orders table, they will become orphaned records when the shipper record is deleted. The shipper ID will remain in the Orders table, but it will be invalid because the record it refers to no longer exists.

The goal of maintaining data integrity is to prevent lost records and keep links in sync so that the hypothetical situation described above never occurs.

Enforce data integrity is enabled for a specific relationship between tables. Once enabled, Access will reject any operations that violate the integrity of the data for this intertable relationship. This means that Access will reject any updates that change the target of the link, as well as deleting the target. You may have a perfectly valid need to change the primary key for a vendor that has orders in the Orders table. In this case, you want Access to automatically update all affected rows in one operation. In this way, Access ensures that the update completes completely and the database is not in an inconsistent state when some rows are updated and others are not. To do this, Access has a parameter Cascading deletion of related entries . If option Cascading deletion of related fields was enabled when data integrity was enabled, then the next time a primary key is updated, Access automatically updates all fields associated with it.

You might want to delete a row and all related records, such as a shipper record and all related orders. To do this, Access has parameter Cascade delete related records . If you select option Cascade delete related records while maintaining data integrity, and then delete the record on the primary key side of the relation, Access automatically deletes all records that refer to the primary key.

Top of page

Viewing relationships between tables

To view inter-table relationships, click Data schema on tab Working with databases . The Data Schema window opens, displaying all existing relationships. If relationships have not yet been defined or if this is the first time this window has been opened, Access will prompt you to add a table or query to the window.

Calling up the Data Schema window

Click File, select “Open” and then select and open the database.
On the Database Tools tab, in the Relationships group, click the Data Schema button.
On the tab Designer, in the Relationships group, click the All Relationships button.

All relationships defined in the database will be displayed. Note that hidden tables (tables that have the hidden in the Properties dialog ) and their relationships are not shown unless the option Show hidden objects is selected in the Navigation Options dialog .

A relationship between tables is represented as a line between tables in the Data Schema window. A relationship that does not provide data integrity is displayed as a thin line between the common fields that maintain the relationship. If you select a link by clicking on a line, the line will become bold. If data integrity is enforced for this relationship, the line will become thicker at the ends. In addition, the number 9 will be displayed above the bold part of the line on one side of the link.0227 1 , and on the other hand, the infinity symbol ( ∞ ).

When the Data Schema window is open, the following commands are available on the ribbon.

On the tab Designer in the group Service

Change links . Opens the Edit Links dialog . When you select a link line, you can click Change relationships to change the relationship between tables. You can also double-click a link line.
Clear layout . Prevents all tables and relationships from being displayed in the Data Schema window. Note that this command only hides tables and relationships, it does not delete them.
Communication report . Creates a report that displays the tables and relationships of a database. The report displays only tables and relationships that are not hidden in the Data Schema window.

On the tab Designer in the group Relations

Adding tables (Adding a table in Access 2013 Allows you to show a selection of tables in the Relationships window.
Hide table . Hides the selected table in the Data Schema window.
Direct links . Displays all relationships and related tables for the selected table in the Data Schema window if they are not already displayed.