Re: How ro append characetrs to auto-incrementing number

[Aaron Bertrand [SQL Server MVP]]

If TTK- prepends every unique value in the column, then it is no more unique
than the number itself. Think about it.

Anyway, the way to do this and to needlessly use up disk space so you can
fool yourself into believing you have a better key is to use a computed
field:

CREATE TABLE dbo.Orders
(
OrderNo INT IDENTITY(1,1),
...,
FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
);

Or a view:

CREATE VIEW dbo.View_Orders
AS
SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
FROM dbo.Orders;

"pmud" <pm...@discussions.microsoft.com> wrote in message
news:6D76A707-E2A2-46C1...@microsoft.com...
> Hi,
>
> I am creating a new table. in the table, I want to have a field called
> OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
> value will be the primary key and thus unique for thr record. How do I
> append
> 'TTK-' to an autoincrementing number? Do I have to use a trigger which
> automatically appends 'TTk-' to the auto-incrementing number as soon as it
> is
> inserted in the databse? If so, what will be the code for that trigger? Or
> is
> there a better way to do it.
>
> Please help.
>
> Thanks
> --
> pmud

[pmud]

Hi Aaron,

We have orders for differnt companies in different tables. The primary
reason for doing this is that we want to allocate a separate chunk of order
nu,bers for each company rather than haveing Order no 10 for Comapny1 and 11
for Company2. So to aviod jumping numbers, we broke orders into different
tables based on teh company.

Now we want to prefix each Order no, in each table with the first 3
characters of the company name. For this, will the following trigger work on
each of the tables:

CREATE TRIGGER IDENTITY_TRIGGER
ON INSERT
AS
{
UPDATE TABLE1
SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
}

Please let me know if there is any mistake I have made. Also, please let me
know if you have any better quick ideas.

Thanks
--
pmud

[Mike Hodgson]

Warning, warning!  I've got alarm bells going off in my head.

You just said you're using 1 column to store 2 facts.  This is a really bad design.  If you've got 2 facts (OrderNo & CompanyCode) then store them in 2 different columns.  You table should look something like:

create table dbo.Orders
(
    OrderNo int identity(1,1) primary key,
    CompanyCode char(3) not null,
    ...,
    CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as varchar(15)),
    ...,
);

Use the OrderNo as your unique primary key, store the company code in a separate column and if you want to combine those 2 facts then do so in a computed column.  Don't mix the 2 values in a single materialised column, it'll just cause you headaches in the long term.

--
mike hodgson
http://sqlnerd.blogspot.com

[Erland Sommarskog]

Aaron Bertrand [SQL Server MVP] (ten...@dnartreb.noraa) writes:
> If TTK- prepends every unique value in the column, then it is no more
> unique than the number itself. Think about it.
>
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
>
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );

To add to Aaron's post: if you need to, you can index the computed
column.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

pmud (pm...@discussions.microsoft.com) writes:
> We have orders for differnt companies in different tables. The primary
> reason for doing this is that we want to allocate a separate chunk of
> order nu,bers for each company rather than haveing Order no 10 for
> Comapny1 and 11 for Company2. So to aviod jumping numbers, we broke
> orders into different tables based on teh company.

You did what?

It's alright of wanting to have separate order numbers per company, but
you're criminal if you split the table into one per company only be able
to use IDENTITY for the order number. The normal key for the table would
be (company_id, order_id). "But then I can't use auto-increment?" So,
don't use auto-increment then. Rolling your own incrementing number is no
big deal:

BEGIN TRANSACTION

SELECT @orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @company

INSERT orders (company, orderid, ...
VALUES (@company, @orderid, ...

...

COMMIT TRANSACTION

And I suspect that if you want one series per company, you want it to
be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
and not 1, 5, 7 which is what you could get if you use IDENTITY.

I will have to add one caveat: the chief reason to use IDENTITY is
not simplicity of proramming, but scalability. In a high-transaction
environment where hundreds of rows are inserted by minute, IDENTITY
may be essential to avoid conentention. But somehow, I don't expect
the order rate for a single company be that high.

[pmud]

Hi Mike,

You said that it was criminal to break out tables into different orders
table per company. What do you think are the flaws in this approach? I know
that it is not a good design but what are the exact resons?

Thanks
--
pmud

"Mike Hodgson" wrote:

> Warning, warning! I've got alarm bells going off in my head.
>
> You just said you're using 1 column to store 2 facts. This is a really
> bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
> them in 2 different columns. You table should look something like:
>
> create table dbo.Orders
> (
> OrderNo int identity(1,1) primary key,
> CompanyCode char(3) not null,
> ...,
> CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
> varchar(15)),
> ...,
> );
>
> Use the OrderNo as your unique primary key, store the company code in a
> separate column and if you want to combine those 2 facts then do so in a
> computed column. Don't mix the 2 values in a single materialised
> column, it'll just cause you headaches in the long term.
>
> --

> *mike hodgson*

[pmud]

Hi Earl,

What are the flaws in this structure?

Thanks
--
pmud

[pmud]

Hi Earl,

I am not very sure about transactions. Do I have to write teh code for
transactions in trigger window? Also, with the approach you mentioned, will
it be possible to have contiguous order nos for each company?

If so, then that will be great.

Thanks for your help..
--
pmud

[Mike Hodgson]

I didn't say it was criminal, Erland did.

But I agree that it's a bad idea to have 1 table per company - if you add another company you'd have to change the schema of the DB rather than simply inserting a row in a table.  As a matter of course, you shouldn't be changing the schema of production databases regularly simply because you have new data (that has already been defined, eg. an order).  Also this design would make querying across multiple companies much more complicated because you'd need to use joins or union operators to combine the sets of data - yuk!  To automate queries like this at runtime you'd need to use dynamic SQL as the structure of your queries would change each time you add a new company - imagine how ugly that would quickly become.  There are plenty of other things wrong with this approach if you just think ahead a year or two and use you imagination a bit.

When I initially posted in this thread I'd missed the bit where you said you wanted to store orders specific to a company in company-specific tables.  I just thought you wanted to have 1 orders table and keep all you orders in there.  My main objection to your design was storing 2 facts in a single column.  Why would you do that?  If you store 2 facts, why not 3, or 4 or 5 or 100?  Imagine how hard it would be to write queries against a table with a single varchar(8000) column that stored an OrderNo, a CompanyCode, a OrderDate, a ShippingDate, a DeliveryAddress, a ContactName, etc., etc.

The basic premise behind relational database theory (very much simplified) is that a table represents a single entity or relationship (this is in conflict with the idea of multiple orders tables to store orders) and a column in a table represents a single fact or attribute of that entity (this is in conflict with combining multiple attributes in a single column).  These are just the fundamental tenets of this area of theory.  (Oh no, I'm starting to sound a little like Celko - LOL!)  Chris Date, Fabian Pascal or E.F. Codd could give you a much better explanation about relational theory than I could.  If you're really interested in the theory I recommend reading one or more of their books (An Introduction to Database Systems by Chris Date is probably a good starting point or further to that his latest book Database in Depth).

--
mike hodgson
http://sqlnerd.blogspot.com

[--CELKO--]

>> We have orders for differnt companies in different tables. The primary reason for doing this is that we want to allocate a separate chunk of order numbers for each company rather than haveing Order no 10 for Comapny1 and 11 for Company2. So to aviod jumping numbers, we broke orders into differenttables based on the company. <<

Boy, that is soooo wrong!! Have you even once considered a proper
relational design? Orders are orders, period. That design flaw is
call "attribute splitting" -- Google it.

If you want to make the company part of the order number, that is fine.
Go to HOME DEPOT o rany chain store and look at a cash register slip
-- store number, register number, and the slip number identify each
purchase. Ther is usually a redudant timestamp.

Start over; what you have done so far is WRONG!

[Erland Sommarskog]

pmud (pm...@discussions.microsoft.com) writes:
> I am not very sure about transactions. Do I have to write teh code for
> transactions in trigger window?

If you are not sure about transactions, you have a lot to learn!
Understanding transactions is essential for implmenting database solutions
and maintaining integrity of the data.

The code that I showed

BEGIN TRANSACTION

SELECT @orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @company

INSERT orders (company, orderid, ...
VALUES (@company, @orderid, ...

...

COMMIT TRANSACTION

is something you would write in a stored procedure.

Note that the transaction is not only needed to get the order number
right. Presumably there is also an OrderDetails table that you need to
fill. And you don't want the order to be store incompletely in the
database, do you?

Speaking of OrderDetails, that table should have a foreign-key constraint
to the Orders table. How is that going to work out if you have one order
table per company? One OrderDetails per company?

Overall, having one table per company is going to lead an increase of
complexity in development seriously.

> Also, with the approach you mentioned,
> will it be possible to have contiguous order nos for each company?

The above is a necessrity to have contiguos order numbers. If you
requirement is contiguous numbers, you *cannot* use autonumber. The
IDENTITY feature is *designed* to not give contiguous numbers.

[pmud]

Hi All,

the way I have currently designed the databse is relational only and I know
that it is wrong to create a table per company. But this design was suggested
someone who is non technical and wants it to be implemented this way , at
least for one company for which we have to do a project now for the sake of
time.I just knew that it is a bad design but didnt have enough reasons to
give an argument.
Now I have enough reasons. :)

Also, another reason he suggested this was that each company might want
different fields which they want in thier order and we needed to import thier
orders from a spreadsheet ; and importing orders to multipe tables is
difficult since customer info is in a separate table, orders in a separate,
order details is in a separated table, status info is in a separate table and
so on...

So these were the reasons of breaking it out by company. Also, we had not
thought of combining 2 fields into 1. CompanY_Name will still be in a
separate field. We just wanted to prepend 'TTK-' or 'WYT-' or some other
character in the order no field, so that if a user calls in and says my order
no is 'TTK-66', the customer service immediately knows what order is the
customer talking about.

We now have to weigh all these factors ( the biggest being time and
importing complexity ) with the complexity of the wong Db design..

Thanks for all your help.
--
pmud

[Erland Sommarskog]

pmud (pm...@discussions.microsoft.com) writes:
> Also, another reason he suggested this was that each company might want
> different fields which they want in thier order and we needed to import
> thier orders from a spreadsheet ;

Now, that is a little more relevant, although hardly enough to warrant
one table per company.

But let me immediately say that since I don't know the business, it is
difficult to say for sure. For instance, if there are so much differences
from company to company, that all will require their own processing code,
then having multiple tables is not that much extra headache.

But presumably, you only want to the import phase to be specific, and
then bring the data into a common track. Customer-specific fields is
always a bit of a headache. If you are on SQL 2005, you could consider
to put such overflow information in an XML column. It depends a bit on
what you are to use the information to.

> and importing orders to multipe tables is difficult since customer info
> is in a separate table, orders in a separate, order details is in a
> separated table, status info is in a separate table and so on...

Not sure what you are saying here. I hope you are not saying that importing
into many tables is difficult, so will import all into one table. (Well, it
may be a good idea to import into a staging-table and then explode the
data from there. The staging table might specific for the format of the
customer's spreadsheet.)

By the way, in this day and age, you get orders by spreadsheet? Wouldn't
it better to have customers send XML?

[pmud]

Hi Erland,

Thanks for the response. I am not very proficient with XML for importing the
data. I just know the basic syntax of XML. But I am not sure how would we
import data from the XML file to different tables in the database. Would you
suggest quick reading some books from where we can get an idea about it?

Also, if we import the data into a staging table, we will have to write
triggers which will populate the right data to the right table automatically.
I know this is better than creating 1 table per company and then managing
each of theose tables as a separate entity. But I guess our business is
complex enough that we have to go with the wrong deign for now.

Thanks again.
--
pmud

[Aaron Bertrand [SQL Server MVP]]

> But I guess our business is
> complex enough that we have to go with the wrong deign for now.

I hope you haven't already convinced yourself that your business will get
less complex, and/or that it will become easier over time to undo this bad
design.

[Erland Sommarskog]

pmud (pm...@discussions.microsoft.com) writes:
> Thanks for the response. I am not very proficient with XML for importing
> the data. I just know the basic syntax of XML. But I am not sure how
> would we import data from the XML file to different tables in the
> database. Would you suggest quick reading some books from where we can
> get an idea about it?

Greame Malcolm has a book on Microsoft Press about using XML with SQL 2000.
It's a bit outdated today, since the XML support in SQL 2005 is much
improved.

By the way, I believe many business-to-business solutions (which I assume
that you are into) use BizTalk, another product from Microsoft. Malcolm
covers BizTalk a bit in his book. Myself, I've never worked with it.

Obviously, going into XML will require your customer change their processes,
so this a far bigger step to take than making a good table design.

> Also, if we import the data into a staging table, we will have to write
> triggers which will populate the right data to the right table
> automatically.

Probably not triggers, but rather stored procedures.

> I know this is better than creating 1 table per company and then managing
> each of theose tables as a separate entity. But I guess our business is
> complex enough that we have to go with the wrong deign for now.

Tip: if you don't make it right now, it will be more difficult to make
it right later.

[Stu]

I had to snicker a bit at the following:

<quote>

By the way, I believe many business-to-business solutions (which I
assume
that you are into) use BizTalk, another product from Microsoft. Malcolm
covers BizTalk a bit in his book. Myself, I've never worked with it.

</quote>

Telling a self-professed SQL beginner to go look at BizTalk is a bit
like telling my 6 year old daughter to go read War and Peace over the
weekend. It's just mean. <G>

Stu

[Erland Sommarskog]

Stu (stuart.a...@gmail.com) writes:
> Telling a self-professed SQL beginner to go look at BizTalk is a bit
> like telling my 6 year old daughter to go read War and Peace over the
> weekend. It's just mean. <G>

Hey, I have neither read War and Peace, nor have I ever seen BizTalk. If
I was evil, that wasn't on purpose!