LinkedIn
Twitter
1. Organize Your Tables
First, it's essential to organize your tables in a way that makes sense to you.
Example: Let's say you have 2 tables called "Income" and "Expenses". An important aspect to note is the naming convention of your columns. Below are two tables to help you understand why proper naming is crucial, especially when writing formulas and using data from other tables.
Revised Tables
Key Tips:
- Use Unique ID Columns: Always include an ID column in each table to uniquely identify each record. This is vital for creating relationships between tables and for effectively tracking individual entries.
I chose the naming convention of "IncomeAmount" and "ExpenseAmount" instead of simply "Amount" to avoid confusion when writing formulas. This approach makes it easier to identify and select the correct data from each table, especially when working with complex formulas across multiple tables, like:
SUM(SELECT(Income[IncomeAmount], [IncomeDate] = TODAY())) + SUM(SELECT(Expense[ExpenseAmount], [ExpenseDate] = TODAY()))
2. Manage Data Visibility
- Security Filters: Try using security filters if you want to show specific views to different types of users. This is useful when you need one view to display to all users but want to restrict view or edit access.
- Slices: Slices are also useful if you need to turn your data into smaller, more manageable parts. This can be particularly helpful when you have custom views for different purposes.
3. Use Virtual Columns for Data Management
- Virtual Columns: Use virtual columns for computations, concatenating, or combining two datasets. Virtual columns are computed from your data and do not take up space in your database.
