Added Update #1. Please check at the end of the question. Thanks.
Friends,
I am designing a Product Listing with categories and filters tied to the categories. I currently have a database schema with static categories. I have to make them dynamic. I couldn't find out the right schema to make it dynamic, so I hard code the schema in the form. My detailed work is as follows.
MySQL Stuff
The main products table is the index of all the products and each category of products have separate fields. For eg., consider a bike and a TV. These two products share common fields that are in the product table:
- Product ID
- Name
- Price
- Photo
- Category
And when it comes to the category, it has some extra fields. So, bike comes in the automobiles category, where it has fields:
- Mileage
- Engine Type
- Seater
- Fuel
For the TV product, it comes in its own category, TV, where the fields are:
- Dimensions
- Video Type
- Response Time
- Input Options
Currently my database structure is as follows:
DESC `Products`;
+------------+--------------+------+-----+---------+----------------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+------------+--------------+------+-----+---------+----------------+
| Product ID | int(11) | NO | PRI | (null) | auto_increment |
| Name | varchar(255) | YES | | (null) | |
| Price | int(11) | YES | | (null) | |
| Photo | varchar(255) | YES | | (null) | |
| Category | int(11) | YES | | (null) | |
+------------+--------------+------+-----+---------+----------------+
And the category fields are as:
DESC `television`;
+---------------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+---------------+--------------+------+-----+---------+-------+
| Product ID | int(11) | YES | | (null) | |
| Dimensions | varchar(25) | YES | | (null) | |
| Video Type | varchar(255) | YES | | (null) | |
| Response Time | varchar(25) | YES | | (null) | |
| Input Options | text | YES | | (null) | |
+---------------+--------------+------+-----+---------+-------+
DESC `automobiles`;
+-------------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------------+--------------+------+-----+---------+-------+
| Product ID | int(11) | YES | | (null) | |
| Mileage | int(11) | YES | | (null) | |
| Engine Type | varchar(255) | YES | | (null) | |
| Seater | int(11) | YES | | (null) | |
| Fuel | varchar(255) | YES | | (null) | |
+-------------+--------------+------+-----+---------+-------+
And when I insert data to the database, I use this way:
INSERT INTO `television`
(`Product ID`, `Dimensions`, `Video Type`, `Response Time`, `Input Options`)
VALUES
(1, 100, 'hd', 2, 'hd');
INSERT INTO `automobiles`
(`Product ID`, `Mileage`, `Engine Type`, `Seater`, `Fuel`)
VALUES
(1, 100, 'hd', 2, 'hd');
HTML / PHP
Seems to be fine for a static set of categories, where I use a simple form like the following.
Television
<form action="new.php">
<ul>
<li>
<label>Name</label>
<input type="text" />
</li>
<li>
<label>Price</label>
<input type="text" />
</li>
<li>
<label>Photo</label>
<input type="text" />
</li>
<li>
<label>Category</label>
<input type="text" />
</li>
<li>
<label>Dimensions</label>
<input type="text" />
</li>
<li>
<label>Video Type</label>
<input type="text" />
</li>
<li>
<label>Response Time</label>
<input type="text" />
</li>
<li>
<label>Input Options</label>
<input type="text" />
</li>
<li>
<input type="submit" />
</li>
</ul>
</form>
Automobile
<form action="new.php">
<ul>
<li>
<label>Name</label>
<input type="text" />
</li>
<li>
<label>Price</label>
<input type="text" />
</li>
<li>
<label>Photo</label>
<input type="text" />
</li>
<li>
<label>Category</label>
<input type="text" />
</li>
<li>
<label>Mileage</label>
<input type="text" />
</li>
<li>
<label>Engine Type</label>
<input type="text" />
</li>
<li>
<label>Seater</label>
<input type="text" />
</li>
<li>
<label>Fuel</label>
<input type="text" />
</li>
<li>
<input type="submit" />
</li>
</ul>
</form>
My Problem
Now the form data is simple. I just use the structure of table to insert into the database. The problem now I face is, what if the categories are supposed to be dynamic? How am I supposed to change my database schema to make it dynamic?
Okay, let me define what's my view of dynamic. When the admin wants to add new categories, what I currently do is, create new forms for the categories, go to phpMyAdmin and add a new table and again the same insert queries and new files will be added. I want to do this via an admin panel using PHP alone. Is this feasible? What kind of schema changes should I make?
Update #1
First of all. Thanks! Now I have improvised my table this way. Now I have four tables.
- Products - Index of all the products.
- Categories - For name-sake? Includes the category name and its parent.
- Attributes - Includes the attribute names for the category.
- Association - The association of the
Product ID,Attribute ID, and its value.
Now, well, I am working on. This looks promising. Will wait for answers, at the same time, update my work too! :) Thanks.
