Re: Database Design

H wrote:

>
> First let me reiterate that i'm new to this :) And also

        I think that before anything elsee it would be A Very Good Thing if you read "An Introduction to Database Systems", 7th ed, Chris J Date, 1.999, Addison Wesley.

> here is why I don't want to add a new column every time.

        No need, the relational model for database management is everything you should ever need -- the parts of it that weren't properly implemented won't affect you here.

> I'm trying to match employees with customers. The way i would like to
> do this is have a web site where employees and customers fill out
> forms about themselves. In otherwords they would specify their age,
> sex, degree, university etc.

        I think the first thing is that you need to get yourself a DA cum DBA cum data modeller!

> pull the data out and run my matching algorithm. However, I want to
> make the database flexible. If i find out that age, sex, degree, and
> university is not satisfactory for making a good match I would like to
> be able to add extra fields for employees/customers to fill in. I
> want to add these fields without restructuring the entire database,
> especially since I anticipate that i will be doing this several times.

        You can manipulate the database schema as well as anything else... whenever you need a new field you need only issue an ALTER TABLE ... ADD COLUMN statement (or something like that on your RDBMS of choice), set the already existing tuples' values of the new columns to some default value, and you are set. Everything should work OK; you will just have to avoid using the evil SELECT * FROM... construct, and remember to add the new column to any INSERT or UPDATE statements.

        This may seem like too much work, but it is just making explicit problems that your original design would mask away. Alternatively, but much less elegant, you could fail to define the new columns as NOT NULL, or else give them default values that would be used in cases of INSERT or UPDATE.

        In practice you will discover that after some months your database

won't change much anymore. And anyway you should set your develpment environment so that you will easily any statement touching relations you've changed.

-- 
  _
/ \ Leandro Guimarães Faria Corsetti Dutra           +55 (11) 246 96 07
\ / http://geocities.com./lgdutra/       BRASIL      +55 (43) 322 89 71
  X  http://tutoriald.sourceforge.net./     mailto:lgcdutra_at_terra.com.br
/ \ Campanha fita ASCII, contra correio HTML    mailto:leandrod_at_mac.com