Introduction to SQL Commands

This lesson will teach the basic concepts behind entities and relationships along with hands-on SQL commands and aggregate functions. We’ll also go over the primary key, foreign key, and various operators, including Like, AND, and OR.

CRMS

We’ve started to design the database for a basic customer relationship management system (CRMS). Below is a picture of the tables this database is going to have alongside the relationships.

Basic details of the tables

We’ll start with a recap of the entity relationship diagram (ERD) above. Then, we’ll discuss some rules about the columns that may not be obvious from the diagram.

We see four tables.

• customers
• orders
• products
• order_items

The customers table

• The customers table has an id column, which is also going to be the primary key of the table. That means each customer will have a unique id value.

• This table has a name column. The entries aren’t unique. Two customers may have the same name. It’s a mandatory column because we can’t have a customer without a name.

The orders table

• This table also has an idcolumn. This is the primary key of the table in that each order will have a unique id.
• It has a customer_id column. This will be a reference to a corresponding row in the customers table, and it’ll indicate which customer the order belongs to. We call this a foreign key because it corresponds to the primary key (id) of another table (customers). The customer_id is mandatory.
• It has an order_number column. This is a string-type column that’s mandatory, and the entry should be unique.
• It has an ordered_at column. This is a time stamp and is mandatory. It records when the customer has issued an order. If it’s not given at order creation, it’ll automatically take the current date time stamp.
• There’s a relationship between customers and orders. One customer can have many orders referencing them, so, the relationship is one-to-many. Also, for every one order, we can find one customer the order belongs to.

The products table

• This table has an id column, which is the primary key.

• It has a name column as well, which is a mandatory string-type column. No two products can have the same name.

The order_items table

• This table has an id column, which is the primary key of the table.

• It has a reference to the instance of orders the order_items belongs to. This is the order_id column. It’s a foreign key.

• There’s a one-to-many relationship between orders and order_items.

• This table has a reference to the product the order item refers to. The reference is the column product_id, which also acts as a foreign key. This is mandatory because we can’t have an order item without reference to an instance of products.

• There is a one-to-many relationship between products and order_items.

• There’s a business rule that says that we can’t have the same product twice in the same order. We use the quantity field to allow for many instances of the same product to exist in the same order. The quantity is an integer type column. It’s mandatory and has the default value 1.

• The order_items table is there to help model the properties of the many-to-many relationship between orders and products. As we said in the previous chapter, a many-to-many relationship is modeled using a table in the middle. In our case, this table is order_items, and we end up having two one-to-many relationships. This is because one order can have many order items, and one product can have many order items.

There are a lot of details surrounding the data model even for this simple CRMS, and we haven’t listed them all.