Field storage decision for a high-cardinality attribute
A product catalogue needs a sku string (max 64 chars, unique, frequently filtered and sorted), a description (long text, rarely filtered), and a specifications array of key/value pairs (up to 40 pairs per product, each key unique per product, values up to 255 chars). The site has 500,000 products imported nightly. Editors need to filter products by SKU in views and sort by SKU.
1. Choose field types and storage for each of the three attributes, and justify the choice for the specifications array.
Read the worked answer
SKU: a string field (not text) with a unique constraint on the field, because string fields are stored in the base table and are directly indexable and sortable in views; a text field with a format would be stored in a separate table and cannot be indexed the same way. Description: text_long with a text format, stored in its own field table; it is not filtered, so no index is needed. Specifications: the key/value pairs with unique keys per product are a natural fit for a map field type (Drupal\Core\Field\Plugin\Field\FieldType\MapItem), which stores keys and values as a serialized blob in a single column per delta. Because the keys are per-product and not globally searchable in views, a map avoids creating 40 columns or a separate entity per pair. If editors need to filter by a specific specification key in views, the map is a poor fit and a paragraph or multi-value key/value field with a separate table would be needed; the setup says filtering is only by SKU, so map is correct.
2. What indexing and performance considerations apply at 500,000 products with a nightly import?
Read the worked answer
Ensure the SKU column has a database index (field storage indexes in the field's schema, or a unique key constraint), because views filtering and sorting on an unindexed column at 500k rows will table-scan. The nightly import should use batch processing with entity saves in chunks (e.g., 50-100 per batch) and should not run drush cr per item; use \Drupal::entityTypeManager()->getStorage('node')->save() in a batch, or better, a direct SQL upsert for the bulk fields if the import is the only writer, then rebuild search indexes. Watch for the map field's serialized blob growing large; 40 pairs of 255 chars is ~10KB per product, which is acceptable in a text/blob column but should not be loaded in list builders. Use EntityQuery with accessCheck(FALSE) for the import and avoid loading full entities when only the SKU is needed.