Order Entry System

An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning
and such, is to either remove the item from inventory when it is placed in
the cart and set it to "promised" - not fully considering it sold until the
entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventor
y by the number of items that are sitting in abandoned carts on your site -
at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational t
o the customer and you don't attempt to keep that absolutely sync'd with you
r real unpromised inventory. I've seen sites where they do a final availabil
ity check at purchase time
(which is often on a separate server from the browsing server) and tell you
then that the item is no longer available. I think that is a pretty bad cust
omer experience, but you will need to strike the balance with your other bus
iness needs.
There are other techniques for dealing with available to promise, but they t
end to be more loosely coupled and that doesn't fit well when your inventory
per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order[vbcol=seagreen]
> out
> Any
>

Order Entry System

An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
Two things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>
|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time
(which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs.
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.
|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.[vbcol=seagreen]
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order
> out
> Any
>

Order Entry System

An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete - credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time (which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > An online shopping store with a lot of products... Unfortunately the
> number
> > of products to be sold are limited.. kind of in an inventory...
> >
> > I could have millions of customers logging on someday ... How can I
scale
> > this for concurrency and also ensure that data is consistent and i do
not
> > oversell the products that are not in the inventory..thats assuming i
show
> > all the customers all the products available and everyone decides to
order
> > it which i would not be able to control. I can imagine a product catalog
> > going thru some sort of a replication architecture for concurrency/scale
> out
> > purposes as data is static. How does one do it for an inventory system..
> Any
> > suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
> >
> >
>

Order by Clause

Hi,
I have column with values January, February and so on. I need to perform
sort based on months instead the system sorts it by Alphabetical Order. Any
Hint?
Thanks
MannyManny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny
How about storing dates as DATETIME / SMALLDATETIME rather than
strings? If it's too late to do that then you can try:
SELECT mth
FROM tbl
ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
David Portas
SQL Server MVP
--|||do you not have a real date? if not, then do you at least have a year as
well? if not, sorting my month number is pretty meaningless. (e.g. Jan
05 comes after Dec 04).
Manny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny|||Manny Chohan wrote:

> I have column with values January, February and so on. I need to
> perform sort based on months instead the system sorts it by
> Alphabetical Order. Any Hint?
Create a table with months:
Table Months
ID int NOT NULL,
MonthName varchar(20)
and do a join on that. Normally it would be better to save the month
ID instead of the full text.
HTH,
Stijn Verrept.|||... order by
charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Augus
t*September*October*November*December*')
Steve Kass
Drew University
Manny Chohan wrote:

>Hi,
>I have column with values January, February and so on. I need to perform
>sort based on months instead the system sorts it by Alphabetical Order. Any
>Hint?
>Thanks
>Manny
>|||Thanks. It worked.
"Steve Kass" wrote:

> ... order by
> charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Aug
ust*September*October*November*December*
')
> Steve Kass
> Drew University
> Manny Chohan wrote:
>
>|||I apologize for the off-topic comment, but this one is just too good to pass
:

> do you not have a real date?
How many times have I been asked that question - not necesarily in the same
context, but still... :)
ML
http://milambda.blogspot.com/|||David with all due respect, I'd suggest a datetime format that carries the
century. Yes, you specify the year 2000 but we should all be developing cod
e
that doesn't leave ambiguity because you never know what SQL Server defaults
will be in the future and more impportantly how your technique will be
applied to other situations.
CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
And, we should be thinking globally so it really should be:
CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
Just my two cents,
Joe
"David Portas" wrote:

> Manny Chohan wrote:
> How about storing dates as DATETIME / SMALLDATETIME rather than
> strings? If it's too late to do that then you can try:
> SELECT mth
> FROM tbl
> ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
> --
> David Portas
> SQL Server MVP
> --
>|||Joe from WI wrote:
> David with all due respect, I'd suggest a datetime format that carries the
> century. Yes, you specify the year 2000 but we should all be developing c
ode
> that doesn't leave ambiguity because you never know what SQL Server defaul
ts
> will be in the future and more impportantly how your technique will be
> applied to other situations.
> CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
>
A good point.

> And, we should be thinking globally so it really should be:
> CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
>
That does work although on the face of it the string is wrong. 112
defines the format as YYYYMMDD, which is not what you have specified.
There is some implict conversion at work here so I'd stick to the 101
version because the string that's passed complies with the documented
format and behaviour for CONVERT.
David Portas
SQL Server MVP
--sql

Oracle 9i Client on SQL Server 2000 Ent.

I installed Oracle 9i client on a SQL Server 2000
Enterprise Edition server with Windows 2000 Advanced
Server operating system. Next, I opened the linked server
and lastly I tried to open the Microsoft OLE DB Provider
for SQL Server which caused the server to hang and had to
be rebooted.
Please help me with what could cause this problem?
Thank You,
MikeHonestly I don't know the answer, so I can just throw some
ideas:
I assume it was working fine before you installed the
Oracle 9i client. Just a crazy though, try to reboot your
server couple times and see if that fixes the problem.
If it still doesn't work then remove the Oracle 9i client
and see what happens.
hth.
>--Original Message--
>I installed Oracle 9i client on a SQL Server 2000
>Enterprise Edition server with Windows 2000 Advanced
>Server operating system. Next, I opened the linked
server
>and lastly I tried to open the Microsoft OLE DB Provider
>for SQL Server which caused the server to hang and had to
>be rebooted.
>Please help me with what could cause this problem?
>Thank You,
>Mike
>.
>