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?
Click on the arrows to vote for the correct answer
A. B. C. D.C.
Cloud Spanner is a horizontally scalable, strongly consistent relational database service provided by Google Cloud Platform (GCP). When designing a schema for a Cloud Spanner customer database, it is important to consider the data types and indexing options available to optimize the performance of the database.
In this case, the requirement is to store a phone number array field in a customer table and also allow users to search customers by phone number. Here are the explanations for each answer:
A. Create a table named Customers. Add an Array field in a table that will hold phone numbers for the customer.
This approach suggests creating a single table named Customers and adding an array field to store phone numbers for each customer. While this design is simple, it can become challenging when searching for specific phone numbers as it requires a full table scan to find a match. Therefore, this approach may not be efficient for searching by phone number.
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 suggests creating two tables: Customers and Phones. In the Phones table, there would be a CustomerId field that would be used to find the corresponding customer for a phone number. This design provides a more efficient way of searching for customers by phone number, as the index can be built on the Phone table's CustomerId field.
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.
This approach is similar to option A but suggests adding a secondary index on the array field to improve the performance of searching by phone number. While this can improve search efficiency, it can also increase the database's write latency as each update to the phone number array field will require updating the secondary index.
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 approach suggests creating a parent table named Customers and a child table named Phones. The Phones table is interleaved into the Customers table, meaning that the child rows are stored within the parent rows, which can improve query performance. An index is created on the phone number field in the Phones table to enable efficient searching by phone number.
In summary, option B is the most appropriate design for storing a phone number array field in a customer table while also allowing efficient searching by phone number.