Showing posts with label designing. Show all posts
Showing posts with label designing. Show all posts

Tuesday, March 20, 2012

Big table

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.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
--|||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

<John.Arthur@.gmail.com> wrote in message
news:1113326413.314223.31090@.o13g2000cwo.googlegro ups.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),
> 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.|||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|||True. To each his own... :)

"Dave" <dafergu2@.hotmail.com> wrote in message
news:1113329911.088937.55740@.l41g2000cwc.googlegro ups.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

Friday, February 10, 2012

Best Way of data transfer

Hello All,
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...