database layout

[impulse]

First of all, I'm new to Microsoft Enterprise Server Manager 8.0. I trying to create a database for contact information that will be used in my web application to store info from the contactUs.htm. I have used Access in the past. My database will consist of the following fields: strFN, strLN, strCompany, strTitle, strAdd1, strAdd2, strCity, strState, strZip, strCountry, strPhone, strFax, strEmail, strComments, strRepToContact, and the date.

My questions ..... Do I need a primary key? What should I use as the primary key? Does it offer an autoNum like Access that can be used as the primary key? What data type would the date need to be? Any recommendations on a great book for Microsoft Server?

Thanks for any advice that is offered to a confusion individual.

[russell]

Do I need a primary key? should have one.
What should I use as the primary key? this depends on the data. rememebr that the purpose of a primary key is to guarantee uniqueness in the table. i'd add an identity column and make that the PK. also, might put a unique constraint on (strFN, strLN, strCompany, date) -- assuming that u are capturing date and time in that date column. read on as this is touching on the next questions...
Does it offer an autoNum like Access that can be used as the primary key? identity property. of a bigint, int, smallint column
What data type would the date need to be? use smalldatetime unless u need to store dates way in the past of future.
Any recommendations on a great book for Microsoft Server? here are a few web sites worth a look

http://www.sql-server-performance.com/default.asp
http://www.databasejournal.com/features/mssql/
http://forums.databasejournal.com/
http://www.sqlteam.com/

should try to get used to creating your objects in Query Analyzer and not EM. this will give u far more control AND serve to give u a much greater understanding. a far better teaching tool than any book out there.

[impulse]

Will you explain smalldatetime?

[russell]

datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

[impulse]

Thanks! If I set the strID field as the primary key with bigint, is it an auto-number or do I have to tell it to be one?

[russell]

u have to tell it.

Create Table myTable (
id int identity(1, 1) not null primary key,
strFN varchar(32),
strLN varchar(32) not null,
etc...
)
check out BOL (books online, the built-in sql server help) for complete create table syntax.

[impulse]

Still confused....bol not a lot of help. Do I use Query Analyzer for this?

[russell]

u should use quey analyzer for almost everything. but if u must use the gui, right-click tables, new table, set data type and then set identity to yes.

at first, using the gui will seem easier, because u dont know al the syntax yet. once u do, query analyzer will be much better you'll find.

either way works though.

i know that BOL seems cryptic at first

[impulse]

Thanks! I will give it a try.

[impulse]

Last question, maybe! How do I apply unique constraints using the gui?

[russell]

here's how to do it in Query Analyzer ( 'cause I'm too lazy to fire up EM and check and I don't remember what to click on )


ALTER Table myTable
ADD Constraint constraintName UNIQUE(column[s])

Give constraintName some meaningful name. i prefix unique constraints with UX_, maybe something like UX_TABLENAME_FIELDNAME[s]