# Build Internal DB
## Table Editor
This editor panel helps users to create tables and manage their schema.
**Create new table**
Users can add new table into the database.
| Field | Description |
| --------------- | ----------------------------------------------------------- |
| **Name** | Provide a unique name for the table |
| **Description** | A brief description of the table and the purpose |
| **RLS** | Restrict access to the table by enabling Row level security |
**Manage columns - New table**
This interface allows users to create/update/delete columns of the table.
The interface generates the `id` and `created_at` columns by default. Users can keep them or discard them according to the need, and create new columns using the `Add column` button. Click the `Save` button to commit the work.
Below is the list of information the user will enter when creating a new column and then save the column.
| Field | Description |
| ----------------- | ------------------------------------------------------------------------------ |
| **Name** | Provide a unique name for the column |
| **Type** | Select the type of the column |
| **Default Value** | Set the default value for the column when a new row is inserted into the table |
| **Primary** | Check if the column has to be the primary column of the table |
Add new columns to the table using the `New Column` button.
Input the required fields and click the `Save` button to add the new column.
Add the required columns to the table the same way, using the `New Column` button.
In this example - the other fields like 'email', 'countryresidence', 'primarynationality', and 'alternatenationality' columns are added.
The `Options` button at the end of each column allows the user to `Edit` the column and `Delete`.
**Manage columns - Existing table**
This presents the interface for users to manage the columns of an `existing table` - by clicking `New Column` `Edit Column` `Delete Column`
Below is the list of information the user will enter when creating a new column and then saving the column.
| Field | Description |
| ------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Name** | Provide a unique name for the column |
| **Description** | Select the type of the column |
| **Foreign Key Relation** | This will open up the list of tables where the user wants to link this column as the foreign key, and then pick the column to which the link is complete. It will show an error msg if the data types of both columns do not match |
| **Data Type** | Choose the type of the column from the list |
| **Default Value** | Set the default value for the column when the record gets inserted into the table |
| **Is Primary Key** | Check if the column has to be the primary column of the table |
| **Allow Nullable** | Set if the column can allow nullable values if no value is provided when the record is saved |
| **Is Unique** | Enforce values in the column to be unique across rows |
| **Check Constraint** | Define the rule that will check the value saved in this column meets the criteria |
#### Primary Key
One essential feature of a table is the inclusion of a "primary key," which serves as a distinct identifier for each individual row of data.
It is advisable to establish a Primary Key for each table within your database. Any column can be designated as the primary key, provided that it contains a unique value for each record.
Using a uuid type or a numbered identity column as the primary key is a commonly observed practice.
```sql
create table projects (
id bigint generated always as identity primary key
);
```
We could also use `generated by default as identity`, which would allow us to insert our own unique values.
```sql
create table projects (
id bigint generated by default as identity primary key
);
```
#### List of data types of the column
Each column in a database has a specific data type assigned to it. The database management tool offers a variety of pre-set data types for users to choose from, with the option to create custom types or utilize extensions if necessary.
The SQL editor allows users to work with any data type supported by Postgres. However, the Table Editor interface only displays a limited selection of data types in order to streamline the user experience for those who may not be as familiar with database management.
{% hint style="info" %}
Refer the [`data types`](https://www.postgresql.org/docs/current/datatype.html) for more details
{% endhint %}
| Data Type | Description |
| --------------- | ------------------------------------------------------------------------------------- |
| **int2** | Signed 2byte integer value. `-32,768 to 32,767` |
| **int4** | Signed 4byte integer value. `-2,147,483,647 to 2,147,483,647` |
| **int8** | Signed 8byte integer value. `-9,223,372,036,854,775,807 to 9,223,372,036,854,775,807` |
| **float4** | Single precision floating point number. `23 bits` `8 bits` |
| **float8** | Double precision floating point number. `52 bits` `11 bits` |
| **numeric** | Exact numeric of selected precision |
| **json** | Json formatted text value |
| **jsonb** | Binary Json text |
| **text** | Variable length characters |
| **varchar** | Variable length characters |
| **uuid** | Universally unique identifier |
| **date** | Calendar date format |
| **time** | Time of day without timezone |
| **timetz** | Time of day with timezone |
| **timestamp** | Date and time without timezone |
| **timestamptz** | Date and time with timezone |
| **bool** | `true` or `false` |
**Sort tables**
This is the option to sort the table and view - by `Alphabetical` or `Entity Type`
**Search tables**
This is the feature to search and filter down the tables on the list.
**Filter columns**
This lets the user narrow down the columns they want to view/update/delete.
## SQL Editor
This editor helps the user to write and run queries using SQL language. There is a result window below the query window for viewing the execution results. This serves as the query panel where the user can operate with the tables and data using SQL syntax.
### Joining tables with foreign keys
The origin of the term "Relational" in database management is rooted in the interconnected nature of data, which exhibits some form of relational structure. In the case of the example involving "projects," it may be related to a designated "category" for each project, such as "Infrastructure" or "Cloud." To facilitate this, we shall establish a distinct table known as "categories" and establish a connection with our existing movies table.
```sql
create table categories (
id bigint generated always as identity primary key,
name text -- category name
);
alter table projects
add column category_id bigint references categories;
```
You can also create "many-to-many" relationships by creating a "join" table.
```sql
create table projects (
id bigint generated by default as identity primary key,
name text,
description text
);
create table resources (
id bigint generated by default as identity primary key,
name text
);
create table performances (
id bigint generated by default as identity primary key,
project_id bigint not null references projects,
resource_id bigint not null references resources
);
```
## Schema Visualizer
This panel displays the tables and columns as a network of schema, including the foreign key links. This helps the user to visualize the overall DB schema of the app.
Tables are part of schemas, which serve as a structured method for organizing tables, typically implemented for security purposes.
If a schema is not specified during the table creation process, Postgres will default to creating the table in the public schema.
We can establish schemas to structure our tables. The creation of a private schema will be kept hidden from our application programming interface (API).
```sql
create schema private;
create table private.credits(
id bigint generated by default as identity primary key,
credit bigint not null,
resource_id bigint not null references public.resources
);
```
## Views
A view is a virtual table based on the result of a SELECT statement from one or more tables. It provides a way to simplify complex queries, hide data complexity, and provide security by restricting access to specific columns of a table.
For example, suppose you have a table named employees containing information about company employees. In that case, you could create a view named **employee\_names** that includes only the first\_name and last\_name from the employees table. Here is the SQL statement:
```sql
CREATE VIEW employee_names AS
SELECT first_name, last_name
FROM employees;
```
Now, you can use the **employee\_names** view in your SQL queries like this. This query will return all the rows from the employee\_names view, which contains only the first\_name, last\_name columns from the employees table.
```sql
SELECT * FROM employee_names;
```
It's essential to remember that a view in SQL does not store data. Instead, it is just a SELECT statement executed each time you query the view. It means that the data in the view is always up-to-date with the data in the underlying table or tables. It is possible to update data via the view in certain circumstances, but not recommended. You should modify the data in the underlying table if you need to modify the data.
#### Security
As a standard practice, views are typically accessed based on the creator's authorization, known as the "security definer" setting. When a privileged role generates a view, individuals who access it will inherit the permissions assigned to that role. To implement row level security measures, the view should be defined with the "security invoker" modifier.
Views can restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. We can prevent them from reading sensitive columns by excluding them from the underlying query.
#### Materialized View
A materialized view is a view that stores the results of the view’s query. Whenever you query the materialized view, it returns the data stored with the view itself. It doesn’t run the view’s query against the underlying tables.
```sql
CREATE MATERIALIZED VIEW viewname
[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
[BUILD IMMEDIATE|BUILD DEFERRED]
AS
select_query;
```
A materialized view can combine all of that into a single result set that’s stored like a table.
Calculations can also be added to materialized views for any fields you may need, which can save time, and are often not stored in the database. For example, perhaps you want to store both the monthly and annual salary for an employee. Normally, you would only store one and calculate the other, but in a materialized view, you can store both.
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)
.png?alt=media)