Database design

gwh

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.

Someone indicated that normalization is not about eliminating repetition, it’s about ensuring that the non-key attributes are functionally dependent on the entire primary key, but then I read somewhere that you’re supposed to break down the information as far as possible to avoid redundancy so I’m a bit confused. Or does it depend on the situation and what’s required of the database. I mean say the CMS needed to have more functionality than what I indicated above – I mean say the client wanted to be able to generate reports based on style information such as fabric composition or sleeve style etc. - would this change the setup?

I wondered if someone could comment on the setup to see if I’m on the right track here?

Appreciate any help.

Piranha

Normalization is, at least to me, a way to ensure that the database will work when expected changes are implemented. You can set up the database as you do now, but if the customer want to see all products that have the size XL, or the colour blue, or short sleeves you will have to do workarounds or redesign the database instead of doing all this now. And not even the workarounds might work, what is someone misspelled "short sleeves" and wrote "shor sleeves" or "sleeves: short".

I can't say how you should build the database, but it seems that you really need to normalize it. A good guide is here.

Someone indicated that normalization is not about eliminating repetition, it’s about ensuring that the non-key attributes are functionally dependent on the entire primary key

That someone is wrong. The first normal form is partly to avoid repetitive information. That someone is also right. The third normal form is to ensure that non-key attributes are functionally dependent on the entire primary key. But to have it in third normal form you need to have it in first and second as well, which you don't have if you have repetitive information.

gwh

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 - 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB 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=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Thanks for any continued support.

Piranha

You have the right idea with doing lots of tables. But 17 tables is a little to much, and what is more you need to redesign the database, programming and layout if you want one more characteristic.

But it seems really wrong to have supplierid everywhere. Exactly what is the supplier supplying? If it is garments then the supplyierid should be in garments, then join the tables to get the supplierid. Think about the database as storing data, nothing more and nothing less.

An idea how you can handle the database.

TABLE category
id
// other stuff

TABLE garments
id
categoryid
image
// and so on

TABLE characteristic
id
name
// and so on

TABLE characteristicvalue
id
characteristicid
value
// and so on

TABLE characteristicvalue_to_garments
garmentsid
characteristicvalue
// maybe more

Then you sould have Colour in characteristic, blue, red, black in characteristicvalue and then link it to garments with characteristicvalue_to_garments. The relationships would look something like this:

------------     ------------     -----------------------     -----------------------     ------------------
|          |     |          |     |                     |     |                     |     |                |
| category |1   *| garments |1   *| characteristicvalue |*   1| characteristicvalue |*   1| characteristic |
|          |     |          |     | _to_garments        |     |                     |     |                |
|          |     |          |     |                     |     |                     |     |                |
------------     ------------     -----------------------     -----------------------     ------------------

And you would need about 5 tables (maybe 1 or 2 more, don't know for sure).

gwh

Thanks again for the extra info.

I've revised the table structure (see below), and I’m now trying to figure out the best sequence to enter data into the database. If I have a number of tab delimited .txt files containing the data for the different tables, I thought as a first step I could use the following code to populate the garments table:

LOAD DATA INFILE 'garments.txt'
INTO TABLE garments
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'

I also thought I could use the same code to insert the data into the colours, sizes and categories tables. My problem starts when I have to try to enter the correct garment_id from the garments table into the correct foreign key fields in the garments_to_colour, garments_to_sizes and garments_to_categories look up tables. Do I need to do this manually or can I use the last_insert_id() function? But not sure if this would work since I’m not populating the garments table one row at a time – I’m inserting all info in one go with LOAD DATA INFILE, as mentioned above.

Wondered if you could help solve this problem?

CREATE TABLE garments (
garment_id smallint(5) unsigned NOT NULL auto_increment,
supplier varchar(30) NOT NULL,
garment_type varchar(30) NOT NULL,
title varchar(60) NOT NULL,
code varchar(20) NOT NULL,
description varchar(400) NOT NULL,
extra_info varchar(50) default NULL,
image enum('y','n') NOT NULL,
swatch_image enum('y','n') NOT NULL,
PRIMARY KEY (garment_id),
UNIQUE KEY supplier (supplier,garment_type, description, title,code)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE colours (
colour_id smallint(5) unsigned NOT NULL auto_increment,
colour varchar(20) NOT NULL,
PRIMARY KEY (colour_id),
UNIQUE KEY colour (colour)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE sizes (
size_id smallint(5) unsigned NOT NULL auto_increment,
size varchar(15) NOT NULL,
PRIMARY KEY (size_id),
UNIQUE KEY size (size)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE categories (
cat_id smallint(5) unsigned NOT NULL auto_increment,
category varchar(30) NOT NULL,
PRIMARY KEY (cat_id),
UNIQUE KEY category (category)
) ENGINE=InnoDB 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=InnoDB DEFAULT CHARSET=latin1;

Piranha

Unfortunatley I can't help with that since I have never used load data. Hopefully someone else can help you out instead.