Live Chat Software by IXXO |
Knowledgebase
Product Record Rules (SQL)
Posted by David Stanley on 26 January 2012 05:40 PM
|
|
Basically two tables are used for each product entry. The "products" table and the "product_description table". While the "products" table holds all the information about a product, like any settings, the "product_description" table hold only the meta, name and language information of each product. When creating a product you need both tables (make sure the "pid" between "products" and "product_description" match each other and that you choose a valid "language" in "product_description". ------------------------------------------------------------------------ A basic product would need at least the following information : ------------------------------------------------------------------------ TABLE products "pid" => "42" / auto increment when creating a new product "cid" => "hardware" "title" => "your product name" / for backwards compatibility, the actual title is taken from the "product_description" table, not from this table, so you could leave this entry blank => "" "price" => "125.25" TABLE product_description "sn" => "50" / auto increment when creating a new product description record "pid" => "42" / identical to the products "pid" "title" => "your product name in language en" "language" => "en" / any valid language code like "en", "de", "es", etc. ------------------------------------------------------------------------ An additional language description for the above product would look like this ------------------------------------------------------------------------ TABLE product_description "sn" => "51" / auto increment when creating a new product description record "pid" => "42" / identical to the products "pid" "title" => "your product name in language de" "language" => "de" / any valid language code like "en", "de", "es", etc. *********************************************************************** Below is the complete ruleset for the current product records : ------------------------------------------------------------------------ TABLE `products` ------------------------------------------------------------------------ `pid` int(10) unsigned NOT NULL auto_increment, `cid` int(10) unsigned NOT NULL default '0', `manufacturer_id` int(10) unsigned NOT NULL default '0', `is_visible` enum('Yes','No') NOT NULL default 'Yes', `is_hotdeal` enum('Yes','No') NOT NULL default 'No', `is_home` enum('Yes','No') NOT NULL default 'No', `is_taxable` enum('Yes','No') NOT NULL default 'Yes', `is_dollar_days` enum('Yes','No') NOT NULL default 'No', `inventory_control` enum('Yes','No') NOT NULL default 'No', `inventory_rule` enum('Hide','OutOfStock') NOT NULL default 'Hide', `stock` int(10) NOT NULL default '0', `stock_warning` int(10) NOT NULL default '0', `weight` decimal(10,2) unsigned NOT NULL default '0.00', `free_shipping` enum('Yes','No') NOT NULL default 'No', `digital_product` enum('Yes','No') NOT NULL default 'No', `digital_product_file` varchar(255) NOT NULL default '', `price` decimal(20,5) unsigned NOT NULL default '0.00000', `price2` decimal(20,5) unsigned NOT NULL default '0.00000', `price_level_1` decimal(20,5) unsigned NOT NULL default '0.00000', `price_level_2` decimal(20,5) unsigned NOT NULL default '0.00000', `price_level_3` decimal(20,5) unsigned NOT NULL default '0.00000', `shipping_price` decimal(20,5) unsigned NOT NULL default '0.00000', `tax_class_id` int(11) NOT NULL, `tax_rate` decimal(20,5) NOT NULL default '-1.00000', `call_for_price` enum('Yes','No') NOT NULL default 'No', `priority` int(11) NOT NULL default '0', `attributes_count` int(11) NOT NULL default '0', `min_order` int(10) NOT NULL default '1', `max_order` int(10) unsigned NOT NULL default '0', `added` datetime NOT NULL default '0000-00-00 00:00:00', `product_id` varchar(30) NOT NULL default '', `case_pack` int(11) NOT NULL default '-1', `inter_pack` int(11) NOT NULL default '-1', `gift_quantity` int(10) unsigned NOT NULL default '0', `dimension_width` decimal(10,2) NOT NULL default '0.00', `dimension_length` decimal(10,2) NOT NULL default '0.00', `dimension_height` decimal(10,2) NOT NULL default '0.00', `image_location` enum('Local','Web') NOT NULL default 'Local', `image_url` varchar(255) NOT NULL default '', `title` varchar(255) NOT NULL default '', `meta` varchar(255) NOT NULL default '', `meta_title` varchar(255) NOT NULL default '', `overview` text NOT NULL, `description` text NOT NULL, `cg_disable` int(11) NOT NULL, `extra_point` double NOT NULL, `ps_disable` int(2) NOT NULL, `user_req` int(11) NOT NULL, `related_pids` text NOT NULL, `ptype` INT NOT NULL, `imgzoom` VARCHAR( 80 ) NOT NULL , `imgstyle` VARCHAR( 255 ) NOT NULL, `rmaactive` TINYINT( 1 ) NOT NULL DEFAULT '1', `product_type` enum('Normal','Subscribe') NOT NULL default 'Normal', `time_limit` int(11) default '0', `times_limit` int(11) default '0', `compare_cat` int(11) NOT NULL default -1, `reviewactive` TINYINT( 1 ) NOT NULL DEFAULT '1', `normal_price_visible` enum('Yes','No') default 'Yes', PRIMARY KEY (`pid`), KEY `cid` (`cid`), KEY `is_visible` (`is_visible`)) AUTO_INCREMENT=1 ; ------------------------------------------------------------------------ TABLE `product_description` ------------------------------------------------------------------------ `sn` int(11) NOT NULL auto_increment, `pid` int(11) NOT NULL default '0', `title` varchar(255) NOT NULL default '', `meta` varchar(255) NOT NULL default '', `meta_title` varchar(255) NOT NULL default '', `overview` text NOT NULL, `description` text NOT NULL, `language` varchar(20) NOT NULL default '', PRIMARY KEY (`sn`)) AUTO_INCREMENT=1 ; Please note, that these records can change in later releases. There is no guarantee that those records will remain like this forever, although the basic fields probably won't change. | |
|
Comments (0)