[thelist] Re: Database schema

Cosmin G cosming at as.ro
Wed Dec 10 18:04:54 CST 2003


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.


----- Original Message -----
From: <thelist-request at lists.evolt.org>
To: <thelist at lists.evolt.org>
Sent: Wednesday, 10 December, 2003 05:13 AM
Subject: thelist Digest, Vol 10, Issue 18

>    1. Database schema (Cosmin G)
>   22. RE: Database schema (Ken Moore)
>   24. RE: Database schema (Tab Alleman)
>   27. RE: Database schema (Joel D Canfield)
>   28. RE: Database schema (Ed McCarroll)
>
>
> ----------------------------------------------------------------------
>
> Date: Tue, 9 Dec 2003 13:34:33 +0200
> From: "Cosmin G" <cosming at as.ro>
> To: <thelist at lists.evolt.org>
> Subject: [thelist] Database schema
> Message-ID: <004801c3be4c$218174f0$fc69e9d5 at voltec01>
> Content-Type: text/plain;
> charset="iso-8859-1"
> MIME-Version: 1.0
> Content-Transfer-Encoding: 7bit
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 1
>
> Hi. I've been asked to develop a database for a computer website. Right
now
> I have the following tables:
> categories (1 - cpu's, 2 - hard-drives, )
> producer
> product
> exchange rate
> All products have some common values (price, warranty period, description)
> and there are some fields which are product specific (such as cpu
frequency,
> cpu cache memory, etc). Right now I've stored all the product specific
> fields in the products table and prefixed them with the name of the
category
> they refer to. Is this a good approach or should I put them in separate
> tables? The table is getting pretty unmanageable already.
>
>
> ------------------------------
>
> Date: Tue, 09 Dec 2003 12:02:11 -0700
> From: "Ken Moore" <psm2713 at hotmail.com>
> To: thelist at lists.evolt.org
> Subject: RE: [thelist] Database schema
> Message-ID: <BAY9-F66DZlLkzg0nhH000355fd at hotmail.com>
> Content-Type: text/plain; format=flowed
> MIME-Version: 1.0
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 22
>
> Hi all,
>
> Cosmin G wrote:
>
> >Hi. I've been asked to develop a database for a computer website. Right
now
> >I have the following tables:
> >categories (1 - cpu's, 2 - hard-drives, )
> >producer
> >product
> >exchange rate
> >All products have some common values (price, warranty period,
description)
> >and there are some fields which are product specific (such as cpu
> >frequency,
> >cpu cache memory, etc). Right now I've stored all the product specific
> >fields in the products table and prefixed them with the name of the
> >category
> >they refer to. Is this a good approach or should I put them in separate
> >tables? The table is getting pretty unmanageable already.
>
> As a database programmer for 20 years, I have got several ideas. But I
will
> need more info. Since I have just had a nearly unsolvable problem solved
> off-line (thank you very much Chris Rosser) I would be happy to help if
you
> want to write with more details at psm2713 at hotmail.com. Specifically, what
> is the main purpose of the DB.
>
> The key is to design well. Once you have done that, many other problems
> become much easier.
>
> Ken.
>

> Date: Tue, 9 Dec 2003 15:21:03 -0500
> From: "Tab Alleman" <Tab.Alleman at MetroGuide.com>
> To: <thelist at lists.evolt.org>
> Subject: RE: [thelist] Database schema
> Message-ID:
<C263956D43E36B4E966EDE6DE7C10C972AF721 at Yosemite.MetroGuide.RealMetros.RealM
etros.com>
> Content-Type: text/plain;
> charset="us-ascii"
> MIME-Version: 1.0
> Content-Transfer-Encoding: quoted-printable
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 24
>
> Cosmin G wrote:
> > Hi. I've been asked to develop a database for a computer website.
> > Right now I have the following tables:
> > categories (1 - cpu's, 2 - hard-drives, )
> > producer
> > product
> > exchange rate
> > All products have some common values (price, warranty period,
> > description) and there are some fields which are product specific
> > (such as cpu frequency, cpu cache memory, etc). Right now I've stored
> > all the product specific fields in the products table and prefixed
> > them with the name of the category they refer to. Is this a good
> > approach or should I put them in separate tables? The table is
> > getting pretty unmanageable already.=20
>
> I favor splitting category-specific columns into separate tables.
> ------------------------------
>
> Date: Tue, 09 Dec 2003 14:30:33 -0600
> From: Minh Lee Goon <evolt at goonies.info>
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] table height: 100% with CSS
> Message-ID: <3FD630E9.30207 at goonies.info>
> In-Reply-To:
<50229.135.196.95.234.1070896320.squirrel at www.johnallsopp.co.uk>
> References:
<50229.135.196.95.234.1070896320.squirrel at www.johnallsopp.co.uk>
> Content-Type: text/plain; charset=us-ascii; format=flowed
> MIME-Version: 1.0
> Content-Transfer-Encoding: 7bit
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 25
>
> You can use the deprecated cousin of the width attribute, called height.
> <table height="100%">
>
> If you have multiple rows (e.g. a header row and a content row) in your
> table, you should specify the height of the header row as well (<td
> height="200">) and the rest of the table will be 100% less 200px.
>
> Minh Lee
>
> > Maybe I'm having a stupid moment, but I can't see how to make a table
100%
> > high in CSS.
> ------------------------------
>
> Date: Tue, 09 Dec 2003 14:35:59 -0600
> From: Simon Willison <cs1spw at bath.ac.uk>
> To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Subject: Re: [thelist] Web UI Question
> Message-ID: <3FD6322F.6040808 at bath.ac.uk>
> In-Reply-To: <004201c3bdc6$78e25920$0300000a at hershel2000>
> References: <004201c3bdc6$78e25920$0300000a at hershel2000>
> Content-Type: text/plain; charset=us-ascii; format=flowed
> MIME-Version: 1.0
> Content-Transfer-Encoding: 7bit
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 26
>
> Hershel Robinson wrote:
> > When he goes back in to edit one of these numbers, then it jumps to the
left
> > again. This IS a bit weird, but the display looks nice.
> >
> > Any one have any thoughts on this?
>
> Ask some users. Or even better, run some usability tests and watch what
> the users do - if they seem unduly distrurbed by it, ditch it.
> Personally it sounds just fine to me - I've seen interfaces that do this
> before (doesn't Excel do exactly this?) and never been troubled by them.
> ------------------------------
>
> Date: Tue, 9 Dec 2003 12:52:27 -0800
> From: "Joel D Canfield" <joel at spinhead.com>
> To: <thelist at lists.evolt.org>
> Subject: RE: [thelist] Database schema
> Message-ID: <72E9FAA171D63B48AAC707C72900E6B45A1DA5 at ireland.spinhead.com>
> Content-Type: text/plain;
> charset="us-ascii"
> MIME-Version: 1.0
> Content-Transfer-Encoding: quoted-printable
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 27
>
> Right now I've stored all the product specific
> > fields in the products table and prefixed them with the name=20
> > of the category
> > they refer to. Is this a good approach or should I put them=20
> > in separate
> > tables? The table is getting pretty unmanageable already.
>
> All the product stuff should go in 'products'
>
> What fields do you have in there? It's hard to imagine (for me, anyway)
> so many fields that it would be unmanageable. Maybe there *is* stuff in
> there that belongs elsewhere, but it's hard to say without seeing a
> field list.
>
> joel
> ------------------------------
>
> Date: Tue, 9 Dec 2003 13:16:04 -0800
> From: "Ed McCarroll" <Ed at ComSimplicity.com>
> To: <thelist at lists.evolt.org>
> Subject: RE: [thelist] Database schema
> Message-ID: <NDBBLLCLKJDNHHNPAAHHAECPGBAA.Ed at ComSimplicity.com>
> In-Reply-To: <004801c3be4c$218174f0$fc69e9d5 at voltec01>
> Content-Type: text/plain;
> charset="iso-8859-1"
> MIME-Version: 1.0
> Content-Transfer-Encoding: 7bit
> Precedence: list
> Reply-To: "thelist at lists.evolt.org" <thelist at lists.evolt.org>
> Message: 28
>
> > Right now I've stored all the product specific fields in the products
> > table and prefixed them with the name of the category they refer to.
> > Is this a good approach or should I put them in separate tables? The
> > table is getting pretty unmanageable already.
>
> It depends upon what you're doing with the data, but I'd be inclined to
> set up one table for each category.  IMO, a good schema is one that
> accurately models the real world, and CPUs and hard drives aren't
> really the same kind of things.
>
>  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> Ed McCarroll                               MailTo:Ed at ComSimplicity.com
> ComSimplicity                                           (310) 838-4330




More information about the thelist mailing list