Thanks for the reply.
I've spent some time considering your suggestions and have tried to
normalise the data further. I'm not sure whether I've gone from one extreme
to the other though - instead of 2 tables, I've now got 17 tables!!
I've included the code I used after the following info (see below)
I created separate tables for categories, category_type, codes, colours,
sizes, descriptions, suppliers, title and garment type. I also created a
garments table. Since I found most of the abovementioned tables to be of a
many-to-many relationship with regard to the garments table, I thought I had
to create look-up or linking tables (not sure what the correct term is
here), so I created tables like garment_to_code and garment_to_colour etc.
in order to show the relationship between the particular tables and the
garments table.
I'm still not sure if I'm on the right track - the garments table doesn't
quite look right. I mean I kind of thought garments was the main entity and
then colour, size, description etc. were attributes of this entity, so I
thought they should somehow be included within the garments table but I
didn't know how to do that because of the many-to-many relationships.
I chose the inclusion of the different foreign keys within the tables based
on how I wanted the CMS to function (also so I could query the database
successfully for use in flash), eg. I wanted the "Insert new garment item"
page of the CMS to include the following functionality:
Based on which supplier is clicked on, the category menu dropdown would be
populated with any category that particular supplier has, therefore I
thought I'd need to include supplier_id as a foreign key in the categories
table. Same with Garment Type - include supplier_id in garment_type table.
Once a garment type is clicked on, the code, description, colour and size
menus get populated with info relevant to the garment type and supplier,
therefore code, description, size and colour would need to have both
supplier_id and garment_type_id as foreign keys in their respective tables.
Sorry for the long drawnout explanation above, but just wondered if someone
could comment on how they think I've progressed with this, since my first
email - just so I know I'm not completely off track!
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
`extra_info` varchar(50) default NULL,
PRIMARY KEY (`garment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`cat_type_id` smallint(5) unsigned NOT NULL,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `category_type` (
`cat_type_id` smallint(5) unsigned NOT NULL auto_increment,
`cat_type` varchar(20) NOT NULL,
PRIMARY KEY (`cat_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `codes` (
`code_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`code` varchar(20) NOT NULL,
PRIMARY KEY (`code_id`),
UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `description` (
`desc_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`description` varchar(70) NOT NULL,
PRIMARY KEY (`desc_id`),
UNIQUE KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_category_type` (
`garment_id` smallint(5) unsigned NOT NULL,
`category_type_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`category_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_code` (
`garment_id` smallint(5) unsigned NOT NULL,
`code_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`code_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM 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]
