Page 1 of 1

MySQL Tutorial Part 1: Design By Polymath and Herefishyfishy

#1 polymath  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 670
  • Joined: 04-April 08

Posted 05 June 2008 - 03:07 PM

Herefishyfishy and Polymath’s tutorial on MySQL Databases Part One: Design

Before we begin, please note the following:
•This tutorial assumes you already have MySQL on your computer. The installation is long, but the processes can be found easily on any Search Engine.
•This tutorial assumes you have no prior knowledge of SQL. This is an introduction ONLY.
•Connecting to MySQL requires special procedures with any server-side scripting language. These procedures can be found on any Search Engine, and in addition, they are often found with installation guides on MySQL, especially using PHP, the most popular for use with MySQL. Watch out for Part 2 of this tutorial, which wil involve using PHP.

Section 1: Why use a database?
Most programming languages allow the user to edit a text or binary file while they are using a program (e.g. saved games). However, these “flat files,” as they are called, have many disadvantages. They are slow, hard to navigate, and all programs have access to them, which could result in two programs using the file at the same time, usually corrupting (and rendering useless) the file. Because of this, many programmers and web developers have switched over to using a Relational Database Management System, or RDBMS. These avoid many of the problems associated with flat files, but only if they are used correctly, which is what this tutorial will teach you.

Section 2: Structure of RDBMS
RDBMS follows a set hierarchy that organizes the files. It starts with a user; with MySQL I believe the default user is admin. Each user has control over several databases, usually one database for each site/application. These databases contain tables of information, which is normally organized by Entry (row). These Entries have several cells of information in that row, which are accessed by a program. For those of you familiar with Object Oriented Programming, the User is the program, the Database is the package or namespace, the Table is the class, the Entry is an object, and the Cell is an attribute of the object (if you didn’t understand it, that’s OK, it’s a comparison to other languages). For visual learners, here’s a diagram of the organization/hierarchy in a RDBMS:

Attached Image
Attached File  database.bmp (684.97K)
Number of downloads: 609


Section 3: What are our tables?

First, as with anything relating to any kind of programming, ask yourself, “What am I trying to accomplish with this?” Our example for today is going to be an online store, such as amazon.com. What are we trying to accomplish, or in other words, what do we need to include in our database? Well, Customers is obvious, and Products is just as simple. Orders is more abstract, but we need to include that, too. So, we should create a table for all of these within our database (for any/all coding go to Section 5).

Section 4: Designing the Database
Next, ask yourself what information should be stored in each table. For our customers, we should ask ourselves, “What information do customers have that we need to store?” Well, customers have names, usernames, passwords, and addresses, among other things, like credit-card numbers (which we will not discuss-end of story). So we could create columns in the table to represent each of these pieces of data (remember, the rows define an individual and his/her data, just like an instantiation/object of a class in OOP). So we could create a table that looks like this:


+--------------+------------+-------------+---------------+
|'Name'''''''''|'Username'''|'Password''''|'Address'''''''|
+--------------+------------+-------------+---------------+
|'Joe Smith''''|'joe_is_''''|'codingrocks'|'1888 Hello Dr.|
|''''''''''''''|'amazing''''|'''''''''''''|'Springfield IL|
+--------------+------------+-------------+---------------+
|'Wolfgang A.''|'Rondo_alla'|'i-love''''''|'1111 History''|
|'Mozart'''''''|'_turka'''''|'-sonatas''''|'Ln Springfield|
|''''''''''''''|''''''''''''|'''''''''''''|'IL''''''''''''|
+--------------+------------+-------------+---------------+
|'Susan B.'''''|'Suffragette|'20th''''''''|'1111 History''|
|'Anthony''''''|'-girl-1920'|'_amendment''|'Ln Springfield|
|''''''''''''''|''''''''''''|'''''''''''''|'IL''''''''''''|
+--------------+------------+-------------+---------------+
|'Joe Smith''''|'coderdude9'|'codingrocks'|'2222 Hi Blvd.'|
|''''''''''''''|'02'''''''''|'''''''''''''|'Las Vegas, NV'|
+--------------+------------+-------------+---------------+


So, which column should we use to identify each individual customer? The identification column is called the Primary Key. Hey, wouldn’t it be a great idea to use their name? No, because there will inevitably be more than one “Joe Smith” or “John Doe” on our site (think of how unreasonable it would be to be cut from DIC just because there is another guy in the middle of Mongolia with your name already registered). As you can see, Entries 1 & 4 are both named Joe Smith. What about their address? Not this either, for Mozart and Susan B. Anthony both live in the same house (pretend they're married-HYPOTHETICALLY). And it would be a big security issue if, say, when you were registering, a message popped up and said, “ERROR: joe_is_amazing already is using the password ‘codingrocks’, please pick another.” We’d have people hacking accounts left and right. So if people can have the same name, password, and address, and it would be a security issue or be unreasonable to deny somebody because there’s another person with this attribute, what can we regulate? Where’s our Primary Key? We can regulate their username (as most sites do), because you need someone’s username and password to log in, and you see people’s username all the time, AND your username is not your real name, so you can change it to any random string you can remember.



Section 5: The Code

So, now you must be asking yourself when you’re actually going to be doing something. Well, we are going to, starting now. First, let’s create a database, for the purposes of our tutorial, we will call this “test.” Open up the MySQL Command Line Client (Start Menu-MySQL for Windows; also called the MySQL monitor) and type: sql -h <hostname> -p and then type in your password on the next line. Once this is done, type in: create database test; This is relatively self explanatory. Then, we are going to create our customers table. To do this we will type (notice this is noncasesensitive):
 use database test;
create table customers (
username CHAR(15) UNIQUE NOT NULL PRIMARY KEY,
password CHAR(15) NOT NULL,
realname CHAR(50) NOT NULL,
address CHAR(75) NOT NULL
);


Alternatively, and what may be easier to OOP folks to remember:
create table test.customers (
username CHAR(15) UNIQUE NOT NULL PRIMARY KEY,
password CHAR(15) NOT NULL,
realname CHAR(50) NOT NULL,
address CHAR(75) NOT NULL
);


This should require minimal description. Basically, in the first one we use the “use” command to tell the MySQL monitor that all following actions deal with our “test” database. In the second we are telling it we want to create a table customers in the database test(notice for this one the test.customers, follow the hierarchy). In Both we are creating a table that is within the test database which has 4 columns, username, 15 characters max which must be unique and is the primary key, password (also 15 character max), realname (50 characters max), and address (100 characters max). None of these columns can ever have a null value (i.e. nothing), so we add NOT NULL to the end of each. This is usual- having null values is bad database design, so most of our columns will have this.

IMPORTANT NOTE: The reasoning behind the length of the address column entries is because of the recent SQL injections. Thus, we are going with 75 to be on the safe side.

To review our work, we just type: SHOW TABLES FROM test;, which should return a column with the heading “Tables_in_test” and 1 row- customers. We can also type in describe test.customers; or use database test; describe customers;, which should return a table showing the columns. This is actually like a table itself, with each column as an entry and the columns in this are each of the actual column’s attributes.


Now to make rows (Insert the first line only if you haven’t already used the use database test; earlier. We are using the use statement in this code because we are typing in customers so many times that it is worth the use statement. Also, use statements are somewhat simpler to some people, too):

use database test;
 insert into customers values {“Joe Smith”, “joe_is_amazing”, “codingrocks”, “1888 Hello Dr. Springfield IL”};

insert into customers values {“Wolfgang A. Mozart”, “rondo_alla_turka”, “i_love_sonatas”, “1111 History Ln., Springfield, IL”};

insert into customers values {“Susan B. Anthony”, “Suffragette-girl-1920”, “20th_amendment”, “1111 History Ln., Springfield, IL”};

insert into customers values {“Joe Smith”, “coderdude902”, “codingrocks”, “2222 Hi Blvd, Las Vegas, NV”};


This is self-explanatory. You can also use the INSERT statement to add some, but not all, values to a table, as in insert into customers (NAME, USERNAME) values {“Samuel Clements”, “mark_twain”}

To display the table to make sure we did things right, we use a SELECT statement (we will come back to this later).
select * from customers;


This shows all the data in the entire table. Alternatively, we could type describe customers; but this will only show information about the columns.

To the next table: the Products. Note that as we go along, we hope you’ll need less and less description. For this, our primary key will be the Product Number. We’ll also want to store the price of the product. We’ll finally have a column for the name and for the description of the product. The Product Number is going to be an integer type, but it’s also going to be auto-incremented, so that it auto-generates a product id for each time an entry is created. The Price is going to be a float type, which indicates that it is a decimal. In the example it will have two numbers following the decimal point and will go up to 9,999,999.99 as our maximum price. Our code for this is as follows:

 use database test;
create table products (
ProductNumber INT UNIQUE NOT NULL AUTO INCREMENT PRIMARY KEY,
Price FLOAT(7, 2) NOT NULL,
Name CHAR(25) NOT NULL,
Description CHAR(75) NOT NULL
);



To insert some products into our table (see if you can figure out what will go into the columns—Note, we don’t need to input ProductNumber as it is a auto increment):

use database test;
insert into products (Price, Name, Description) values {1000.00, “TV”, “A Standard Hi-Def TV”};

insert into products (Price, Name, Description) values {499.99, “PS3-60G”, “The 60 GB Sony PlayStation 3”};

insert into products (Price, Name, Description) values {999999.99, “Jet”, “A private jet for one cent less than a million”};




Now, the final table. This will be the orders table. First ask yourself, “What will I use to keep track of the orders?” We will make an OrderID column for this. Next, we’ll want to know who ordered it. We’ll use what’s called a foreign key. This will allow us to link back to the Primary Key of the customers table. We also need to know where to ship it to. That’s actually false, for this table. When we are creating a script to connect to MySQL, to find the destination, we’ll find the customer who ordered it in the username column and then search the customers table(We can only use foreign keys on primary keys). The same principle is true for the amount due—We’ll just look it up in the products table with our implementation script. However, if the user specifies to ship to another address, we’ll have a special column just for that. Finally, we’ll need to know the product we’re shipping (and guess what, another foreign key). Our Code for our last table-Remember to look for the Foreign Keys!:

 use database test;
create table orders (
OrderID INT UNIQUE NOT NULL AUTO INCREMENT PRIMARY KEY,
Customer CHAR(15) REFERENCES customers(username) NOT NULL,
Product INT REFERENCES products(ProductNumber) NOT NULL,
SpecialAddress CHAR(75)
);



One last note: Notice that we didn’t use NOT NULL for SpecialAddress. This can be null, and if it is, then we’ll look it up in the customers table with our script.

To add entries:

use database test;
insert into orders (Customer, Product) values (“coderdude902”, 2210);
insert into orders (Customer, Product, SpecialAddress) values (“joe_is_amazing”, 1120, “1111 Hi Dr. Oakton KN 33040”);



That’s all for designing our databases. Stay tuned for Part II: Implementation. If you have any questions feel free to ask! The reference section follows. Note that the best reference of all is MySQL itself (type help contents), and you can also try the MySQL Website.



Section 6: Reference

Here is a summary of variable types:

Integers:

-TINYINT (-127 to 128, or 0 to 255 if an UNSIGNED TINYINT)

-BIT (same as TINYINT)

-BOOL (same as TINYINT, not really a Boolean)

-SMALLINT (-32767 to 32768 or 0 to 65535 if an UNSIGNED SMALLINT)

-MEDIUMINT (-223 to 223-1 or 0 to 224-1 if an UNSIGNED MEDIUMINT)

-INT (or INTEGER) (-231 to 231-1 or 0 to 232-1 if an UNSIGNED INT/INTEGER)

-BIGINT (-263 to 263-1 or 0 to 264-1 if an UNSIGNED BIGINT- notice these numbers are much too large than you will ever want to use UNLESS you want to store very large numbers, like the amount of dollars in the U.S. National Debt. BIGINTs take 8 bytes of data, twice as much as INTs and FOUR TIMES as much as SMALLINTs.)

Floating-point numbers

-FLOAT(x,y) (will store a number with a max of x digits and a max of y digits after the decimal point. Notice that when you are creating a table, you must supply x and y yourself.)

-DOUBLE(x,y) (same as FLOAT(x,y), but with a greater range)

-DOUBLE PRECISION(x,y) (same as DOUBLE(x,y))

-REAL(x,y) (same as DOUBLE(x,y))

Dates and Times

-DATETIME (stores both a date and time, from the start of Jan 1 of the year 1000 to the end of December 31 in the year 9999. You cannot store B.C. dates with it, obviously.)

-TIMESTAMP(x) (Stores a UNIX timestamp. This is the number of seconds since January 1, 1970. x denotes the display format. If you put 14 for x, you will get the 4-digit year, 2-digit month, day, hour, minute, and second. If you put 8, you will get the 4-digit year, 2-digit month, and 2-digit day. For the purposes of our database, we will use 14, because it gives the most detail.

Strings, Blobs, and Text (Yes, there IS a type called a BLOB.)

-CHAR(x) (A regular string with a length of x. If the data entered into the database has a length of less than x, the rest will be padded with spaces. Luckily, when you view the database, the spaces will not show. CHARs are faster than VARCHARs.)

-VARCHAR(x) (A regular string with length less than or equal to x. If the string entered is shorter than x characters, the database will accept it anyways, without spaces at the end. Since these do not require as much storage as a CHAR, they are used by many programmers; however, CHARs are no harder to use and they are faster. If you are low on storage, go ahead and change all your CHARs to VARCHARs, though.)

-BLOB (Not what it sounds like! A BLOB is a Binary Large Object. It can store any file, not just text. Also, there are TINYBLOBs, MEDIUMBLOBs, and LONGBLOBs.)

-TEXT (A noncasesensitive BLOB. You should NEVER use TEXT, in my opinion.)

Enums

-ENUM(value1, value2, value3, value4…) (Cells of columns of this type can hold any value that is listed in the ENUM. ENUMs can hold up to 65,535 values, but I don’t know who would waste their time typing that many things into a computer.)

-SET(value1, value2, value3, value4…) (Cells of columns of this type can hold any subset of the values listed in the SET. Can hold up to 31 values.)

This post has been edited by polymath: 07 June 2008 - 08:23 AM


Is This A Good Question/Topic? 4
  • +

Replies To: MySQL Tutorial Part 1: Design

#2 polymath  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 670
  • Joined: 04-April 08

Posted 07 June 2008 - 08:26 AM

PLEASE NOTE THAT QUOTES SOMETIMES DO NOT SEEM TO WORK OUTISIDE OF CODE BLOCKS .

When reading, If you see a ' please replace ' with the appropriate quote (single or double) or an apostrophe in your mind.

Thank you.

This post has been edited by polymath: 07 June 2008 - 08:28 AM

Was This Post Helpful? 1
  • +
  • -

#3 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1875
  • View blog
  • Posts: 20,282
  • Joined: 17-March 01

Posted 07 June 2008 - 08:33 AM

Great tutorial, I'll be sure you both get your kudos. Thanks!
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 07 June 2008 - 08:35 AM

Excellent tutorial!
Was This Post Helpful? 0
  • +
  • -

#5 DappaDan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 18-June 08

Posted 20 June 2008 - 08:13 AM

I have decided to interpret the MySQL language. As a starter, I can honeslty say this tutorials is brilliant!

Thanks a heap!
Was This Post Helpful? 0
  • +
  • -

#6 polymath  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 670
  • Joined: 04-April 08

Posted 21 June 2008 - 06:47 AM

Thanks all of you. :)
Was This Post Helpful? 0
  • +
  • -

#7 chowdawg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 28-July 09

Posted 10 September 2009 - 10:12 AM

To set a Foreign Key, alternatively, you can use the syntax:

FOREIGN KEY (Customer) REFERENCES customers(username);


I like doing it this way because it gives a clear sense of what is a Foreign Key instead of having to search for the REFERENCES.
Was This Post Helpful? 0
  • +
  • -

#8 BuddhaBang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 17-September 09

Posted 17 September 2009 - 05:28 AM

Indeed nice tutorial. Well done both.
Was This Post Helpful? 0
  • +
  • -

#9 chinee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-March 10

Posted 09 March 2010 - 08:57 AM

very helpful thank you
Was This Post Helpful? 0
  • +
  • -

#10 sfw  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 27
  • Joined: 24-April 08

Posted 10 March 2010 - 05:47 PM

thank you very much. very easy to follow and lots of information.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1