[thelist] Re: Database schema

[David Siedband]

I think it would make sense to break up the products table a bit.  Any 
time when you find yourself with a table where you would have a large 
amount of fields that are not getting used, it's a good candidate for 
breaking into more specificized tables that get joined to the original.  
This is a clue that the table is decribing multiple entity types, which 
is usually suboptimal.  I would suggest putting the printer and scanner 
specific fields in a common table though to accomidate 
printer/scaner/fax devices.

--
Dave




Cosmin G wrote:

>Well, I think the best thing I can do is show everyone the .sql file, so
>here goes:
>
>
>#
># Table structure for table `product`
>#
>
>DROP TABLE IF EXISTS `product`;
>CREATE TABLE `product` (
>  `prod_id` int(11) NOT NULL auto_increment,
>  `cat_id` int(11) NOT NULL default '0',
>  `manuf_id` int(11) NOT NULL default '0',
>  `name` varchar(50) NOT NULL default '',
>  `description` text NOT NULL,
>  `state` smallint(6) NOT NULL default '0',
>  `picture` varchar(255) NOT NULL default '',
>  `price` int(11) NOT NULL default '0',
>  `warranty` varchar(5) NOT NULL default '',
>  `model` varchar(45) NOT NULL default '',
>  `code_name` varchar(45) NOT NULL default '',
>  `mb_ob` set('audio','video','LAN','modem') NOT NULL default '',
>  `cpu_socket` varchar(15) NOT NULL default '',
>  `cpu_freq` smallint(7) NOT NULL default '0',
>  `cpu_fsb` smallint(6) NOT NULL default '0',
>  `cpu_cache_l2` varchar(15) NOT NULL default '',
>  `mb_chip` varchar(30) NOT NULL default '',
>  `mb_format` varchar(5) NOT NULL default '',
>  `mb_socket` varchar(50) NOT NULL default '',
>  `mb_fsb` varchar(10) NOT NULL default '',
>  `mb_cpu` varchar(20) NOT NULL default '',
>  `mb_mem` varchar(12) NOT NULL default '',
>  `mb_slots` tinyint(2) NOT NULL default '0',
>  `mb_hdd` varchar(20) NOT NULL default '',
>  `mb_vid_mode` varchar(15) NOT NULL default '',
>  `hd_cap` varchar(15) NOT NULL default '',
>  `hd_rpm` varchar(10) NOT NULL default '',
>  `hd_interf` varchar(20) NOT NULL default '',
>  `hd_access_time` tinyint(5) NOT NULL default '0',
>  `hd_mem` tinyint(2) NOT NULL default '0',
>  `mem_late` varchar(15) NOT NULL default '',
>  `mem_tip` varchar(15) NOT NULL default '',
>  `mem_capa` varchar(15) NOT NULL default '',
>  `mem_freq` varchar(15) NOT NULL default '',
>  `speaker_model` varchar(20) NOT NULL default '',
>  `speaker_type` varchar(8) NOT NULL default '',
>  `speaker_freq` varchar(50) NOT NULL default '',
>  `speaker_signal_noise` varchar(50) NOT NULL default '',
>  `mon_type` varchar(15) NOT NULL default '',
>  `mon_diag` varchar(25) NOT NULL default '',
>  `mon_max_res` varchar(25) NOT NULL default '',
>  `case_type` varchar(45) NOT NULL default '',
>  `car_power` varchar(15) NOT NULL default '',
>  `md_tip` varchar(15) NOT NULL default '',
>  `md_bla` varchar(15) NOT NULL default '',
>  `md_model` varchar(15) NOT NULL default '',
>  `kb_model` varchar(30) NOT NULL default '',
>  `kb_conection` varchar(25) NOT NULL default '',
>  `conex` varchar(30) NOT NULL default '',
>  `print_tip` varchar(30) NOT NULL default '',
>  `print_tip_pagina` varchar(30) NOT NULL default '',
>  `print_speed` varchar(80) NOT NULL default '',
>  `pv_chipset` varchar(50) NOT NULL default '',
>  `pv_mem` varchar(50) NOT NULL default '',
>  `pv_tip_mem` varchar(50) NOT NULL default '',
>  `scan_tip` varchar(20) NOT NULL default '',
>  `scan_interface` varchar(20) NOT NULL default '',
>  `scan_resolution` varchar(20) NOT NULL default '',
>  `scan_colors` varchar(30) NOT NULL default '',
>  `scan_paper` varchar(30) NOT NULL default '',
>  PRIMARY KEY  (`prod_id`)
>) TYPE=MyISAM;
>
>  
>
>
>  
>