Home arrow PHP arrow Page 2 - Creating a Searchable Inventory System: Setting Up Your Database and User Interface

A Look at the Database - PHP

Businesses that hope to sell items through their website need a searchable inventory system for the web. This article, the first in a four-part series, will start you on your way to building one.

  1. Creating a Searchable Inventory System: Setting Up Your Database and User Interface
  2. A Look at the Database
  3. Defining the Visual Layout
  4. How Do We Begin?
By: Brian Vaughn
Rating: starstarstarstarstar / 25
November 08, 2005

print this article



The most fundamental element of any online inventory system, and perhaps the most important, is the database itself. The database contains the information that drives the rest of the site (such as product specs, availability, prices, and so on). Its layout in turn affects the usability of the information it contains. A properly designed database can be utilized in many ways, while a poorly designed database can be almost worthless.

We will begin our searchable inventory project by taking a look at the database structure itself. For our example project we will create an inventory system of computer hardware components. For simplicity’s sake, we will not store extensive information as would be used in a typical online storefront, but will store only the basic details (name, a brief description, product manufacturer, and part type). After all, the concepts we will be learning are not limited to computer parts, but rather deal with the overall techniques we will be applying to search through those components.

Our database will comprise four tables, and may be created by running the following SQL queries:

-- holds category info: "motherboards", "memory", etc.
CREATE TABLE `pc_categories` (
  `id` int(2) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`id`)

-- holds manufacturer info: "ASUS", "MSI", etc.
CREATE TABLE `pc_manufacturers` (
  `id` int(3) unsigned zerofill NOT NULL auto_increment,
  `category_id` int(2) unsigned zerofill NOT NULL default '00',
  `name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`)

-- holds part info
CREATE TABLE `pc_parts` (
  `id` int(5) unsigned zerofill NOT NULL auto_increment,
  `sub_category_id` int(2) unsigned zerofill NOT NULL default
  `manufacturer_id` int(3) unsigned zerofill NOT NULL default
  `name` varchar(35) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `category_id` (`sub_category_id`,`manufacturer_id`)

-- holds sub category info: "AMD motherboards", "Intel
CREATE TABLE `pc_sub_categories` (
  `id` int(3) unsigned zerofill NOT NULL auto_increment,
  `category_id` int(1) unsigned zerofill NOT NULL default '0',
  `name` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`)

As you can see, each table contains a specific yet related type of information. This is referred to as “relational database design.” Each table contains a primary key field, which is used to uniquely identify each record found within. These primary key fields in turn are used to link each table’s records to records contained within other related tables. For example, “pc_categories” is used to contain major computer hardware categories such as “memory” and “motherboards.” Its primary key field is then used to establish a link to related records found within “pc_sub_categories,” such as “desktop memory” and “notebook memory,” or “AMD compatible motherboards” and “Intel compatible motherboards.”

The relationship between our four database tables is as follows:

Put another way, each hardware category contains one or more sub-categories, as well as one or more manufacturers. In turn, each computer part belongs to a sub-category and a manufacturer. In this way, all four tables in our database are related to each other despite the fact that they contain different types of data.

Another goal in proper relational database design is to reduce repetition in the storage of data. For instance, rather than storing the name of a manufacturer over and over again within our main “pc_parts” table, we simply store it once within the “manufacturers” table, and then refer to it using its primary key value. That way instead of storing a string of text we are simply storing a small integer value. This will result in a smaller overall database size -- an important element to consider when dealing with large bodies of information.

If you would like a list of records to pre-populate your SQL database for testing purposes, you may obtain one here: http://portfolio.boynamedbri.com/devshed/search/part_1/data.sql. A link will also be provided to a finished version of the application we will be developing at the end of this article.

>>> More PHP Articles          >>> More By Brian Vaughn

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: