DEV Community: cenan ozen

Exploring String Field Types in Databases: CHAR, VARCHAR, and TEXT

cenan ozen — Fri, 06 Oct 2023 10:31:37 +0000

In this post, we will explore and compare the different string field types commonly used in databases: CHAR, VARCHAR, and TEXT. We will discuss their characteristics, use cases, and the considerations that DBAs and developers should keep in mind when choosing the right string field type.

Which string type you choose will depend on the specific needs of your application. If you need to store a fixed length string, such as a ZIP code, then you should use a CHAR column. If you need to store a variable-length string, such as a customer name, then you should use a VARCHAR column. If you need to store a very large string, or binary data, then you should use a TEXT or BLOB column.

Limitations

There usually is a limit, for the length of the field depending on the type.

Here are the maximum VARCHAR field sizes for some common databases:

MySQL: the maximum size of a VARCHAR field is 65,535 bytes.
PostgreSQL: the maximum size of a VARCHAR field is 1,073,741,823 characters. (1 GB)
Oracle Database: the maximum size of a VARCHAR field is 4,000 bytes (which can be fewer than 4,000 characters, depending on character encoding).
Microsoft SQL Server: the maximum size of a VARCHAR field is 8,000 bytes.
SQLite: the maximum size of a VARCHAR field is 2,147,483,647 bytes. (2 GB)
IBM Db2: In IBM Db2, the maximum size of a VARCHAR field depends on the version and configuration, but it is typically at least 32,767 bytes.

and for the CHAR type:

In MySQL, the maximum size of a CHAR field is 255 characters. However, this limit can be increased to 65,535 characters by modifying the row format to DYNAMIC or COMPRESSED.
PostgreSQL: the maximum size of a CHAR field is 1,073,741,823 characters.
Oracle Database, the maximum size of a CHAR field is 2,000 bytes (which can be fewer than 2,000 characters, depending on character encoding).
Microsoft SQL Server: the maximum size of a CHAR field is 8,000 bytes.
In SQLite, the maximum size of a CHAR field is 2,147,483,647 bytes.
In IBM Db2, the maximum size of a CHAR field depends on the version and configuration, but it is typically at least 32,767 bytes.

and finally for the TEXT field type:

Database	Maximum size
MySQL	4 GB
PostgreSQL	1 GB
Oracle Database	4 GB
Microsoft SQL Server	2 GB
IBM DB2	2 GB

Performance

Which string type you choose will also affect the performance of your database application. CHAR columns are generally the fastest, followed by VARCHAR columns. TEXT and BLOB columns are the slowest.

Conclusion

Thank you for reading, and see you on another post 👋👋

Types of SQL Joins

cenan ozen — Wed, 14 Dec 2022 20:29:55 +0000

In SQL, a join is a way to combine data from multiple tables based on a related column between them. This allows you to query data from multiple tables in a single statement. In this article, we will explore the different types of joins in SQL.

There are several different types of joins in SQL, including inner joins, outer joins, cross joins. and self joins.

We will use the following users and posts tables for our examples:

users:

id	name
1	John
2	Harold
3	Lionel

posts:

id	user_id	caption
1	1	foo
2	1	bar
3	2	baz
4	NULL	qux

Inner Join

Inner joins are the most common type of join and are used to combine rows from two or more tables based on a related column between them. It is the default join, which is used when you don't specify a join type in your SQL statement.

SELECT * FROM users
INNER JOIN posts
ON users.id = posts.user_id;

This statement would return all rows from users and posts where the id column is the same in both tables. The resulting table would include only the columns from users and posts, and would include a row for each pair of matching rows from the two tables.

Result:

id	name	id	user_id	caption
1	John	1	1	foo
1	John	2	1	bar
2	Harold	3	2	baz

Let's visualize the result as sets so we can understand it better. The blue are is our result set:

Outer Joins

An outer join, on the other hand, is a type of join that combines rows from two or more tables and includes unmatched rows from one or more tables. There are three types of outer joins: left, right, and full.

Left Outer Join

A left outer join includes all the rows from the left table, and the matched rows from the right table.

SELECT * FROM users
LEFT JOIN posts
ON users.id = posts.user_id;

Result:

id	name	id	user_id	caption
1	John	1	1	foo
1	John	2	1	bar
2	Harold	3	2	baz
3	Lionel

And as a set:

Before we go on and talk about right joins, looking at the above picture, what if we want to get the difference of the left set from the right one without the intersection?

In that case we should add a where condition that eliminates the intersection from the result:

SELECT * FROM users
LEFT JOIN posts
ON users.id = posts.user_id
WHERE posts.user_id IS NULL;

Result:

id	name	id	user_id	caption
3	Lionel

As you see, we have the previous result minus the inner join.

Right Outer join

A right outer join includes all the rows from the right table, and the matched rows from the left table. It is basically the symmetrical to the left outer join.

SELECT * FROM users
RIGHT JOIN posts
ON users.id = posts.user_id;

Result:

id	user_id	caption	id	name
1	1	foo	1	John
2	1	bar	1	John
3	2	baz	2	Harold
4	NULL	qux

Full Outer Join

And a full outer join includes all the rows from both tables, whether they are matched or not.

SELECT * FROM users
FULL OUTER JOIN posts
ON users.id = posts.user_id;

Result:

id	name	id	user_id	caption
1	John	1	1	foo
1	John	2	1	bar
2	Harold	3	2	baz
3	Lionel
		4	NULL	qux

Cross Join

A cross join, also known as a cartesian join, is a type of join in SQL that combines every row from one table with every row from another table, resulting in a cartesian product. Here is an example of a cross join:

SELECT *
FROM users
CROSS JOIN posts;

Result:

id	name	id	user_id	caption
1	John	1	1	foo
1	John	2	1	bar
1	John	3	2	baz
1	John	4		qux
2	Harold	1	1	foo
2	Harold	2	1	bar
2	Harold	3	2	baz
2	Harold	4		qux
3	Lionel	1	1	foo
3	Lionel	2	1	bar
3	Lionel	3	2	baz
3	Lionel	4		qux

This statement combines every row from users with every row from posts, resulting in a table with the columns from both users and posts. The number of rows in the resulting table would be the product of the number of rows in users and the number of rows in posts. It is important to note that a cross join does not have a join condition, and as such, it will return a cartesian product even if the tables have no matching rows. Because of this, cross joins are not often used in practice, and other types of joins, such as inner or outer joins, are generally preferred.

Self-join

This type of join is used to join a table to itself, using an alias for the second instance of the table. It is often used to compare values within a single table.

Using our example tables for a self join wouldn't make a lot of sense, but let's write the code to see the syntax and the results anyway.

SELECT * FROM users u1
JOIN users u2
ON u1.id = u2.id;

Result:

id	name	id	name
1	John	1	John
2	Harold	2	Harold
3	Lionel	3	Lionel

Conclusion

And that's all about the different types of joins in SQL. I hope this will be useful for you to understand their differences.

Thank you for reading, and see you on another post 👋👋

Bookstore Database Schema

cenan ozen — Wed, 23 Nov 2022 16:42:36 +0000

In this post we will design a schema to be used in a bookstore software or an online store selling books.

books is the first table we are going to create and it will hold information about the books themselves.

Primary fields of the books table that holds direct information about a book are:

title — Title of the book
ISBN_13 — 13 digit ISBN number of the book
publication_date — Date of publication
page_count — Number of pages in the book
edition_number — Edition number

If we want hold the older 10 digit ISBN code for a book we could add an ISBN_10 varchar(10) field.

page_count field holds the number of pages in the book and we have a field to hold edition number. However, if we want to hold page counts in each edition separately, we might want to create a related editions table and keep edition specific information in that table.

Next, we have genres, publishers and languages tables. These tables are connected to the books table using a many-to-one relation. If we want a book to have more than one genre, publisher or language, then we would need to create join tables and have many-to-many relations.

We are going to create an authors table to represent book authors. Since a book can have multiple authors and an author can have multiple books, the relation between books and authors will be many-to-many. We have an author_books join-table for the needed relation.

We have an optional Orders and Customers part that could be useful for an online book shop.

General order information is kept in the orders table, and individual books in an order are kept in the order_items table.

Order items have the following fields:

line_price a field for the books price in this order. We could have a current_price field in the books for any listing or report, but since that price can change in time, we must have a constant price associated with the order.
amount field holds the count of a specific book in an order.

We have the following generic tables related to customers, which are not specifically related to bookstores.

Those are the basic tables that we would need to design a bookstore schema. If we were developing an online store we could add reviews, ratings and similar tables. We have used a similar structure in our movie catalogs schema tutorial.

We came to the end of our post. You can open this sample movie catalog schema in dbmodeller and use it in your own projects. You can click View Samples button on the welcome page in dbmodeller.net to checkout other samples.

Open Schema in DbModeller

Thank you for reading 👋👋

Movie Catalog Database Schema

cenan ozen — Tue, 18 Oct 2022 18:41:59 +0000

Today we are going to create a database schema for a movie catalog. It could be used for a web-site like IMDB or a desktop software to catalog movies.

Our main table to store information about movies is the Movie table.
This table holds the main aspects of the movie like the following:

Title of the movie
Release date
Run time length of the movie as minutes
IMDB id of the movie
MPA Rating of the movie (G, PG, PG-13, R, NC-17)

This table has a reference to languages table which holds the language of the movie.

Next we have the plots, languages and genres tables. These are all colored with the same color because they hold the detail information about a movie.
The plots table has a many-to-one connection to the movies table and one-to-one connection to the languages table. This is because a movie can have multiple plots in different languages. The languages table is referenced by the movies table as we have mentioned above. This is for the language of the movie it self.
We have a many-to-many connection between movies and genres tables. This is because a movie could have multiple genres. There is a movie_genres table to be able to establish the many-to-many connection.

There are many ways to represent people associated with a movie. We could have seperate tables for directors, producers, actors and actresses and these tables could hold that persons details. However we chose to create a single people table to represent any person and have various join-tables to represent the association of the specific person to the movie.

Any person who is not an actor/actress is represented through a record in the people table and a connection through directors or producers to the movies table. Depending on our needs, we could add writers, executive_producers, production_managers and many other similar tables with the same structure as producers and directors tables. A foreign key for people and a foreign key for movies is all that is needed.

To represent the actors in the database, we will do something different than directors and producers. An actor plays a character in a movie. Sometimes a character is played by different actors. For example in X-Men: First Class (2011), James McAvoy plays the 30 year old Charles Xavier role and Laurence Belcher plays the young Xavier role. It is also possible that the same character is portreyed in different movies. Like in the other movies in the series, where Xavier is portraied by Patrick Stewart.

To be able to model this scenario where a character is not specifically tied to a movie, we have characters table which is associated with the movies table through a join-table.

Note: The correct plural form of person is people, although you can name the people table persons if you want to keep the naming consistent.

Finally we have the awards table. This table would hold the list of awards (94th Academy Awards, 79th Golden Globe Awards, Palme d'Or etc). It has name and year fields of the award, however we could add, ceremony_date, ceremony_location, presenter and many other fields if we need them.

The awards table is associated with the movies table through the movie_awards table. The category field would hold the name of the award (Best Picture, Best Actor, Best Supporting Actress etc). won_awards field is a boolean field that represents if the award is won or if it is just a nomination.

We came to the end of our tutorial. You can open this sample movie catalog schema in dbmodeller and use it in your own projects.

Open In DbModeller