Normalization is about using ids to minimize change, which also eliminates repetition. It's fine to have the color "red" repeated
throughout your table as long as it will never change. But if you suddenly have two shades of red, you'll need to update all the
records that say "red". If you used id's, you just update the text associated with the id, a single record. Nobody ever designs to
5th normal form (except as an exercise), you usually reach level 2 or 3.
When designing a database, you want to determine the various "objects" you need to hold and their attributes. One mistake is that
you are putting the garment attributes in the a general description field. Which is fine if you don't need to search on more than
free form text. Sleeve, fabric, cuff, colors, etc. are all attributes of the garment. Since a garment can have multiple attributes,
the attributes should be in a separate table.
I would create an attributes table that contains all the attributes of the garment. Then you would be able to search the single
table to find all garments made of a certain fabric with a certain cuff type. The garment attributes table contains codes that link
to a description. The description could have multiple fields so you can handle conversions between different markets. For example,
sizes vary between different geographic areas (my sneaker has sizes on the label for US, UK, EU and CM). Since the size is
represented by an ID, you can search on any of the sizes the ID represents.
----- Original Message -----
From: "Officelink" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, May 23, 2007 8:29 AM
Subject: Database design
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
