Mysql Database Design A Complex Shopping Catalogue Part

Question

Hello All.

I want to make an anouncement system. But i messed the database design part.

The system will have unlimited categories and their items so every category will has strange properties

for example car has a model, trade, manufacture date, color, gear type. . . and more

another example Real Estate has a door count, gardened, dublex triplex type, how many bath, how many kitchen

another one can be electronic, it has megapixel, gigabyte, warrantie, resolution, dimensions, shipment . . .

so how i can collect these datas together maybe some objects will have similar properties for example most of items must have a manufacter date so how i can use the datas ? How can be the schema ?

Sercan Virlan

Answer 1

Mind you, this is a very vague question, so I am trying my best to answer :)

Note: There are many better ways to do this, and I could question WHY you are doing it this way, but the below method should accomplish what you are looking for!

Here are 3 tables:

CREATE TABLE `object` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`category` INTEGER NOT NULL ,
`name` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
);

CREATE TABLE `properties` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`objectid` INTEGER NOT NULL ,
`property_key` VARCHAR(100) NOT NULL ,
`property_value` MEDIUMTEXT NOT NULL ,
PRIMARY KEY (`id`)
);

CREATE TABLE `category` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
);

Now lets add some pretend data into them:

INSERT INTO category VALUES ("","CAR");
INSERT INTO category VALUES ("","REALESTATE");
INSERT INTO category VALUES ("","ELECTRONIC");
INSERT INTO object VALUES ("",1,"98 CAMERO");
INSERT INTO object VALUES ("",1,"06 PRIUS");
INSERT INTO object VALUES ("",2,"A House Someplace");
INSERT INTO object VALUES ("",3,"iPod Touch");
INSERT INTO object VALUES ("",3,"iPhone");
INSERT INTO object VALUES ("",3,"Zune");
INSERT INTO properties VALUES ("",1,"color","red");
INSERT INTO properties VALUES ("",1,"doors","4");
INSERT INTO properties VALUES ("",1,"engine","v6");
INSERT INTO properties VALUES ("",2,"engine","electric");
INSERT INTO properties VALUES ("",2,"doors","4");
INSERT INTO properties VALUES ("",2,"color","blue");
INSERT INTO properties VALUES ("",6,"video-playback","true");
INSERT INTO properties VALUES ("",4,"video-playback","true");
INSERT INTO properties VALUES ("",5,"video-playback","true");
INSERT INTO properties VALUES ("",6,"manufacturer","microsoft");
INSERT INTO properties VALUES ("",5,"manufacturer","apple");
INSERT INTO properties VALUES ("",4,"manufacturer","apple");

Now, here is what our data should look like.

mysql> select * from object;
+----+----------+-------------------+
| id | category | name              |
+----+----------+-------------------+
|  1 |        1 | 98 CAMERO         | 
|  2 |        1 | 06 PRIUS          | 
|  3 |        2 | A House Someplace | 
|  4 |        3 | iPod Touch        | 
|  5 |        3 | iPhone            | 
|  6 |        3 | Zune              | 
+----+----------+-------------------+
6 rows in set (0.00 sec)

mysql> select * from category;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | CAR         | 
|  2 | REALESTATE  | 
|  3 | ELECTRONICS | 
+----+-------------+
3 rows in set (0.00 sec)

mysql> select * from properties;
+----+----------+----------------+----------------+
| id | objectid | property_key   | property_value |
+----+----------+----------------+----------------+
|  1 |        1 | color          | red            | 
|  2 |        1 | doors          | 4              | 
|  3 |        1 | engine         | v6             | 
|  4 |        2 | engine         | electric       | 
|  5 |        2 | doors          | 4              | 
|  6 |        2 | color          | blue           | 
|  7 |        6 | video-playback | true           | 
|  8 |        4 | video-playback | true           | 
|  9 |        5 | video-playback | true           | 
| 10 |        6 | manufacturer   | microsoft      | 
| 11 |        5 | manufacturer   | apple          | 
| 12 |        4 | manufacturer   | apple          | 
+----+----------+----------------+----------------+
12 rows in set (0.00 sec)

You can add an unlimited number of categories, an unlimited number of properties, and an unlimited number of objects all into these tables.

Now to join them ALL together:

mysql> SELECT * FROM object 
    ->   LEFT JOIN category ON object.category = category.id
    ->   LEFT JOIN properties ON properties.objectid = object.id;
+----+----------+-------------------+------+-------------+------+----------+----------------+----------------+
| id | category | name              | id   | name        | id   | objectid | property_key   | property_value |
+----+----------+-------------------+------+-------------+------+----------+----------------+----------------+
|  1 |        1 | 98 CAMERO         |    1 | CAR         |    1 |        1 | color          | red            | 
|  1 |        1 | 98 CAMERO         |    1 | CAR         |    2 |        1 | doors          | 4              | 
|  1 |        1 | 98 CAMERO         |    1 | CAR         |    3 |        1 | engine         | v6             | 
|  2 |        1 | 06 PRIUS          |    1 | CAR         |    4 |        2 | engine         | electric       | 
|  2 |        1 | 06 PRIUS          |    1 | CAR         |    5 |        2 | doors          | 4              | 
|  2 |        1 | 06 PRIUS          |    1 | CAR         |    6 |        2 | color          | blue           | 
|  3 |        2 | A House Someplace |    2 | REALESTATE  | NULL |     NULL | NULL           | NULL           | 
|  4 |        3 | iPod Touch        |    3 | ELECTRONICS |    8 |        4 | video-playback | true           | 
|  4 |        3 | iPod Touch        |    3 | ELECTRONICS |   12 |        4 | manufacturer   | apple          | 
|  5 |        3 | iPhone            |    3 | ELECTRONICS |    9 |        5 | video-playback | true           | 
|  5 |        3 | iPhone            |    3 | ELECTRONICS |   11 |        5 | manufacturer   | apple          | 
|  6 |        3 | Zune              |    3 | ELECTRONICS |    7 |        6 | video-playback | true           | 
|  6 |        3 | Zune              |    3 | ELECTRONICS |   10 |        6 | manufacturer   | microsoft      | 
+----+----------+-------------------+------+-------------+------+----------+----------------+----------------+
13 rows in set (0.00 sec)

I hope this is what you are looking for, its an infinitely scalable solution, though if the database becomes incredibly taxed with a heavy workload, it could slow down, just due to its design.