Determine Next Available Order ID

I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?

' Establish data connection...

Dim sqlConnAsNew SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))

'Determine order id number...

Dim order_idAsInteger

Dim strSQLAsString

strSQL = "Select MAX(order_id) from mkt_order"

Dim sqlCmdAsNew SqlCommand(strSQL, sqlConn)

Dim sqlDAAsNew SqlDataAdapter(sqlCmd)

Dim sqlDSAsNew DataSet

sqlDA.Fill(sqlDS, "item")

If sqlDS.Tables(0).Rows.Count <> 0Then

order_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1

Else

order_id = 1

EndIf

This is because a DBNull will be returned if you use MAX() function and there is no (qualified) record in the table. Let's try TOP...ORDER BY... to avoid using MAX():

strSQL = "Select TOP 1 order_id from mkt_order ORDER BY order_id DESC"

design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
...
...
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
...
...
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
....
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David
|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> ...
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>
|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.4ax.com... [vbcol=seagreen]
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
many
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
...
...
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
...
...
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much."Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
...
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Please see the three table below, I'm designing the db schema so that it
> > maintains each Client has exactly 1 default service.
> >
> > table ServiceType
> > (
> > ServiceTypeId,
> > ...
> > ...
> > )
> >
> > table Client
> > (
> > ClientId,
> > ...
> > ...
> > )
> >
> > table ClientAvailableService
> > (
> > ClientId foreign key
> > ServiceTypeId foreign key
> > IsDefaultService bit
> > ...
> > ...
> >
> > )
> >
> ...
> >
> >
> > Would someone out there have or know a better way to implement this or
> > just
> > have some comment about this issue? I assume experienced db designers
> > must
> > have run into this issue before. Thank you very much.
> >
> >
> >
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.4ax.com...
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
> >The second way is to do add a ranking column in decimal type or int type,
> >the lowest number would mean that it's the default, this would definitely
> >work but retrieving the default one would require reading and sorting
many
> >rows.
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
..
..
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
..
..
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much."Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
...
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> ...
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.
4ax.com...
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
many[vbcol=seagreen]
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Design Issue With a Table

Hi All,
I have one of those "design" issue that I'm wondering if my train of thought
is correct or if there is a better way...below I listed out as best I could
the issue at hand.
I have a table with the following Psuedo layout that needs to be split into
two separate tables for various concurrency reasons.
First the current table layout (not the real one...but I figured make it
more straight forward)
CREATE TABLE [dbo].[TableWithBothFields] (
[IdentiyField] [int] IDENTITY (1, 1) NOT NULL ,
[EditedByUserField] [char] (10) ,
[SystemUpdatesThisField] [char] (10)
) ON [PRIMARY]
What I would like is to have the following TWO tables instead
Table#1
CREATE TABLE [dbo].[TableWithJustEditedByUserField] (
[IdentiyField] [int] IDENTITY (1, 1) NOT NULL ,
[EditedByUserField] [char] (10) ,
) ON [PRIMARY]
Table#2
CREATE TABLE [dbo].[TableWithJustSystemUpdatesThisField] (
[IdentiyField] [int] IDENTITY (1, 1) NOT NULL ,
[SystemUpdatesThisField] [char] (10)
) ON [PRIMARY]
The "problem" is To have everything done on both tables with the
[IdentifyField] being the same on both tables as well
Since the indenity field is created on INSERTs ...can that me automatically
synced OR am I correct in my thinking that at you must have one table with
the Idenity Filed and on the other it's just a straight integer field which
you update via Stored Procedures.
If this is the way to do it...or for that matter the only way - then I guess
I'm set...however if there is a better way to do it then I am now...I'd
appreciate your input.
Best Regards,
MekimIn principle there's nothing wrong with partitioning a table vertically
in this way although whether this is the "best" solution or not I can't
really say, without knowing the "concurrency reasons" than lead you to
consider this design. I suggest you join the two tables on the Natural
key rather than the IDENTITY, that way will presumably require less
work synching between the two. Unfortunately you didn't include the key
in your simplified example. IDENTITY should never be the only key of a
table.
David Portas
SQL Server MVP
--|||Hi David,
The concurrency issue is that users can edit a one part of the fields in
this table and the system will update a separate portion of the fields in
that same table from time to time.
Therefore - if I a users changes let's say "lastname" field then the
"accountbalance" type fields will be outdated and vice versa.
On a separate note - I am very curious to learn why you say that IDENTITY
should never be the only key on a table.
Best Regards,
Mekim
"David Portas" wrote:

> In principle there's nothing wrong with partitioning a table vertically
> in this way although whether this is the "best" solution or not I can't
> really say, without knowing the "concurrency reasons" than lead you to
> consider this design. I suggest you join the two tables on the Natural
> key rather than the IDENTITY, that way will presumably require less
> work synching between the two. Unfortunately you didn't include the key
> in your simplified example. IDENTITY should never be the only key of a
> table.
> --
> David Portas
> SQL Server MVP
> --
>|||IDENTITY is a SURROGATE key used as a convenient identifier in
referencing tables and elsewhere. It is not a substitute for the
natural key of your table, which should always be declared as unique
and not NULL even when IDENTITY is used.
Every table needs to have a unique key in your logical data model and
IDENTITY is not part of that logical model at all. A table with only an
IDENTITY key therefore has no integrity, no practical value (why would
you want to record the same fact more than once?), harms performance
(due to redundant data) and makes some query problems difficult or
impossible to solve.
David Portas
SQL Server MVP
--|||Hi David,
I'm sorry - but I'm really not understanding what the big deal is - which in
no way minimize what you are saying - in fact since I respect your knowledge
in SQL I am only asking for a clarification so I can understand why I may be
doing incorrectly.
For example - if it were a record of each print job done, why would an
IDENTIY field be good enough? To add another "Counter ID" would not seem
necessary.
Best Regards,
Mekim
"David Portas" wrote:

> IDENTITY is a SURROGATE key used as a convenient identifier in
> referencing tables and elsewhere. It is not a substitute for the
> natural key of your table, which should always be declared as unique
> and not NULL even when IDENTITY is used.
> Every table needs to have a unique key in your logical data model and
> IDENTITY is not part of that logical model at all. A table with only an
> IDENTITY key therefore has no integrity, no practical value (why would
> you want to record the same fact more than once?), harms performance
> (due to redundant data) and makes some query problems difficult or
> impossible to solve.
> --
> David Portas
> SQL Server MVP
> --
>|||If you don't know what the deal is with keys and normalization then you
should read some books on relational database design as this isn't
really the place for a tutorial. You should design for Third Normal
Form as a minimum requirement and that means keys are not optional.
I can only guess what information you would want to record about a
print job. Maybe Document Name, Size, Number of Copies, User Name,
Datetime of Printing. I don't know what you mean by "another Counter
ID". Presumably the natural key might be (user_id, document_name,
datetime_printed).
David Portas
SQL Server MVP
--|||Hi David,
I could always expand my knowledge on this topic - and I certainly will be
making a visit to the book store now. I appreciate you helping me!
Best Regards,
Mekim
"David Portas" wrote:

> If you don't know what the deal is with keys and normalization then you
> should read some books on relational database design as this isn't
> really the place for a tutorial. You should design for Third Normal
> Form as a minimum requirement and that means keys are not optional.
> I can only guess what information you would want to record about a
> print job. Maybe Document Name, Size, Number of Copies, User Name,
> Datetime of Printing. I don't know what you mean by "another Counter
> ID". Presumably the natural key might be (user_id, document_name,
> datetime_printed).
> --
> David Portas
> SQL Server MVP
> --
>|||David, please correct me if I am wrong, but I think the issue is that mekim'
s
CREATE TABLE statements defined the ID fields as only IDENTITY, without a
unique (PK) constraint.
In other words there is nothing wrong with using IDENTITY to create unique
identifiers, but it i spointless to use it on a non-unique field.
So, mekim's original DDL could be corrected as follows:
-- Add PRIMAY KEY constraint to IDENTITY field.
--
CREATE TABLE [dbo].[TableWithBothFields] (
[IdentiyField] [int] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_TableWithBothFields] PRIMARY KEY CLUSTERED,
[EditedByUserField] [char] (10) ,
[SystemUpdatesThisField] [char] (10)
) ON [PRIMARY]
-- Table#1
-- Add PRIMAY KEY constraint to IDENTITY field.
--
CREATE TABLE [dbo].[TableWithJustEditedByUserField] (
[IdentiyField] [int] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_TableWithBothFields] PRIMARY KEY CLUSTERED,
[EditedByUserField] [char] (10) ,
) ON [PRIMARY]
-- Table#2
-- Add PRIMAY KEY constraint to identifying field and foreign key pointing
back to its "sister" table
--
CREATE TABLE [dbo].[TableWithJustSystemUpdatesThisField] (
[IdentiyField] [int] NOT NULL
CONSTRAINT [PK_TableWithBothFields] PRIMARY KEY CLUSTERED
CONSTRAINT
FK_TableWithJustEditedByUserField_TableW
ithJustSystemUpdatesThisField
FOREIGN KEY REFERENCES [dbo].[TableWithJustEditedByUserField] (IdentiyField),
[SystemUpdatesThisField] [char] (10)
) ON [PRIMARY]
"mekim" wrote:
> Hi David,
> I could always expand my knowledge on this topic - and I certainly will be
> making a visit to the book store now. I appreciate you helping me!
> Best Regards,
> Mekim
> "David Portas" wrote:
>|||You are right that Mekim didn't declare the IDENTITY as a key. IDENTITY
itself is no guarantee of uniqueness. Even so, a UNIQUE or PRIMARY KEY
constraint ALSO needs to be declared for the table's natural key - that
was the more fundamental point that I was trying to make.
--
David Portas
SQL Server MVP
--|||The problem is, making an identity column as a key by itself does not
prevent the table from having duplicate EditedByUser values or
SystemUpdatesThis values or a combination of both. And without such a
restriction on duplicates, the OP's table is more or less meaningless to
begin with.
In this case, the identity column acts as an redundant identifier without
offering much for preserving entity integrity. If OP had analyzed his
business model sufficiently, he could have identified the unique attributes
in his table and could use that as a PRIMARY KEY or as a UNIQUE NOT NULL
constraint.
Anith