Very Computer

I'm new to SQL Server, having previously only worked with
MySQL, and not in great detail.  I merely created basic
tables and constructed queries.  I am now at a new job
where I'm using SQL Server doing ASP.NET development.

Here is the situation at hand:

I have two tables, AutoGeneral and AutoOptions.  I am
curious as two what the best method for keeping track of
entries specific to an assigned AutoID.  I was reading
about auto-numbering with identity properties, but I'm not
quite sure I understand it.  I simply want a column in
each table called AutoID that will hold the ID #, and I
want the ID #'s to start at 1 and increment +1 with each
new row inserted.  How would I go about setting this up?  
Thank you in advance.

Can you post your table DDLs(CREATE table statements), sample
data as INSERT statements & expected results?

--
- Anith

Well, I was using SQL Server Enterprise Manager.  I don't
have the CREATE statements written out.  I already have
the tables made.  This is the CREATE statement generated
by the 'AutoGeneral' table I already have:

CREATE TABLE [dbo].[AutoGeneral] (
        [AutoVIN] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoYear] [smallint] NOT NULL ,
        [AutoMake] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoModel] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoMiles] [int] NOT NULL ,
        [AutoPrice] [smallmoney] NOT NULL ,
        [AutoWarranty] [bit] NOT NULL ,
        [AutoWarrantyLength] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoType] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoTransmission] [bit] NOT NULL ,
        [AutoSpeeds] [tinyint] NULL ,
        [AutoFuel] [tinyint] NOT NULL ,
        [AutoEngine] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [AutoDrive] [tinyint] NOT NULL ,
        [AutoDoors] [tinyint] NOT NULL ,
        [AutoHatch] [bit] NOT NULL ,
        [AutoAC] [bit] NOT NULL ,
        [AutoRadio] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoExtColor] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoIntColor] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [AutoOther] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
)

And here is the AutoOptions:

CREATE TABLE [dbo].[AutoOptions] (
        [ABS] [bit] NOT NULL ,
        [Airbags] [bit] NOT NULL ,
        [CruiseControl] [bit] NOT NULL ,
        [DayRunLight] [bit] NOT NULL ,
        [FogLight] [bit] NOT NULL ,
        [GPS] [bit] NOT NULL ,
        [HeatMirror] [bit] NOT NULL ,
        [HeatSeat] [bit] NOT NULL ,
        [LeathInt] [bit] NOT NULL ,
        [RoofRack] [bit] NOT NULL ,
        [Sunroof] [bit] NOT NULL ,
        [MultiWheel] [bit] NOT NULL ,
        [OnStar] [bit] NOT NULL ,
        [Keyless] [bit] NOT NULL ,
        [Antenna] [bit] NOT NULL ,
        [Locks] [bit] NOT NULL ,
        [Mirror] [bit] NOT NULL ,
        [Seats] [bit] NOT NULL ,
        [Steering] [bit] NOT NULL ,
        [Windows] [bit] NOT NULL ,
        [Wipers] [bit] NOT NULL ,
        [RearDefrost] [bit] NOT NULL ,
        [RemDoor] [bit] NOT NULL ,
        [RemTrunk] [bit] NOT NULL ,
        [SecureSys] [bit] NOT NULL ,
        [Spoiler] [bit] NOT NULL ,
        [TowPack] [bit] NOT NULL ,
        [TractCon] [bit] NOT NULL ,
        [AlumWheel] [bit] NOT NULL ,
        [ChromeWheel] [bit] NOT NULL
)

All of the values are pulled from a web form.  This is for
keeping track of used car inventory online.  The
AutoGeneral table is for all of the main automobile info,
and the AutoOptions table is for all the extra options.  I
wanted to assign an AutoID to each row entered into the
two tables, so I can reference the two rows by calling one
unique ID.  Follow?  For example:

         / ----AutoGeneral----AutoID + AutoGeneral Values
NewEntry1                      Same
(Values from                    |  
the web form)\                 Same
              --AutoOptions---AutoID + AutoOptions Values

I just want the value of AutoID to start at 1, and
increment +1 for each new set of values, or row, entered
into the two tables.

Quote:>-----Original Message-----
>Can you post your table DDLs(CREATE table statements),
sample
>data as INSERT statements & expected results?

>--
>- Anith

>.

Apart from all the issues that Joe was so kind to point out, here's another
suggestion:

Your AutoGeneral already has a good candidate for the Primary Key, AutoVIN.
There really shouldn't be a need for an AutoID other than the fact that a
VARCHAR(50) doesn't make for a great PK. Also AutoOptions seems a bit
excessive so too does prefixing every column name with Auto, and, as Joe
pointed out, you might want to do some more detailed data analysis before
you firm up your table schema, i.e. what really constitutes an option and
what datatypes are appropriate.

Anyway, having said all that, how about this (I've pretty much taken your
table schema and just added to it):

CREATE TABLE [dbo].[AutoGeneral]
(AutoID INT IDENTITY(1,1) PRIMARY KEY,
[AutoVIN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AutoYear] [smallint] NOT NULL ,
[AutoMake] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AutoModel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AutoMiles] [int] NOT NULL ,
[AutoPrice] [smallmoney] NOT NULL ,
[AutoWarranty] [bit] NOT NULL ,
[AutoWarrantyLength] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AutoType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AutoTransmission] [bit] NOT NULL ,
[AutoSpeeds] [tinyint] NULL ,
[AutoFuel] [tinyint] NOT NULL ,
[AutoEngine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoDrive] [tinyint] NOT NULL ,
[AutoDoors] [tinyint] NOT NULL ,
[AutoHatch] [bit] NOT NULL ,
[AutoAC] [bit] NOT NULL ,
[AutoRadio] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AutoExtColor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[AutoIntColor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[AutoOther] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

If you choose to use VIN as your PK, simply remove AutoID, make sure you
declare a PK constraint for VIN and replace AutoID in the following DDL with
VIN.

CREATE TABLE AutoOptions
(OptionID INT IDENTITY(1,1) PRIMARY KEY,
Description VARCHAR(20) NOT NULL)

INSERT INTO AutoOptions VALUES ('ABS')
INSERT INTO AutoOptions VALUES ('Airbags')
INSERT INTO AutoOptions VALUES ('CruiseControl')
etc.

CREATE TABLE AutoGeneralOptions
(AutoID INT CONSTRAINT FK_AutoGeneral FOREIGN KEY REFERENCES AutoGeneral
(AutoID),
OptionID INT CONSTRAINT FK_AutoOptions FOREIGN KEY REFERENCES AutoOptions
(OptionID),
CONSTRAINT PK_AutoGeneralOptions PRIMARY KEY (AutoID, OptionID))

You then use AutoGeneralOptions to associate the AutoID for a vehicle from
AutoGeneral with the appropriate AutoOptions that the vehicle has. Options
that are not applicable simply do not have a record in AutoGeneralOptions.

So if a vehicle has Airbags but not ABS, you would do:

INSERT INTO AutoGeneralOptions VALUES (1, 2)  -- given that Airbags results
in an OptionID of 2

In other words you have a many-to-many relationship between AutoGeneral and
AutoOptions, hence the additional table AutoGeneralOptions that provide a
one-to-many relationship between AutoGeneral and AutoGeneralOptions, and a
one-to-many between AutoOptions and AutoGeneralOptions.

Michael MacGregor
Database Architect
SalesDriver