Help w/ unique identity

Not sure if I have the right term in my Subject, but I have written an
application using VB6, which stores employee transactions in a SQL database.
I had originally designed the app for independent use in a standalone PC in
our remote offices but was then asked to expand its use to our corporate
office and import the transactions from all the locations into one database.
I have an Identity column in my table labeled "TransactionID" with the
Identity Seed and Indentity Increment set to the default of 1 and
automatically increments. The TransactionID number ensures that each
transaction is unique because other pieces of data collected in the table
could be the same such as StoreNumber, EmployeeNumber, etc.
All this is working fine, but now I want to import the transactions from the
remote location(s) into the SQL database at the corporate office. Now, there
is the very real possibility that a TransactionID from any of the remote
locations will match. Plus, when I import the records into the corporate
database, won't the master database want to reassign it a TransactionID as
well?
Since the TransactionID is unique at each store/office level, I was thinking
of formatting the TransactionID to something like "1234.01" where "1234" is
the next row in the table and "01" is the store number. This would be
separated by a decimal point. The store number would never exceed 2 digits
and the corporate office could be "00" or "99".
Is this possible or is there a better way to do this? I need to preserve the
TransactionID from the store/office because I use it to create an audit
trail if a transaction is ever modified.
Thanks,
BarryYou can add StoreID to the table and keep current Identity column as it (int
).
Perayu
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I suggest you add another field in the table called store id
modify the unique constraint or the Pk to a composite (PK) or constraint
a composite pk is composed of at least two or more fields. In that way
you still preserve uniquesness of the data without violation atomicity of
the column
or better known as the domain integrity
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||> Since the TransactionID is unique at each store/office level, I was thinkingd">
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
I can not understand well because you are mixing names here.
My recommendation is to add a new column to the transaction table, to store
the [store] from where the transaccion was made. Do not use the formatting
approach (1234.01), then you will have to parse it into a select statement
and this will avoid sql server from considering it as a search argument.
Also, you can not create indexes based on a formula unless you create a
computed column and base the index on this column.
avoid this:
select c1,..., cn
from dbo.[transactions]
where right(transactionid, 2) = '01'
AMB
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I already have a StoreNumber column, so I guess I just need to find some
info on constructing a Composite Constraint. Any good links?
Thanks,
Barry
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> I suggest you add another field in the table called store id
> modify the unique constraint or the Pk to a composite (PK) or constraint
> a composite pk is composed of at least two or more fields. In that way
> you still preserve uniquesness of the data without violation atomicity of
> the column
> or better known as the domain integrity
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "BCS" wrote:
>
database.
in
database.
table
the
there
as
thinking
is
digits
the|||
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
>
You didn't mention, but I'm assuming that records in this table are only
inserted at the store level and are consolidated at the corporate level for
accounting and reporting purposes. The corporate data model can have the
same basic column layout as the store level model, but do not use the
identity property for TransactionID, becuase there is no need for it and
would create problem when importing.

> Since the TransactionID is unique at each store/office level, I was
> thinking
> of formatting the TransactionID to something like "1234.01" where "1234"
> is
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
>
Identity columns (in this case TransactionID) must be integers, and you
cannot alter their format.

> Is this possible or is there a better way to do this? I need to preserve
> the
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
>
The combination of TransactionID and StoreID would make each transaction
unique at the corporate level, and this would be a good candidate for the
primary key at both the store and corporate level. When your primary key is
an identoty key, you should attempt to identify a natural key (ex: StoreID,
RegisterID, TransactionDateTime) and place a unique constraint on it.

> Thanks,
> Barry
>|||He probably means that the primary key should be composed of more than 1
column. For example:
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreNumber],
[TransactionID]
)
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:rT_Oe.36020$es2.553871@.twister.southeast.rr.com...
>I already have a StoreNumber column, so I guess I just need to find some
> info on constructing a Composite Constraint. Any good links?
> Thanks,
> Barry
> <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> database.
> in
> database.
> table
> the
> there
> as
> thinking
> is
> digits
> the
>