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 '00', `manufacturer_id` int(3) unsigned zerofill NOT NULL default '000', `name` varchar(35) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`id`), KEY `category_id` (`sub_category_id`,`manufacturer_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.