A foreign key is a value or column in one table that references an entity or key in another table. Shared identifiers let joins combine related records into a composite record. For example, a company ID in a companies table can be referenced by company_id in an employees table. section 2.1.2
The same linking idea applies to search documents. Separate text, image, video, or audio documents can be related when they contain a shared term such as “Haystack.” Unstructured data can also support fuzzy foreign keys: different phrases, such as a full name and a first name, may resolve to the same entity before joining. section 2.1.3 section 2.1.3
Foreign-key relationships support several increasingly flexible forms:
- Exact joins match a shared discrete value, commonly an ID field. section 2.1.2
- Document joins match the same term across separate search records. section 2.1.3
- Fuzzy joins resolve multiple representations to one entity before creating the relationship. section 2.1.3
Figure 2.3 Structured data in a typical database. Discrete values represent identifiers and enumerated values, continuous values represent data that falls within ranges, and foreign keys exist when the same value exists across two tables and can thus be used as a shared attribute that creates a relationship between corresponding rows from each table.
Use the structured-data diagram to compare discrete identifiers, continuous values, and foreign-key relationships across table rows. section 2.1.2
Figure 2.5 Fuzzy foreign keys. In this example, the same entity is being referenced using different terms, and a join is occurring based upon multiple phrases resolving to the same entity.
Use the fuzzy-key diagram to see how different phrases can resolve to the same entity and support a join. section 2.1.3
