🤫Questions and Answers

Show a clear example of why a query to an ideal data structure is simpler than the same query to a relational data structure

In the context of the ideal data structure as described, a query to retrieve data is simplified by the elimination of the 'Names' table and the use of a dictionary or list of constants in the application code. This approach reduces the complexity of queries by avoiding the need to join multiple tables to resolve human-readable names to their corresponding IDs.

For example, in a relational data structure, to get a document's details, you might need to perform a query that involves joining several tables, such as 'Documents', 'Document_Types', and 'Providers', to resolve the names and types associated with numeric IDs. This could look something like:

SELECT d.document_name, dt.type_name, p.provider_name
FROM Documents d
JOIN Document_Types dt ON d.type_id = dt.id
JOIN Providers p ON d.provider_id = p.id
WHERE d.id = 1;

In contrast, with the ideal data structure, the query can be simplified by directly accessing the 'Sets' and 'Values' tables without the need for resolving names through joins. The application code, using a predefined list of constants, already knows the 'Name_id' corresponding to the properties it needs to query. This could be simplified to:

document_name = get(2, document_name, None)

Here, get is a function that internally knows how to retrieve the document's name based on the 'Name_id' (e.g., document_name constant) without the need for a join operation to resolve the name. The constants used in the application code replace the need for the 'Names' table, making the query simpler and more direct.