You are designing a schema for a Cloud Spanner customer database. You want to store a phone number array field in a customer table. You also want to allow users to search customers by phone number.
How should you design this schema?
You are designing a schema for a Cloud Spanner customer database. You want to store a phone number array field in a customer table. You also want to allow users to search customers by phone number.
How should you design this schema?
To design a schema for storing a phone number array field in a customer table and allowing users to search customers by phone number, you should create a table named Customers as a parent table and a table named Phones. Interleave the Phones table into the Customers table and create an index on the phone number field in the Phones table. This approach leverages the hierarchical structure of interleaved tables in Cloud Spanner, providing efficient storage and retrieval of related data, and improves query performance by allowing direct indexing and searching of phone numbers within the customer context.
i vote D since it said 'interleave'
Correct. Just sharing a link: https://cloud.google.com/spanner/docs/schema-and-data-model#creating_a_hierarchy_of_interleaved_tables
Correct answer is C, as in the question states: "You want to store a phone number array field in a customer table". So... adding the phone number as array field and adding a secondary index should be the best option in this case.
i say B, because if a user has more numbers you are storing the same user multiple times each time changing the phone number. Having a second table for phone numbers and having a foreign key that points to the user with this phone number avoid this duplication problem.
https://cloud.google.com/spanner/docs/data-types -->can't set secondary index in array so I vote D
D seems quite nice, but what do u thing about statement "You want to store a phone number array field in a customer table.", and interleave in another table, not customer one. In sql there is array field, and using UNNEST function is possible to filter records based on array, then answer A
D is correct
The correct answer is D. You should create a table named Customers as a parent table and a table named Phones, and interleave this table into the Customer table. You should also create an index on the phone number field in the Phones table. This allows you to store the phone number array field in the Customers table and search for customers by phone number using the index on the Phones table.
C is not a valid solution because Cloud Spanner does not allow creating secondary indexes on array fields.
https://cloud.google.com/spanner/docs/schema-design#creating-indexes: "bad idea to create non-interleaved indexes on columns whose values are monotonically increasing or decreasing" Since phone numbers monotonically increase/decrease, I would take D as the answer
D is the correct answer
Search on ARRAY column is best here. Answer: A
It's D.
I will go with D.
D. Create a table named Customers as a parent table. Create a table named Phones, and interleave this table into the Customer table. Create an index on the phone number field in the Phones table. This design will allow you to store multiple phone numbers for each customer and efficiently search for customers by their phone numbers. In Cloud Spanner, tables can be interleaved, which means that the child table's rows are co-located with the parent table's rows. This setup can offer better performance for certain types of queries and data models, especially when there's a strong relational structure.
The best approach is D. Create a table named Customers as a parent table. Create a table named Phones and interleave this table into the Customer table. Create an index on the phone number field in the Phones table. Here's why: Interleaved Tables: Interleaved tables in Cloud Spanner are designed for efficient storage and retrieval of related data. By interleaving the Phones table into the Customers table, you ensure that data for a specific customer is stored together, improving query performance. Indexing for Search: Creating an index on the phone number field in the Phones table allows for efficient searching of customers based on their phone numbers. This is crucial for your requirement to allow users to search customers by phone number. Scalability: This approach scales well as your customer database grows. Cloud Spanner automatically handles the distribution and scaling of data across multiple nodes.
Let's look at why the other options are less suitable: A. Create a table named Customers. Add an Array field in a table that will hold phone numbers for the customer. This approach is inefficient for searching. You would need to scan the entire array field for each customer, which can be slow for large datasets. B. Create a table named Customers. Create a table named Phones. Add a CustomerId field in the Phones table to find the CustomerId from a phone number. This approach is less efficient than interleaving. You would need to join the Customers and Phones tables for every search, which can be slower than using interleaved tables. C. Create a table named Customers. Add an Array field in a table that will hold phone numbers for the customer. Create a secondary index on the Array field. Cloud Spanner doesn't support secondary indexes on array fields.