Building a Store Application With MySQL++ and C/C++

Building a Store Application With MySQL++ and C/C++ - Preparing the database
(Page 2 of 7 )

First off, we need to create our MySQL database and tables. For the purposes of this tutorial we will keep things simple. An actual store database is bound to be more complex, however this one will provide you with the necessary foundation to build upon.

Load the MySQL console application and create a new database called Article1:

create database Article1;

use Article1;

We will divide our products into two generic categories: foodstuff and equipment. The category table will have 4 fields:

1. ID: Will serve as our primary key.
2. Category: Category into which different products may fall into.
3. CategoryID: The ID that all the products in that category will share.
4. Isle: The isle in which the various products in this category might be found.

In our simple example the table will have look like this:



Here's the SQL that you should enter at the MySQL prompt:

create table Category

(

ID int unsigned not null auto_increment primary key,

Category varchar(15) not null,

CategoryID int unsigned not null,

Isle int unsigned not null,

unique(Category, CategoryID)

);

insert into category(ID, Category, CategoryId, Isle) values(null, 'Foodstuff', 101, 5);

insert into category(ID, Category, CategoryID, Isle) values(null, 'Equipment', 102, 7);

Now that the category table exists and contains some records, we'll go on to create the products table. For each product in the store we would like to know the product's name, serial, quantity and price. Moreover, we would like to know what category each product belongs to. Here’s how our initial table will look:



... and here's the SQL code:

create table Products

(

ID int unsigned not null auto_increment primary key,

ParentID int unsigned not null,

Name varchar(20) not null,

Quantity int unsigned not null,

Serial int not null,

Price float(4,2) not null,

index(ID),

unique(Name)

);

insert into Products values(1, 101, 'Bread', 12, 1015435, 0.99);

insert into Products values(2, 101, 'Cookies', 7, 1015436, 2.99);

insert into Products values(3, 102, 'Shampoo', 34, 1025437, 5.47);

insert into Products values(4, 101, 'Milk', 15, 1015438, 1.96);

Now that our database exists and contains some data, we're ready to move onto the next step. As we progress we will change the database by adding and removing categories and products. That, however, will be done through the MySQL C API, which we will be the topic of the next section.
blog comments powered by Disqus