How and when should I use schema specific tables or the single “registry_entity” table?

Schema Specific Table

Let’s say I want to connect a registry table myosin_motor_protein to a results table procession_assay and pull certain values out from these 2 schemas. The query would look like so:

SELECT assay.timestamp AS "Time",
assay.fluorescence_rfu AS "Fluorescence",
myosin.name$ as "Myosin",
myosin.created_date AS "Created Date",
myosin.procession_rate AS "Procession Rate",
myosin.protein_family AS "Protein Family",
myosin.color as "Color"

FROM alab.myosin_motor_protein AS myosin

LEFT JOIN alab.procession_assay$raw AS assay
ON assay.entity = myosin.id

Here’s what the output might look like:

“Registry_Entity“ Table

By using the overarching registry_entity, schema$raw and field$raw tables, and then narrowing down by particular schema, the query results show all of the data that you’ve specified by schema using the WHERE clause.

SELECT registry_entity.id, registry_entity.name, field.field_name, field.value
FROM registry_entity 
LEFT JOIN schema$raw schema 
ON registry_entity .schema_id = schema.id
LEFT JOIN field$raw field
ON schema.id = field.schema_id
AND registry_entity.id = field.registry_entity_id
WHERE schema.name = 'Myosin Motor Protein'
ORDER BY registry_entity.id, field_name

Here’s what the output might look like:

Pre-Schema vs "Registry_Entity" Comparison