DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_description` (
`garment_id` smallint(5) unsigned NOT NULL,
`desc_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`desc_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_title` (
`garment_id` smallint(5) unsigned NOT NULL,
`title_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`title_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_type` (
`type_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type` varchar(30) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `suppliers` (
`supplier_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
PRIMARY KEY (`supplier_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `title` (
`title_id` smallint(5) unsigned NOT NULL auto_increment,
`title` varchar(60) NOT NULL,
PRIMARY KEY (`title_id`),
UNIQUE KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Thanks for any continued support.
From: John Meyer [EMAIL PROTECTED]
Date: Wed, 23 May 2007 07:28:23 -0600
To: mysql@lists.mysql.com
Subject: Re: Database design
Officelink wrote:
Hi everyone,
I¹m trying to set up a database with information that will be used in a
garment slideshow in flash.
The information to be included as part of the slideshow would be:
code, optional title, description, colours, sizes, garment image, fabric
swatch image
Each clothing item to be included in the slideshow will belong to one of
eleven or so categories. Each of the categories will belong to one of two
category types.
I also planned to set up a simple CMS that would allow the information to be
added, edited and deleted from the database.
With the above goals in mind, I came up with two tables as follows:
GARMENTS TABLE
garment_id, int(11), not null, auto_increment, primary key
cat_id, int(10), unsigned, not null, default 0
garment_code, varchar(30), not null
garment_title, varchar(40), null
garment_desc, varchar(255), not null
garment_image, varchar(50), not null
garment_colour, varchar(50), not null
garment_swatch, varchar(50), null
garment_sizes, varchar(100), not null
CATEGORIES TABLE
cat_id, int(10), not null, auto_increment, primary key
cat_name, varchar(40), not null
cat_type, tinyint(4), not null, default 1
I was worried about repeating data in some of the columns, for example the
garment_desc column would have information about sleeve length, cuff type,
fabric, fabric composition etc. and I thought that all these areas could
possibly be broken up into separate tables, but I wasn¹t sure if it was
necessary. Also the colour and size columns would have a lot of repetitive
data.
While normalization does have the goal of eliminating repetition, there
are other reasons. Most notably, you don't want to introduce errors or
even differences into your database.
A person who accidentally types eRd, for instance. You might, and I
emphasize the word might, consider breaking color and size into two
different tables based upon the following:
1. The possible set of valid answers.
2. Whether that element will be used in any sort of grouping or
searching level (are you able to search by color, for instance)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
