Using SQL to Create Tables: Does This Look Right?

John,

I am trying to simplify the data tables created by a commercial form module I am using; currently, all of the data go into ONE column and there are two tables with a myriad of IDs linking the questions to each respective record, which, clearly based on the questions I am about to ask, is way, WAY over my SQL head. :) I think I can manage the data if each form and its records went into its own table.  Therefore, I was given the following:

CREATE TABLE mycustomtable
(
    mytable_id  smallint
        IDENTITY(1,1)
        PRIMARY KEY CLUSTERED,
    FirstName        varchar(300)   NULL,
    LastName        varchar(300)   NULL,
)

...couple of questions to understand what's going on here. If I used HOTEL as the custom table name, would the syntax be: CREATE TABLE HOTEL (HOTEL_ID smallint...? The mycustomtable and mytable throws me off. :)

The Identity and Primary key are used to create a unique ID for each record? I am just wondering what's going on here....

Lastly, I am using about a dozen variables for each form; some are dates, some currency, some text, so integers, etc. Since I am only trying to create a grid view that allows for a simple query/filter, could I just set all fields to varchar and not run into any problems down the road? Would sorts still function properly?

So, for example, I was going to use the following:

CREATE TABLE Hotel

(
    hotel_id  smallint
        IDENTITY(1,1)
        PRIMARY KEY CLUSTERED,
    h_status        varchar(100)   NULL,
    h_bid              varchar(50)   NULL,
    h_checkin       varchar(50) NULL,
...etc
)

Am I heading in the right direction here?

...and not to put the cart before the horse, but I then planned to use an:

INSERT INTO Hotel (h_status, ...) Values ('$(h_status)', ...)

...then SELECT my hotel table using the SQLGridSelectedView module to do a simple gridview.

Sound like a plan? I only ask, as I hate to go down this road, only to find a better way or run into problems later after I have a 1,000 records, thereby making this even harder on myself re-doing it. :)

I'll try to answer these questions, but keep in mind that you are jumping into the deep end of the pool here. ;)

Yes, CREATE TABLE mycustomtable creates a new table named mycustomtable, and CREATE TABLE Hotel would create one called hotel.

The prefix of the tablename on the ID field is not required, but it does help when you are joining that field in queries etc. Also, ID by itself is a reserved word so if you use it for a field name then you will need to wrap it in square brackets when you use it in a query. [ID]

The IIDENTITY(1,1)   PRIMARY KEY CLUSTERED, part creates a Unique Index on your table so records can be looked up or related to other tables in Queries and Joins.

Yes, you could make them all varchars, but the sorting may or may not work as anticipated. Using strong datatypes is what helps to keep all data valid in its context.  If you make a field that holds numbers into a varchar then someone could put the word 'one' in there which is not a number and would also sort after the number 2.

For the question on doing Insers and displaying the data your plan sounds good, but don't fool yourself into thinking it will be that easy.  There are many, many things that will come up as you try to Create, Read, Update, and Delete data (also known as CRUD).