First of all, I don't think it matters whether or not data complies to the relational model. What you need here is just a list of passed certifications associated with a contact record.
In terms of Solr and Lucene, every contact should be represented by a separate document in the index. The list of certification IDs should be stored in a field that you'll be able to query later.
Luckily, Sitecore has a predefined way of achieving this. You don't even have to use a custom index. Instead, you can store custom fields in the contact documents of the Analytics Index.
1. Define a custom contact facet
Certifications should be represented by contact facets—the out-of-the-box storage for custom contact data. Follow this official documentation page:
Create a custom contact facet
2. Index your custom facet
You'll need to implement a custom contactindexable.loadfields
pipeline processor and inherit it from ContactIndexableLoadFieldsProcessor
. You'll be able to add custom data fields to the index. You'll also need to configure the custom field to be stored in the Analytics index—I suggest that you define an index field contact.Certifications
with type="System.GUID"
. Follow this guide for more details:
Index custom contact facets
Here's an approximate implementation:
public class IndexCertifications : ContactIndexableLoadFieldsProcessor
{
protected override IEnumerable<IIndexableDataField> GetFields(ContactIndexableLoadFieldsPipelineArgs args)
{
var fields = new List<IIndexableDataField>();
var certifications = // get the list of certifications
foreach (var certification in certifications)
{
fields.Add(new IndexableDataField<Guid>("contact.Certifications", certification.Id));
}
return fields;
}
}
Note that, since Lucene and Solr allow to store several values in the same field, in the code above I am returning multiple field objects with the same name.
3. Query the Analytics index
To be able to query your custom field, you first need to extend the class Sitecore.ContentSearch.Analytics.Models.IndexedContact
:
public class BrandIndexedContact : IndexedContact
{
[IndexField("contact.Certifications")]
public Guid Certifications { get; set; }
}
Then you can query it like this:
ISearchIndex index = ContentSearchManager.GetAnalyticsIndex();
using (IProviderSearchContext context = index.CreateSearchContext())
{
var certificationId = new Guid("{certification-id-here}");
IQueryable<BrandIndexedContact> query =
context.GetQueryable<BrandIndexedContact>()
.Where(_ => _.Certifications == certificationId);
SearchResults<BrandIndexedContact> results = query.GetResults();
}