Overview of this article
In this part 1 of our series, we will add a table called users to our relational database, along with all the fields (columns) needed. The SQL script for doing this is provided below for your ease. Our overall goals include: – Modularity for easy future changes and expansion. – Clarity, consistency, and readability of fields (see the Naming conventions section below). – Ease for you to both understand and quickly copy/paste the code below into your database management tool’s ‘Run SQL’ feature.
Assumptions and prerequisites
We’ll assume the following for the purposes of you being able to follow this part 1 of the 5-part tutorial: – You have a PostgreSQL database set up. – You have access to a database management tool like PGadmin, DBeaver, or any of the numerous others that allow you to create and manage tables in your Postgres database. – You know how to run SQL commands with your database management tool in order to create the users table below.
Note: This tutorial is specifically created for Postgres. However, If you have little or no experience with PostgreSQL OR if your database is actually MySQL, Microsoft SQL Server, or some other database platform, the instructions below – while not guaranteed – should work fine or require very little adjustment.
Create the users table
Paste the code below into your db management tool’s Query tool. If you are using PGadmin, do the following: (a) Select your database. In the image below we called ours “practice”. (b) Click the “Tools” dropdown menu. (c) Choose “Query Tool”. (d) In the right pane labeled “Query Editor,” paste in the code we’ve given you below that begins with “CREATE TABLE public.users”.
![Image of SQL editor](https://gyazo.com/4d57f770fce0c988d98fa1e5327fdb02.png “Image of SQL editor”)
id serial NOT NULL,
id_session int8 NULL,
t_email VARCHAR(128) NULL,
t_password VARCHAR(128) NULL,
t_name_user VARCHAR(64) NULL,
t_name_first VARCHAR(64) NULL,
t_name_last VARCHAR(64) NULL,
t_phone VARCHAR(32) NULL,
t_ip_address VARCHAR(32) NULL,
i_security_level int8 NULL,
d_visit_first DATE NULL,
d_visit_last DATE NULL,
b_enabled bool NULL,
CONSTRAINT users_pkey PRIMARY KEY (id)
CREATE UNIQUE INDEX users_id_idx ON public.users USING btree (id);
When the above code is executed, we get the following table:
![Image of users table columns](https://gyazo.com/747cb60d751ce5e586b3a4e72a8f03de.png “Image of users table columns”)
Did you notice that we chose the “serial” type for the id field? With PostgreSQL, when you choose “serial” as the data type for a field (column), the database tool will place the following into the “default [value]” property for that field in your users table:
The clue here is the system’s use of the terms “nextval” and “seq”. Used in the overall syntax we see here, the result is that any time we create a new row in the users table, the id field is autoincremented by 1. Here is a picture illustrating the results of this process:
![Image of id field properties](https://gyazo.com/21e93d9d8ef5993841eb3c33c52fc43c.png “Image of id field properties”)
You may have noticed the “t”, “i”, “d”, and “b” prefixing our field (column) names. For purposes of consistency, organizatione, code re-use, elegance, and overall efficiency, we recommend use of prefixes like these (or of your own design). If you want to know more about our take on naming conventions, we invite you to look at this article: [https://kb.objectrocket.com/postgresql/postgresql-naming-conventions-637] (https://kb.objectrocket.com/postgresql/postgresql-naming-conventions-637)
For the purpose of this tutorial, you will not need the following fields: – t_name_first – t_name_last – t_phone – i_security_level
That said, there is no impact on performance or storage by leaving these fields in for now. Why leave them in? When you expand the scope of your application, it is likely those fields – and more – will later be needed.