Big table

[John.Arthur]

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),
isAccVerificate d bit,
verificationNum ber 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_da ta" 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.

[David Portas]

12 columns is a perfectly reasonable number if you need them. I don't
think most people would call that large. Your defined sizes of those
columns are a different matter. You surely don't need 250 characters
each for first and last name! It's wise to choose sensible maximum
sizes for character columns - if you put the space in there, someone
will use it.

Going by the column names it doesn't look like you have any obvious
transitive dependencies but you do appear to have some other problems:

ID is the only key, which it shouldn't be if it's an artificial key.
How about enforcing the constraint that the username or email address
be unique and non-NULL? Usually that makes sense for sites that require
registration.

All your columns except ID are nullable! Do you really want people to
register anonymously? What's the point of a row with only an ID but no
other information.

isMale? Why not call this Gender or Sex and use the ISO codes for Sex,
which are 1=Male and 2=Female?

Store date of birth rather than age. Otherwise you have the problem of
keeping the age up to date.

Don't store passwords in the database; store secure hashes instead.
Password recovery mechanisms are insecure and bad practice, even where
high levels of security aren't a requirement (people tend to re-use
their passwords so you could end-up exposing the password to someone's
E-Banking account!)

--
David Portas
SQL Server MVP
--

[Andy Williams]

John,

Along with what David said, I'd avoid storing the pictures in the database.
For one thing, surely you want the members to be able to post multiple
pictures, and this design won't allow for that. Store them in the file
system instead.

Take a look at:
http://www.aspfaq.com/show.asp?id=2149

-Andy
<Jo*********@gm ail.com> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...

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),
isAccVerificate d bit,
verificationNum ber 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_da ta" 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.

[Dave]

You can easily normalize the pictures into another table so that
multiple pictures could be
stored under the same account. Personally, I like that approach better
than cluttering the
file system.

-- Dave

[Andy Williams]

True. To each his own... :)

"Dave" <da******@hotma il.com> wrote in message
news:11******** *************@l 41g2000cwc.goog legroups.com...

You can easily normalize the pictures into another table so that
multiple pictures could be
stored under the same account. Personally, I like that approach better
than cluttering the
file system.

-- Dave