Designing a Database Structure

In this post, we’ll be covering the details on how we can go about designing a database that fulfils our needs. For that, we’ll conceive a scenario, and design accordingly.

Shopping cart solutions already exist in the world, but for us, we need to build it ourselves. WooCommerce is pretty great when integrated with WordPress, but other platforms? Not so much. We could rely on others to build these things for us, but that means time, and goodwill, and other things that we won’t likely have right out of the gate. So roll up your sleeves, because it’s about to get a little messy.

Product, meet Category. Category, you don’t need to know Brand.

Entity Relation Diagrams (ERDs) are an essential tool. Seriously. If you charge in without thinking and start building the database without considering how it’s gonna be used, you’ll have a bad time.

So where do you begin? Let’s think about how it works in the retail world:

1. A user comes to the shop, looking to shop
2. They go around the aisles looking for the category of item they need
3. They go down the aisle they want to find the product they want
4. They take the item, and head to the registers
5. They pay for the item, and get a receipt

In the course of developing the application, we’ll need to build a database that accounts for this type of use case. So thinking about the objects that are interacting, we have:

• Users, that make
• Orders, which consist of
• Products, that have a
• Brand and
• Category

We have the cart to think about, but that’s a problem for another time. Let’s focus on the stock side of things, which consist of:

• Products
• Brands
• Categories

Now, we break it down further, and find attributes of these aspects in order to construct our relationships. For a shop to be useful, a product has these attributes:

• The name
• The price
• A description of the product
• What brand owns this product
• The category it belongs to
• The barcode data for scanning it at the register

This doesn’t seem useful, but knowing this, we can infer these things:

• One brand can have many products (One to Many)
• Many products can belong to many categories (Many to Many)

And we have the details now to which we can construct an ERD. Here’s what I came up with:

We now know clearly what we’re after in our database, and the relationships that are defined in it. Let’s get building.

Hello my darling, hello mySQL

When you’re doing a group project, you’ll typically define the structure of the database through a series of migrations, that basically define the working database for an application at a given point, and how to create those changes, and revert them. Combined with version control, it lets you collaborate work on the application without worrying that someone is on the wrong version of that database.

In this case however, we’re young and single, so let’s just write all this out in a single SQL file. You’ll need to connect to the database and all that fluff, and then we can use Data Definition Language to build the skeleton. So for the products table, the SQL to create it might look like this:

Because we don’t have the other tables created, we can’t yet establish any foreign key constraints on the table―for example, we can’t link the brand ID of the products table to the ID field in table brand. So let’s create the associated tables now:

Then, we can create the linking relationship between the brands and the products:

What are we looking at here? We’re looking at an alternation to an existing table (products), and we create a foreign key constraint called FK_products_brands, that references the id column of the brands table. If a record is deleted from brands, the product item that has it gets deleted, and if a brand ID happens to get changed in a mysterious way, the brand_id will update to accommodate for that.

Now, a problem shows up, in that we have a many to many relationship between products and categories. A foreign key constraint is not going to cleanly let us define multiple categories to a product.

What we need is a linking table―a go between person that keeps track of all the products and the brands they’re linked to. It’ll look something like this:

You’ll note that there isn’t a single ID column for that linking table, and that’s okay. We can create what’s known as a composite key using the product ID and the category ID, and together they make possible this many to many relationship. Let’s create that table now:

And then, we can create the constraints to link them all together:

And with that, our initial database is complete!