SEO

Hi Everyone,

I am trying to get some hands on database design basics. I have a
sample information set below and then the normalized table information

Information:
FirstName
LastName
Language
Country
State
ZipCode
TimeZone
Gender
BirthDate
Occupation
UserID
Password
SecretQuestion
SecretAnswer
Altername_EmailID


Normalized Tables Derived from the above information set:

User_Table:
UserID varchar primary key
FirstName varchar
LastName varchar
GenderID short
BirthDate date
OccupationID short
Alternate_EmailID varchar


Locale_Table:
UserID varchar foreign key on User_Table
Langauge varchar
Country varchar
State varchar
ZipCode char
TimeZone ????


Authentication_Table
UserID varchar foreign key on User_Table
Password varchar (Some sort of Encryption maybe required on this
column)
SecretQuestion varchar
SecretAnswer varchar (Some sort of Encrytion maybe required on this
column)

Gender_Table
GenderID short primary key
GenderType varchar

Occupation_Table
OccupationID short primary key
OccupationName varchar


I know there is lots and lots of room for improvement on this. I am
not able to decide as to how should I store the data pertaining to the
TimeZone column? Kindly let me know as to what else should I do to
further
improvise on it.

Thanks in advance,
dotyet

Database design is a broad enough subject that a forum discussion may
not be the best place to learn it. You really need to read some books on
the subject or, even better, take a college level class or vendor class
in it. Books should be available from your local library, local
bookstores and online/mailorder book vendors. A reference libraian at
your local library can explain how to find "good" books on a subject.

Science and art are both important components of database design. The
art portion is where you take your application knowledge and experience
and use it as part of the design process to create a solution that may
violate the scientific principles but gives a solution that is "correct"
for the application. A number of years experience are usually needed to
develop a designer's art skills.

The following are some of the items that should be considered for the
example you gave:
1. Can a user have multiple languages?
2. Zip codes for US only or postal codes whenever available?
3. Can there be more than one alternate Email ID?
4. Can there be more than one occupation?
5. Does password need to be stored encrypted?
6. How critical is disk space to store the data?

Other basic design questions:
1. What is the relationship of the data elements? Most of the data is
related to a userID. Country, state, zipcode and timezone are related to
each other and could be associated with multiple userids.
2. What are the regulatory requirements for safeguarding the privacy of
this data. Will they force splitting the data into separate tables for
security or could views be used to control data access. One of the
factors that influences this is what groups of people will be using the
data and what parts of it do regulations allow them to access.
3. Potential growth of the users of the data should be considered.
Today, one or two people may do all maintenance but in a few years there
may be ten or twenty people with different responsibilities accessing it.

A short primer on database design can be found in the UDB manual
db2d1e80, Admin Guide: Planning. It's a good introduction to the science
part of the design process. If you can't find it on your UDB
installation disks, it's available online from IBM.

Philip Sherman


dotyet@yahoo.com wrote:
> Hi Everyone,
>
> I am trying to get some hands on database design basics. I have a
> sample information set below and then the normalized table information
>
> Information:
> FirstName
> LastName
> Language
> Country
> State
> ZipCode
> TimeZone
> Gender
> BirthDate
> Occupation
> UserID
> Password
> SecretQuestion
> SecretAnswer
> Altername_EmailID
>
>
> I know there is lots and lots of room for improvement on this. I am
> not able to decide as to how should I store the data pertaining to the
> TimeZone column? Kindly let me know as to what else should I do to
> further
> improvise on it.
>
> Thanks in advance,
> dotyet
>