Lucid Chart — Graphically Architecting/Creating SQL database for Applications
→ If you are a Solutions Architect, Lead or developer who deals with architecting complex databases of applications, this software will be very useful.
→ This is for SQL based databases at present.
→ We did database architecture on Lucid Chart and realized this experience is worth sharing.
→ Normally when we have one or two applications we create them in a regular way — going into the database or by models.
→ If we have responsibilities of more than five or ten applications graphical view helps us a lot.
Let us explore and create a database and tables together.
What is a Lucid Chart?
→ The lucid chart is the visual collaboration software that performs many tasks.
→ Database architecture (Entity Relational Diagram or ERD), is one of the main tasks
Who uses Lucid Chart?
The lucid chart is used by most Fortune 500 companies like Google, Amazon, NBC, Microsoft, etc.
Hand-on
→ Let us create a database diagram in real-time. The video is also attached below for better understanding.
→ Click the Signup button. Create your account and log in to the dashboard.
→ Once we login we see the below dashboard.
→ Click New → Blank Document
→ We are now on the diagram to create the page. Click the Search icon.
→ Search ERD (i.e. Entity Relational Diagram).
Terminology:
a. Field: column name we are adding to the table. eg. first name, last name, etc
b. Key: Primary key, Foreign Key, etc
c. Type: integer, character varying, date, etc.
There are four ways types of tables create:
a. Defining only field:
b. Defining field and key:
c. Defining field and type
d. Defining key, field and type
→ We can choose any table type as per requirement. Mostly we use the last type where all of them are mentioned.
Which table we are creating?
Here we will build three tables — customer, purchase order and product.
We can add as many fields and complex tables.
a. Create customer table:
→ Drag and Drop the last table on the sheet
→ Enter the field name, type and key. If it is a simple key and not Primary Key or Foreign Key we may keep it empty.
→ Add more rows by right click. Click Insert row below
b. Creating a purchase order table (i.e. order):
→ Drag and Drop the last table on the sheet
→ We now have to write our field name editing key, field and type
c. Finally we have to create the product table:
→ Drag and drop table from left
Understanding Relationships in the table:
→ By relationships, we mean linking two or more tables by lines.
eg1. The foreign key of one table will link to the primary key of the respective table
eg2. one customer can have many orders,
eg3 a user can order one product many times or many products many times.
Creating Relationships in the table:
Eg1: Let us add customer_id of order table as a Foreign key
→ We have clicked the order table and dragged a connection line to primary key of the customer table.
Eg2: one order can have many products
Exporting Table (i.e. ERD diagram):
→ Once table design and relationships are done, the next step is to export the ERD.
→ Mark your diagram as complete
→ Search for Entity Relationship dropdown from the left. Click Export
→ Lucid supports most SQL databases:
a. MySQL:
b. PostgreSQL:
c. SQL Server:
d. Oracle SQL:
e. Quickbase:
→ Based on the SQL database you use, copy the SQL query from textarea.
→ For this article, we will use PostgreSQL.
Creating database and table in PostgreSQL:
STEP 1: Open PostgreSQL pgAdmin.
If we are using a different SQL database, we should open the dashboard and open the query tool.
STEP2: Enter your database credentials if asked
STEP3: Right-click on Database and create a table:
STEP4: Enter our database name for the application eg. purchase_order_customer. Click Save and the database is created.
STEP3: Right-click on the server and click the Query tool. Here we can write our query
STEP4: Copy-paste the query. We can copy-paste in one go or in the split.
Eg.
CREATE TABLE "customer" ( "customer_id" integer PRIMARY KEY, "firstname" character varying, "lastname" character varying, "street" character varying, "city" character varying, "zip" integer, "phone" integer);CREATE TABLE "order" ( "order_number" integer PRIMARY KEY, "customer_id" integer, "customer_name" character varying, "to_street" character varying, "product_id" integer, "to_city" character varying, "to_state" character varying, "to_zip" integer, "ship_date" date);CREATE TABLE "product" ( "product_id" integer PRIMARY KEY, "quantity" integer, "product_type" character varying);ALTER TABLE public.order ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id);
→ Finally, our table is created.
NOTE:
If we have a very complex database architecture as shown below try to break the big query into smaller components and run it in the editor.
Video:
Closing Thoughts:
In this article, we have learned ways to create a database table graphically, add relationships between tables and export easily.
A lucid chart supporting all SQL databases makes it unique. We can also download it as a PDF, PNG etc. formats and share it with our team for easy application database understanding.
Thank you for reading till the end 🙌 . If you enjoyed this article or learned something new, support me by clicking the share button below to reach more people and/or give me a follow on Twitter and subscribe Happy Learnings !! to see some other tips, articles, and things I learn about and share there.
