List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:April 23 1999 7:04pm
Subject:Re: Enum schema question
View as plain text  
sam1600@stripped wrote:
> 
> Hello,
> 
> Please help.
> 
> I have the following table ( VACATION_PACKAGES )
> 
> CREATE TABLE VACATION_PACKAGES (
> id int(11) DEFAULT '0' NOT NULL,
> age_group enum('mixed','single','couple','senior','family') DEFAULT 'mixed' NOT
> NULL,
> all_inclusive enum('no','yes') DEFAULT 'no' NOT NULL,
> destination char(60) NOT NULL,
> event enum('none','art_culture','festival','sporting') DEFAULT 'none' NOT NULL,
> festival enum('no','yes') DEFAULT 'no' NOT NULL,
> guided enum('no','yes') DEFAULT 'no' NOT NULL,
> price enum('budget','moderate','luxery') DEFAULT 'budget' NOT NULL,
> specialty enum('none','adventure','romantic','athletic','other') DEFAULT 'none' NOT
> NULL,
> type enum('other','coach','cruise','hotel_resort','rail') DEFAULT 'other' NOT NULL,
> start_date date,
> end_date date,
> PRIMARY KEY (id)
> );
> 
> I'm trying to create a database close to "normal form" .
> 
> Here is my dilemma / questions.
> 
> As you can see from the above I have several enum column types.
> Should I separate each of those enum columns to individual tables?
> 
> Take my age group column as an example.
> 
> Should I change this:
> age_group enum('mixed','single','couple','senior','family') DEFAULT 'mixed' NOT
> NULL,
> 
> to this:
> age_group char(20),
> 
> ... and create a AGE_GROUP table with one column for age_goup?... This would
> make it easy for me to grab data from the AGE_GROUP table to make a drop down list
> box for user input into the age_group column of the VACATION_PACKAGES table.
> 
> It seams so much easier for me to have everything bunched into one big
> un-normalized table ( is my VACATION_PACKAGES un-normalized? ) for building
> queries and using separate smaller tables to fill up the big un-normalized table.
> But doesn't that duplicate data ( as in my AGE_GROUP example above).
> 
> I've read several articles on normalizing data.  They all seam to rely on
> simple/complex  business scenarios ( product, customer etc. ).  I have a hard time
> trying to normalize a database that has tables like VACATION_PACKAGES, NEWS, TIPS
> ARTICLES, CONTACTS.
> 
> I realize that this is a design question but maybe someone with a similar
> situation could give me some advice and/or point me to a good SQL mailing list.
> 
> Thanks,
> -Sam

Hi Sam

The info you provided is not enough.
To answer your question we need to know, what you want to do with the table.
What sort of queries do you issue on it?

Normalization has its pros and cons.
Pro is you can easily understand the data structure.
The con can be a slower processing speed, but this depends on the queries you want to use.

In my opinion (YMMV) your design is OK as long as you use this table more or less
standalone and you don't want to add new choices to the enum's regularly.

Tschau
Christian

Thread
Enum schema questionsam160023 Apr
  • Re: Enum schema questionChristian Mack23 Apr