 |
|
|
|
MySQL PHP Web Database Tutorial: Getting Started
Getting started with a database means first figuring out what you want to do. Our sample project in this tutorial is
building a database which manages links to sites. I want to be able to add, edit
and remove links in the database, as well as display links from the database.
I want the links to be categorized so I can display groups of links, instead
of all links at once.
I want to record the following data for each link in the database:
- Name of site
- URL of site
- Description of site
- Link Category
Each of these will become one column in a table in the database. If you are not familiar with
databases, a table can be thought of somewhat as a spreadsheet in Excel. The columns in a spreadsheet
relate to the columns in a database. Each row in the spreadsheet would be considered a record (or row)
in the database. There also can be multiple spreadsheets in a single Excel file, as there can be multiple
tables in one database.
This application could use two tables, one table to store all of the categories and another table
to store all the links, with a link back to the categories table. Because this is a beginner tutorial,
I keep it simple by using just one table to store the info.
|
Advanced Topic
Two tables is the better way to create this application. The second
table would store all the category names seperately with a unique key. The
unique key is just a number for each category, usually starting at 1 for the first category, 2 for the
next, and so on. A record in the links table, which are the individual links, would also store this
category number with the link. This number is then used to refer back to the category table to get
the category name when needed. It is a little more complicated but it is a much better way
to create the database.
The benefits to using multiple tables is you get faster queries, and it is easier to manage the
data. Some examples of the benefits of multiple tables:
- Faster query when selecting the list of all categories because you are querying a
smaller set of data.
- Faster queries when selecting links by category, because the it is faster to select, group and
sort numbers much faster then strings.
- Easier to manage because you can change the category names seperately from the links, since they
only store the numbers as reference to the category name.
- Easier to enter data by category because you can pull the category name from the table
and display it in a select form element instead of requiring the use to type it in.
Using multiple tables (or databases) and relating their data to each other using keys is referred to
as relational databases. Read more about relational database design from the related links below.
|
Moving on, I know what the application should do, and what data will be stored in the database.
The next step is to create the database.
Tutorial Sections
Related Links:
Databases from Scratch — a good introduction to
simple and relational databases design, and to databases in general.
Introduction to Relational Database Design — a slightly
more technical look at relational databases and how they are used. Uses mSQL. Knowledge of SQL is
a plus for reading this one.
|
