[thelist] Re: Database schema
Ken Schaefer
ken at adOpenStatic.com
Wed Dec 10 19:39:17 CST 2003
Personally, I don't think that posting your .sql statement is a good idea at
all.
I'm sure that if Rudy was still actively participating he'd say the same
thing. Your "Product" table does look messy, and I can understand why you're
starting to think that it's becoming unmanageable.
However, as stated before, you're jumping the gun. You need to do some ER
model *first*. You need to create some DFD (or similar) *first*. Once you
have these things, the database schema will write itself.
There are many different ways that this could be approached but without the
appropriate modelling of the system, all you're doing is relying on
experience/gut feeling, and it appers that this isn't getting you the
answers you need.
Do the model first. Post it up someplace, and then we can tell you what you
need to do.
Cheers
Ken
Microsoft MVP - Windows Server (IIS)
PS One possiblity is to store the attribute as a value in a column:
TABLE: PRODUCTATTRIBUTES
ProductID Attribute AttributeValue
1 ScanResolution 300 DPI
1 Weight 2 kg
2 CPU_FSB 333 MHz
but, I can give you another dozen different ways you can organise your data.
Create your ER diagram please.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Cosmin G" <cosming at as.ro>
Subject: [thelist] Re: Database schema
: Well, I think the best thing I can do is show everyone the .sql file, so
: here goes:
:
: phpMyAdmin MySQL-Dump
: # version 2.4.0
: # http://www.phpmyadmin.net/ (download page)
: #
: # Host: localhost
: # Generation Time: Dec 11, 2003 at 01:21 AM
: # Server version: 4.0.14
: # PHP Version: 4.3.1
: # Database : `garett_garettvoltec`
: # --------------------------------------------------------
:
: #
: # Table structure for table `categories`
: #
:
: DROP TABLE IF EXISTS `categories`;
: CREATE TABLE `categories` (
: `cat_id` int(11) NOT NULL auto_increment,
: `category_name` varchar(50) NOT NULL default '',
: `tip_categorie` varchar(50) NOT NULL default '',
: `state` smallint(6) NOT NULL default '1',
: PRIMARY KEY (`cat_id`)
: ) TYPE=MyISAM;
: # --------------------------------------------------------
:
: #
: # Table structure for table `exch_rate`
: #
:
: DROP TABLE IF EXISTS `exch_rate`;
: CREATE TABLE `exch_rate` (
: `usd_rate` mediumint(9) NOT NULL default '0',
: `modified` timestamp(14) NOT NULL
: ) TYPE=MyISAM;
: # --------------------------------------------------------
:
: #
: # Table structure for table `manufacturer`
: #
:
: DROP TABLE IF EXISTS `manufacturer`;
: CREATE TABLE `manufacturer` (
: `manuf_id` int(11) NOT NULL auto_increment,
: `manuf_name` varchar(45) NOT NULL default '',
: `website` varchar(70) NOT NULL default '',
: `logo` varchar(60) NOT NULL default '',
: PRIMARY KEY (`manuf_id`)
: ) TYPE=MyISAM;
: # --------------------------------------------------------
:
: #
: # 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;
:
: > Specifically, what
: > is the main purpose of the DB.
:
: It would be meant for a online store. And I've been asked to make it so
that
: people could perform searches according to very specific details for each
: product, hence the big number of details in the 'products' table.
: The exchange rate table would contain the daily USD exchange rate since
: where I live prices revolve around the USD, (and no, I'm not living in
: America :) ), hence a product's price is determined along the lines of
: 50$*daily exch rate*VAT.
: Any suggestions deeply appreciated.
:
More information about the thelist
mailing list