What changes are you trying to make?

In this project, I aimed to improve the data handling processes within our SQL environment. Key changes included creating a new table called product_units, which only contains products with a quantity type of 'unit'. I also updated the database by adding a new column to product_units for tracking the current quantity, while ensuring that the vendor inventory data accurately reflects the revenue each vendor could generate from their products. Additionally, I refined existing SQL queries to accurately calculate total revenues based on the number of products each vendor has available for sale.

What did you learn from the changes you have made?

Through these changes, I learned the importance of data normalization and how creating specific tables for distinct data types can simplify queries and improve overall database efficiency. I also gained insights into how SQL operations such as INSERT, UPDATE, and DELETE are interconnected and require careful consideration of data integrity. Moreover, this project reinforced the need for thorough testing after each change to ensure that the database functions as intended.

Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?

Initially, I considered using temporary tables or views instead of a permanent table for product_units. This approach would allow for dynamic data access without the overhead of maintaining a separate table. However, I opted for creating a permanent table to facilitate easier reporting and long-term data management, especially since the product data is used frequently in various analyses.

Were there any challenges? If so, what issue(s) did you face? How did you overcome it?

One major challenge I faced was ensuring that the CROSS JOIN operation accurately calculated total revenues without generating excessive data duplication. I encountered syntax errors and issues with missing columns, which I addressed by meticulously reviewing table schemas and ensuring that all required columns were correctly referenced in my queries. Collaborating with peers and testing each query step-by-step helped me identify and resolve these issues effectively.

How were these changes tested?

I tested the changes by executing a series of SQL queries that retrieved data from the product_units table and validated the results against expected outcomes. I performed checks to ensure that the revenue calculations were accurate by running sample queries and comparing them with manual calculations. Additionally, I conducted unit tests for the INSERT, UPDATE, and DELETE operations to confirm that each function performed as intended and did not disrupt the integrity of existing data. This rigorous testing process ensured that the database was robust and reliable.