Provide meaningful names & follow naming conventions: On creating a workspace, data source, entity & field aliases, or query, make sure to give a proper name & description indicating its purpose, so that it becomes readable and easily maintainable for the users who are collaborating.
Role-based privilege: While adding users to the application or to the workspace, assign administrative roles only if required, as granting them full privilege means the application/workspace as a whole is open to updation and deletion.
Secure database access: Implement proper authentication and authorization mechanisms to ensure that only authorized users can access the database. Apply the principle of least privilege, granting only the necessary permissions to the app's database user.
Use appropriate indexes: Analyze the query patterns and if possible i.e. if the database is within the user’s reach to modify, create indexes on columns that are frequently used in WHERE clauses or JOIN conditions. Indexes can significantly improve query performance by enabling faster data retrieval.
Use backticks to reference tables and attributes: Although usage of backticks (`) is only required for enclosing table names or attributes that are SQL reserved keywords, for better readability and making it less error-prone, it is always recommended to enclose the table name in backticks.
Project only required columns: To optimize search queries, avoid excessive use of "*" and instead focus on specific columns and specific search criteria.
Implement pagination: When retrieving large result sets, implement pagination to fetch data in smaller chunks using the LIMIT and OFFSET clauses. This improves app performance by reducing the amount of data transferred and displayed at once.
Regularly analyze and optimize queries: Monitor and analyze the performance of the SQL queries. Identify slow-performing queries and optimize them for better efficiency.
Optimize query performance: Ensure the queries are efficient by avoiding unnecessary joins or subqueries and optimising data retrieval operations. Avoid making repetitive or redundant queries and optimize the SQL statements for better performance.
Use proper indentation and formatting: Format the queries for readability using indentation, line breaks, and consistent spacing. This improves code clarity and makes it easier to identify errors.
Avoid deleting used Aliases: It is a good practice to not delete aliases that are being used in the workspace. The existing mappings if there are any, would be shown in the prompt while deleting an alias. If it is to be deleted, remove the mappings from the items/fields and modify the queries where the alias is used. This way there will be a good consistency achieved across the workspace.