Database design

Hi,

I am writing a small dating site and now I am designing the DB.

At some point I realized that I will need to create a table with more than
12 columns and I felt that it is not such a good idea.
I?ve tried normalizing the database, but It was with no effect.
So let?s sat that I have the following table:

CREATE TABLE users
(
id int CONSTRAINT pk_user_id PRIMARY KEY,
username varchar(100),
password varchar(100),
isAccVerificated bit,
verificationNumber int,
f_name nvarchar(250),
l_name nvarchar(250),
e-mail varchar(250),
age int,
location l_name nvarchar(250),
visitors int,
isMale bit
picture_data binary
)

?picture_data? contain the real picture in byre array.

So my question is what is preferably to do.
I believe this is not such a good idea to leave this table like this.

Thanks in advance.

--
Message posted via http://www.sqlmonster.comTwelve columns is not so much. You should see some business
applications. One piece of advice: DO NOT store the image in the
database. Store it in a directory. The database should reference the
path of the image.|||You did a lot of bad things. Very bad things.

1) What is the rule for verifying the user_id? Check digit? Email
address? Anything at all?

2) How many people have you ever met with a CHAR(100) name? If you had
bothered to do any research before getting to newsgroup, you would know
the postal sytem
uses 35 characters per line on a mailing label.

3) Have you ever seen a password with a CHAR(100) value? Without any
rules about letters and numbers in it? Where are your constraints?

4) Why did you use a proprietary, low-level, physical data type like
BIT in the isAccVerificated flag column? SQL programmers do not write
code like that. Assembly language programmers use bit flags. That
isMale is silly; you never researched to find the ISO Standards.

5) How many people can you find in the phone book with a CHAR(250)
first or last name? In Chinese. Hey, if you allow that in the schema,
you will get them.

6) Why did put age instead of birth date? You like doing constant
updates or just not understand the basic nature of time?

7) "e-mail" is an invalid column name. You never did a syntax check
before posting, did you?

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
user_name CHAR(35)NOT NULL,
password CHAR(12)NOT NULL,
verification_nbr INTEGER NOT NULL,
first_name VARCHAR(15)NOT NULL,
last_name VARCHAR(15)NOT NULL,
email VARCHAR(128)NOT NULL, -- research the max length
birth_date DATETIME NOT NULL,
location_name VARCHAR(35)NOT NULL,
gender_code INTEGER NOT NULL --iso gender codes
CHECK (gender_code IN (0,1,2,9))
picture_file_id CHAR(128) DEFAULT 'C:\foobar\not_available' NOT
NULL);|||Several people have answered in your other thread:
http://www.google.co.uk/groups?selm...ooglegroups.com

--
David Portas
SQL Server MVP
--