# Add Tables & Fields

## Define Semantic Layer <a href="#id-1-define-semantic-layer" id="id-1-define-semantic-layer"></a>

Select and define the tables and fields users can query.

{% hint style="warning" %}
Lumi can only formulate queries that pull from selected tables and fields.
{% endhint %}

{% embed url="<https://www.youtube.com/watch?v=XxGQfjypFJE>" %}

### Step-by-Step Guide <a href="#step-by-step-guide" id="step-by-step-guide"></a>

1. Navigate to the 'Tables' tab
2. Ensure you are in Edit Mode
3. Select the tables you wish to expose to Lumi from the dropdown list.
4. Once a table is selected, provide a table name and add context describing what the table is about. Most databases tend to use an unusual table naming convention. We highly recommend you re-name the table into a more human-readable format. This will drastically improve Lumi’s ability to generate accurate code. For more details, refer to the best practices below.
5. For each selected table, use the drop down to select the fields you wish to expose to Lumi.
6. Once a field is selected, provide a semantic name and add context describing what the field is about. Most databases tend to use an unusual field naming convention. We highly recommend you re-name the field into a more human-readable format. This will drastically improve Lumi’s ability to generate accurate code. For more details, refer to the best practices below.

<figure><img src="https://1092914297-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FbsNNtXffkOLYrtYRQIcx%2Fuploads%2FES1L5xXZpZNnnXaN9uU9%2FUntitled%20presentation.jpg?alt=media&#x26;token=bef22373-44d3-4033-bbbd-1d8c0173d194" alt=""><figcaption></figcaption></figure>

### Table Descriptions Best Practices <a href="#table-descriptions-best-practices" id="table-descriptions-best-practices"></a>

* **Use Intuitive Names.** Names should clearly reflect the table's content and purpose. Use descriptive names that convey the table's function or the type of data it holds, making it intuitive for users to understand what data they will find in each table.
* **Be Consistent**. Consistency in naming conventions is key. Decide on specific naming conventions early in the design process and apply them uniformly across all tables. We recommend following `snake_case` naming convention.
* **Plural vs. Singular Names.** Decide whether to use singular or plural names for your tables and remain consistent. There's debate over which is best, but the most important thing is to choose one approach and stick with it throughout your schema.
* **Use Domain-Specific Terms.** Leverage domain-specific terminology when naming tables, especially in the semantic layer. This ensures that the names are relevant and recognizable to the end-users, who are often domain experts but not necessarily tech-savvy.
* **Avoid Abbreviations and Acronyms.** Unless an abbreviation or acronym is well-known and universally understood by all potential users, it's best to avoid them. They can create confusion and reduce the readability of your schema.
* **Avoid Using Reserved Keywords**. Stay away from reserved SQL keywords (e.g., `user`) and database-specific terms for table names.
* **Prioritize Clarity Over Brevity.** While it's important to avoid unnecessarily long names, clarity should never be sacrificed for the sake of brevity. The name should be as long as necessary to ensure it is understandable.

### Field Descriptions Best Practices <a href="#field-descriptions-best-practices" id="field-descriptions-best-practices"></a>

* **Use Intuitive Names.** Field names should clearly indicate what data they contain. Use complete words that accurately describe the data in each field, ensuring that the name is understandable on its own without needing additional context.

| Table           | Field Name in Source | Semantic Name     |
| --------------- | -------------------- | ----------------- |
| Item Master     | id                   | item\_id          |
| Item Master     | desc                 | item\_description |
| Sales Header    | id                   | sales\_header\_id |
| Sales Header    | sell\_to\_customer   | customer\_id      |
| Purchase Header | card\_code           | vendor\_id        |
| Purchase Header | BPLId                | branch\_id        |

* **Prioritize Clarity Over Brevity.** Field names should be as long as necessary to ensure they are clearly understood. Avoid sacrificing clarity for the sake of keeping names short.
* **Consider End-User Perspective.** Always consider the perspective of the end-user or non-technical stakeholders when naming fields. Use terminology and naming conventions that will be most intuitive to them, even if it means deviating slightly from technical norms or preferences.
* **Follow a Consistent Naming Convention.** Use consistent names for fields found across tables. This will help Lumi better infer implicit join conditions.

| Good Semantic Layer                                                                                                        | Poor Semantic Layer                                                                                                                  |
| -------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------ |
| item\_id is consistently defined across all relevant tables: item master, sales line, purchase line, and inventory tables. | item\_id is used in item master, item\_no is used sales line, product\_id is used in purchase line, sku\_id is used inventory table. |

* **Prefer Verbose Names Over Abbreviations.** While it might be tempting to use abbreviations to keep field names short, they can obscure meaning and make the schema less accessible. Always prefer verbose, descriptive names over abbreviations, unless the abbreviation is widely understood and accepted.
* **Include Units of Measurement Where Applicable.** If a field represents a measurable quantity, include the unit of measurement in the field name. For example, **`temperature_celsius`** or **`length_cm`** make it clear what unit the data is measured in, reducing confusion and the need for additional documentation.
* **Avoid Using Reserved Keywords.** Like with table names, avoid using reserved SQL keywords and database-specific terms for field names to prevent conflicts and errors.
* **Attribute Value Clarification.** For fields with a predefined set of values, explicitly define each possible option.

<table><thead><tr><th width="252">Semantic Name</th><th width="254">Good Context</th><th>Poor Context</th></tr></thead><tbody><tr><td>order_status</td><td>Integer field that represents the order status; 1 = Pending, 2 = Active, 3 = Declined, <br>4 = Closed, 5 = Returned.</td><td>Represents the order status</td></tr><tr><td>inventory_adjustment_type</td><td>Integer field that represents the type of inventory adjustment transaction; <br>1 = Sales, 2 = Purchase, <br>3 = Positive adjustment., <br>4 = Negative Adjustment, <br>5 = Transfer, 6 = Consumption</td><td>Represents the inventory adjustment type</td></tr><tr><td>invoice_status</td><td>represents "O" for opened and "C" for closed</td><td>Represents the invoice status</td></tr><tr><td>sales_order_canceled_flag</td><td>represents "N" for not canceled and "Y" (and others) for canceled</td><td>Flag to represent if order is canceled or not.</td></tr></tbody></table>