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