Linking Two Tables Via One ID Number

Linking Two Tables Via One ID Number

Post by Scott Smit » Wed, 11 Sep 2002 03:25:16



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.

 
 
 

Linking Two Tables Via One ID Number

Post by Anith Se » Wed, 11 Sep 2002 04:08:37


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

--
- Anith

 
 
 

Linking Two Tables Via One ID Number

Post by Scott Smit » Wed, 11 Sep 2002 06:04:30


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

>.

 
 
 

Linking Two Tables Via One ID Number

Post by Michael MacGrego » Wed, 11 Sep 2002 07:25:01


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

 
 
 

1. Upate between two Ids in one table

Im familiar with running a SQL update between two tables but im kind of
stuck
in running it on one table. What i want to do is copy the contents of one
field to
other fields dependent on several things. All in one tabel i have the
structure like
so:

Id        Dir            Name        Comm
-1        dir1\          name1        this is a comment
-1        dir2\          name2        another comment
-1        dir2\          name3
.
.
.
2        dir2\            name1
2        dir2\            name2
2        dir2\            name3
.
.
.
15
.
.
In this case I want to run a SQL statement such that the Comm field for Id =
2 gets updated
to the same Comm as that for Id = -1 only if the Dir and Name fields are the
same. I can and have
done this type of thing between two tables but am puzzled as to how I can do
it on one table.
Any help is appreciated.
tia
AGP

2. lone table

3. Linking two different tables on one form WITHOUT using Data Environment Deisgner

4. Beginning Progress programmer looking for work

5. Linking two tables to enable inserting value from one to other

6. How to merge to domobjects on the fly?

7. normalization - two linked fields in one table

8. EXEC FOR XML

9. Create One Record Set from two Tables located in Two Databases

10. Two tables, one SQL query, one DBGrid?

11. Two tables with same name, one uppercase, one lowercase

12. How to use ADO to create one to one relationship between two tables

13. Join two ID Fields With One Source