when user leave's the application for while connection to server stops
and data shown in form is #deleted
how to prevent it?
sam
You didn't indicate what application you are using or how
you are accessing the SQL Server data.
But it can be caused by several things - generally related
to indexing - such as using a float as the index or as part
of the index or having nulls as values in part of the index.
There were similar to this when using Bigints with some apps
which wouldn't map the data type correctly. That's generally
fixed in service packs by now depending on what you are
using. If you are using Access, make sure you have the
latest Jet service pack.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step (floats can confuse it as floats are
approximate data types), it will assume the record has been
deleted.
-Sue
On Mon, 12 Dec 2005 12:33:35 +0200, "Sam"
<focus10@.zahav.net.il> wrote:
>when user leave's the application for while connection to server stops
>and data shown in form is #deleted
>how to prevent it?
>sam
>
|||In addition to Sue's comments, if you are using Access, you should add a
timestamp field to all of your Access linked tables. Access uses the
timestamp field to determine if the data has changed since it was last
fetched.
"Sam" <focus10@.zahav.net.il> wrote in message
news:O2mNQew$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> when user leave's the application for while connection to server stops
> and data shown in form is #deleted
> how to prevent it?
> sam
>
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Sunday, March 25, 2012
data shown in form is #deleted
when user leave's the application for while connection to server stops
and data shown in form is #deleted
how to prevent it?
samYou didn't indicate what application you are using or how
you are accessing the SQL Server data.
But it can be caused by several things - generally related
to indexing - such as using a float as the index or as part
of the index or having nulls as values in part of the index.
There were similar to this when using Bigints with some apps
which wouldn't map the data type correctly. That's generally
fixed in service packs by now depending on what you are
using. If you are using Access, make sure you have the
latest Jet service pack.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step (floats can confuse it as floats are
approximate data types), it will assume the record has been
deleted.
-Sue
On Mon, 12 Dec 2005 12:33:35 +0200, "Sam"
<focus10@.zahav.net.il> wrote:
>when user leave's the application for while connection to server stops
>and data shown in form is #deleted
>how to prevent it?
>sam
>|||In addition to Sue's comments, if you are using Access, you should add a
timestamp field to all of your Access linked tables. Access uses the
timestamp field to determine if the data has changed since it was last
fetched.
"Sam" <focus10@.zahav.net.il> wrote in message
news:O2mNQew$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> when user leave's the application for while connection to server stops
> and data shown in form is #deleted
> how to prevent it?
> sam
>
and data shown in form is #deleted
how to prevent it?
samYou didn't indicate what application you are using or how
you are accessing the SQL Server data.
But it can be caused by several things - generally related
to indexing - such as using a float as the index or as part
of the index or having nulls as values in part of the index.
There were similar to this when using Bigints with some apps
which wouldn't map the data type correctly. That's generally
fixed in service packs by now depending on what you are
using. If you are using Access, make sure you have the
latest Jet service pack.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step (floats can confuse it as floats are
approximate data types), it will assume the record has been
deleted.
-Sue
On Mon, 12 Dec 2005 12:33:35 +0200, "Sam"
<focus10@.zahav.net.il> wrote:
>when user leave's the application for while connection to server stops
>and data shown in form is #deleted
>how to prevent it?
>sam
>|||In addition to Sue's comments, if you are using Access, you should add a
timestamp field to all of your Access linked tables. Access uses the
timestamp field to determine if the data has changed since it was last
fetched.
"Sam" <focus10@.zahav.net.il> wrote in message
news:O2mNQew$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> when user leave's the application for while connection to server stops
> and data shown in form is #deleted
> how to prevent it?
> sam
>
Thursday, March 22, 2012
Data Retrieval Terribly Slow
Hi,
I'm using ASP.NET 1.1, SQL Server 2000 Server:
I followed the ASP.NET 1.1 Starter Kit's Commerce application and applied the same principles it had written the code to retrieve data to my web application I created. For example I've written this Function in a class to return a sqldatareader:
Public
Function GetAdvanceSearch(ByVal sAsString,ByVal ExtAsInteger,ByVal fdateAs DateTime,ByVal tdateAs DateTime)As SqlDataReaderDim oDrAdSearchAs SqlDataReaderDim oCmdGetSearchAsNew SqlCommand("spAdvanceSearch", oComConn)With oCmdGetSearch
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@.DialNo", SqlDbType.VarChar)).Value = s
.Parameters.Add(New SqlParameter("@.FDate", SqlDbType.DateTime)).Value = fdate
.Parameters.Add(New SqlParameter("@.TDate", SqlDbType.DateTime)).Value = tdate
.Parameters.Add(New SqlParameter("@.Ext", SqlDbType.Int)).Value = Ext
EndWith
oComConn.Open()
oDrAdSearch = oCmdGetSearch.ExecuteReader(CommandBehavior.CloseConnection)
Return oDrAdSearch
Else
ReturnNothing
EndIfEndFunction
And When I'm calling this function I do write in this way (assuming that this function is in a class called "Calls"):
Dim objCalls as New Calls
DataGrid1.DataSource = objCalls.GetAdvanceSearch(<PARAMS......>)
DataGrid1.Databind
My application is a Telephone Call Recording System and could expect vast amount of data. Averagely, a month may produce approximately 50,000 records or more. So while querying through my web application for a month, the application itself either gets stuck or the retrieval speed gets drastically slow. However I'm using Datareaders for every querying scenario. My Web application is hosted in a Windows 2000 Server and accessed via Local Network or IntraNet.
What are the ways I could make this retrieval more speedier and efficient? I would like to hear from anyone who have come across this problem and anyone who could help me on this.
Thanks in Advance. Looking forward for a reply from some one.
Have you narrowed down the bottleneck? Is it the actual stored procedure that is slowing things down, or your data access code?|||
Hi Morton,
Firstly, thanks for the reply. Infact, along with this stored procedure and several others written the same way as I had written earlier in the post is causing the slowness or getting stuck. I couldn't figure out why when querying about 50,000 or more records it gets slow or gets stuck, or this Error page appears:
Server Application Unavailable
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
Administrator Note:An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
Atleast I couldn't query 10,000 records, however lesser than that...it takes little time to upload. Pls. help me on this.
Thanks & Regards,
|||Hi Hifni,
Did you check the sql statements with in the stored procedurespAdvanceSearch?
Because generally the select statements for searches may use different execution plans for different where conditions so they use different indexes. So it is better to be sure that the necessary indexes exist over the related columns. You can also update the statistics of the database if you have recently a bulk load on related tables.
One important point is if you use a complex sp, when it is compiled by the sql engine it will work with the compiled execution plan afterwards.
So if you should change the sp to use sp_executeSQL to create and use execution plans each time the sp is called. It may help to reduce period of the execution.
Eralper
http://www.kodyaz.com
Tuesday, March 20, 2012
Data Reinitialized When Filtered Column Changes
Hello,
We have SQL2005 Server, SQL Mobile 2005 CF2.0 application. It seems that
when we mark Orders as complete on the server, it redownloads all
OrderDetails from the server down to the PDA, even though there are no
changes to this table on the server, and there are changes on the PDA that
should get uploaded.
We have 2 tables Orders and OrderDetails. We want only non-complete Orders
and their OrderDetails to filter down to the pda.
So we have a base filter setup on our Orders table like this:
SELECT <published_columns> FROM
[dbo].[Orders] WHERE [Orders].[Completed] = 0 AND
[Orders].[OwnerContactID]=HOST_NAME()
Then we add a join filter to get OrderDetails for those Orders:
SELECT <published_columns> FROM [dbo].[Orders] INNER JOIN
[dbo].[OrderDetails] ON [Orders].[OrderID] = [OrderDetails].[OrderID]
Everthing works great most of the time, the problem is, when someone on the
server marks an Order as Complete, but there are still changes to
OrderDetails on the PDA, it generates conflict on OrderDetails. It does try
to upload from the PDA for OrderDetails, but they are filtered into a
conflict table, and the server wins. The OrderDetails table has not been
touched on the server, so I don't understand this. It's like the server
"sets" all the columns in OrderDetails when Orders.Completed is changed to
TRUE, and then the changes from the PDA are regarded as a conflict.
I don't think I understand how this works, and there probably is a reason is
for this. Any help or suggestions of a better way to do this, filter
Non-Complete items down without reinitializing the child data when that
filtered data changes, please let me know!
Thanks!
Are you using column level tracking? It seems that with column level
tracking this should work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nkleinjan" <nkleinjan@.discussions.microsoft.com> wrote in message
news:07D5DD6F-F0DF-4B4F-B1FB-C7CC3DE4F7D3@.microsoft.com...
> Hello,
> We have SQL2005 Server, SQL Mobile 2005 CF2.0 application. It seems that
> when we mark Orders as complete on the server, it redownloads all
> OrderDetails from the server down to the PDA, even though there are no
> changes to this table on the server, and there are changes on the PDA that
> should get uploaded.
> We have 2 tables Orders and OrderDetails. We want only non-complete Orders
> and their OrderDetails to filter down to the pda.
> So we have a base filter setup on our Orders table like this:
> SELECT <published_columns> FROM
> [dbo].[Orders] WHERE [Orders].[Completed] = 0 AND
> [Orders].[OwnerContactID]=HOST_NAME()
> Then we add a join filter to get OrderDetails for those Orders:
> SELECT <published_columns> FROM [dbo].[Orders] INNER JOIN
> [dbo].[OrderDetails] ON [Orders].[OrderID] = [OrderDetails].[OrderID]
> Everthing works great most of the time, the problem is, when someone on
> the
> server marks an Order as Complete, but there are still changes to
> OrderDetails on the PDA, it generates conflict on OrderDetails. It does
> try
> to upload from the PDA for OrderDetails, but they are filtered into a
> conflict table, and the server wins. The OrderDetails table has not been
> touched on the server, so I don't understand this. It's like the server
> "sets" all the columns in OrderDetails when Orders.Completed is changed to
> TRUE, and then the changes from the PDA are regarded as a conflict.
> I don't think I understand how this works, and there probably is a reason
> is
> for this. Any help or suggestions of a better way to do this, filter
> Non-Complete items down without reinitializing the child data when that
> filtered data changes, please let me know!
> Thanks!
>
>
We have SQL2005 Server, SQL Mobile 2005 CF2.0 application. It seems that
when we mark Orders as complete on the server, it redownloads all
OrderDetails from the server down to the PDA, even though there are no
changes to this table on the server, and there are changes on the PDA that
should get uploaded.
We have 2 tables Orders and OrderDetails. We want only non-complete Orders
and their OrderDetails to filter down to the pda.
So we have a base filter setup on our Orders table like this:
SELECT <published_columns> FROM
[dbo].[Orders] WHERE [Orders].[Completed] = 0 AND
[Orders].[OwnerContactID]=HOST_NAME()
Then we add a join filter to get OrderDetails for those Orders:
SELECT <published_columns> FROM [dbo].[Orders] INNER JOIN
[dbo].[OrderDetails] ON [Orders].[OrderID] = [OrderDetails].[OrderID]
Everthing works great most of the time, the problem is, when someone on the
server marks an Order as Complete, but there are still changes to
OrderDetails on the PDA, it generates conflict on OrderDetails. It does try
to upload from the PDA for OrderDetails, but they are filtered into a
conflict table, and the server wins. The OrderDetails table has not been
touched on the server, so I don't understand this. It's like the server
"sets" all the columns in OrderDetails when Orders.Completed is changed to
TRUE, and then the changes from the PDA are regarded as a conflict.
I don't think I understand how this works, and there probably is a reason is
for this. Any help or suggestions of a better way to do this, filter
Non-Complete items down without reinitializing the child data when that
filtered data changes, please let me know!
Thanks!
Are you using column level tracking? It seems that with column level
tracking this should work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nkleinjan" <nkleinjan@.discussions.microsoft.com> wrote in message
news:07D5DD6F-F0DF-4B4F-B1FB-C7CC3DE4F7D3@.microsoft.com...
> Hello,
> We have SQL2005 Server, SQL Mobile 2005 CF2.0 application. It seems that
> when we mark Orders as complete on the server, it redownloads all
> OrderDetails from the server down to the PDA, even though there are no
> changes to this table on the server, and there are changes on the PDA that
> should get uploaded.
> We have 2 tables Orders and OrderDetails. We want only non-complete Orders
> and their OrderDetails to filter down to the pda.
> So we have a base filter setup on our Orders table like this:
> SELECT <published_columns> FROM
> [dbo].[Orders] WHERE [Orders].[Completed] = 0 AND
> [Orders].[OwnerContactID]=HOST_NAME()
> Then we add a join filter to get OrderDetails for those Orders:
> SELECT <published_columns> FROM [dbo].[Orders] INNER JOIN
> [dbo].[OrderDetails] ON [Orders].[OrderID] = [OrderDetails].[OrderID]
> Everthing works great most of the time, the problem is, when someone on
> the
> server marks an Order as Complete, but there are still changes to
> OrderDetails on the PDA, it generates conflict on OrderDetails. It does
> try
> to upload from the PDA for OrderDetails, but they are filtered into a
> conflict table, and the server wins. The OrderDetails table has not been
> touched on the server, so I don't understand this. It's like the server
> "sets" all the columns in OrderDetails when Orders.Completed is changed to
> TRUE, and then the changes from the PDA are regarded as a conflict.
> I don't think I understand how this works, and there probably is a reason
> is
> for this. Any help or suggestions of a better way to do this, filter
> Non-Complete items down without reinitializing the child data when that
> filtered data changes, please let me know!
> Thanks!
>
>
data recovery - pls help
Hi,
If i have some data on a table that's being deleted by an application and i
did not do backup but the recovery mode is "Full Recovery" can i somehow get
back the data ?
i know there's a deleted/ (created or updated) table but that's wtithin a
session isn't it ?
appreciate any advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
hi,
maxzsim via droptable.com wrote:
> Hi,
> If i have some data on a table that's being deleted by an
> application and i did not do backup but the recovery mode is "Full
> Recovery" can i somehow get back the data ?
> i know there's a deleted/ (created or updated) table but that's
> wtithin a session isn't it ?
> appreciate any advise
> tks & rdgs
unfortunately there's no native way... but you can have a look at
http://www.aspfaq.com/show.asp?id=2449 for some tools that enable that kind
of stuff..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
tks for the link that you have provided
rdgs
Andrea Montanari wrote:
>hi,
>[quoted text clipped - 8 lines]
>unfortunately there's no native way... but you can have a look at
>http://www.aspfaq.com/show.asp?id=2449 for some tools that enable that kind
>of stuff..
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
If i have some data on a table that's being deleted by an application and i
did not do backup but the recovery mode is "Full Recovery" can i somehow get
back the data ?
i know there's a deleted/ (created or updated) table but that's wtithin a
session isn't it ?
appreciate any advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
hi,
maxzsim via droptable.com wrote:
> Hi,
> If i have some data on a table that's being deleted by an
> application and i did not do backup but the recovery mode is "Full
> Recovery" can i somehow get back the data ?
> i know there's a deleted/ (created or updated) table but that's
> wtithin a session isn't it ?
> appreciate any advise
> tks & rdgs
unfortunately there's no native way... but you can have a look at
http://www.aspfaq.com/show.asp?id=2449 for some tools that enable that kind
of stuff..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
tks for the link that you have provided
rdgs
Andrea Montanari wrote:
>hi,
>[quoted text clipped - 8 lines]
>unfortunately there's no native way... but you can have a look at
>http://www.aspfaq.com/show.asp?id=2449 for some tools that enable that kind
>of stuff..
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
Thursday, March 8, 2012
Data Model for Application Inventory
Can someone help me provide a logical E-R model of the different entities
with relationship for collecting Application Inventory ?
The goal is to collect all the Applications along with dependencies.
On the top of my head, I can think of a Server/Hardware Table, an
ApplicationName Table that would appear to be many to many i.e One Server
could host multiple apps and one App could be hosted on multiple servers.
Anyways, I am sure you know what Im talking about. But can someone help me
with all the different tables,etc. that could also include location of
servers, type of servers, type of apps, application dependencies, database
dependencies,etc.
ThanksPublic forums are not the right avenue to get this information since your
conceptual model & business rules are mostly transparent to others here.
If you are ill-equipped to develop and E-R model, the right approach is to
hire a qualified professional who can have direct access to your business
model.
Anith|||Anith,
This is just for my own collection of application inventory and not to be
consumed by the entire company. I was just looking for a very conceptual
model of the different entities with some common attributes. I dont think I
would need to hire an external consultant for this. Just want to get a start
on where to begin..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23wKTNzIVHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Public forums are not the right avenue to get this information since your
> conceptual model & business rules are mostly transparent to others here.
> If you are ill-equipped to develop and E-R model, the right approach is to
> hire a qualified professional who can have direct access to your business
> model.
> --
> Anith
>|||Not sure if the point is missed, but hardly anyone here is familiar with
your conceptual model other than you. A general advice that you receive here
may be based on what others perceive as the problem and may not be the
actual design
problem, not to mention the chances of misunderstanding and
misinterpretation being very high. That is why newsgroup responses cannot
substantively contribute to your requirement.
Different organizations categorize and document their applications
differently. Some consider cataloging all aspects of the existing
applications while some just documents their names with perhaps say, usable
acronyms. Some companies introduce a temporal element to document the
current or historical status of applications used by them.
Even if you are doing it for personal use, I have no idea of your
expectations are. A google search returned a few shareware downloads that do
this sort of thing. I don't know what your application inventory would be
like. Perhaps someone who is familiar with developing it might be able to
help you with this.
Anith
with relationship for collecting Application Inventory ?
The goal is to collect all the Applications along with dependencies.
On the top of my head, I can think of a Server/Hardware Table, an
ApplicationName Table that would appear to be many to many i.e One Server
could host multiple apps and one App could be hosted on multiple servers.
Anyways, I am sure you know what Im talking about. But can someone help me
with all the different tables,etc. that could also include location of
servers, type of servers, type of apps, application dependencies, database
dependencies,etc.
ThanksPublic forums are not the right avenue to get this information since your
conceptual model & business rules are mostly transparent to others here.
If you are ill-equipped to develop and E-R model, the right approach is to
hire a qualified professional who can have direct access to your business
model.
Anith|||Anith,
This is just for my own collection of application inventory and not to be
consumed by the entire company. I was just looking for a very conceptual
model of the different entities with some common attributes. I dont think I
would need to hire an external consultant for this. Just want to get a start
on where to begin..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23wKTNzIVHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Public forums are not the right avenue to get this information since your
> conceptual model & business rules are mostly transparent to others here.
> If you are ill-equipped to develop and E-R model, the right approach is to
> hire a qualified professional who can have direct access to your business
> model.
> --
> Anith
>|||Not sure if the point is missed, but hardly anyone here is familiar with
your conceptual model other than you. A general advice that you receive here
may be based on what others perceive as the problem and may not be the
actual design
problem, not to mention the chances of misunderstanding and
misinterpretation being very high. That is why newsgroup responses cannot
substantively contribute to your requirement.
Different organizations categorize and document their applications
differently. Some consider cataloging all aspects of the existing
applications while some just documents their names with perhaps say, usable
acronyms. Some companies introduce a temporal element to document the
current or historical status of applications used by them.
Even if you are doing it for personal use, I have no idea of your
expectations are. A google search returned a few shareware downloads that do
this sort of thing. I don't know what your application inventory would be
like. Perhaps someone who is familiar with developing it might be able to
help you with this.
Anith
Data Model for Application Inventory
Can someone help me provide a logical E-R model of the different entities
with relationship for collecting Application Inventory ?
The goal is to collect all the Applications along with dependencies.
On the top of my head, I can think of a Server/Hardware Table, an
ApplicationName Table that would appear to be many to many i.e One Server
could host multiple apps and one App could be hosted on multiple servers.
Anyways, I am sure you know what Im talking about. But can someone help me
with all the different tables,etc. that could also include location of
servers, type of servers, type of apps, application dependencies, database
dependencies,etc.
ThanksPublic forums are not the right avenue to get this information since your
conceptual model & business rules are mostly transparent to others here.
If you are ill-equipped to develop and E-R model, the right approach is to
hire a qualified professional who can have direct access to your business
model.
--
Anith|||Anith,
This is just for my own collection of application inventory and not to be
consumed by the entire company. I was just looking for a very conceptual
model of the different entities with some common attributes. I dont think I
would need to hire an external consultant for this. Just want to get a start
on where to begin..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23wKTNzIVHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Public forums are not the right avenue to get this information since your
> conceptual model & business rules are mostly transparent to others here.
> If you are ill-equipped to develop and E-R model, the right approach is to
> hire a qualified professional who can have direct access to your business
> model.
> --
> Anith
>|||Not sure if the point is missed, but hardly anyone here is familiar with
your conceptual model other than you. A general advice that you receive here
may be based on what others perceive as the problem and may not be the
actual design
problem, not to mention the chances of misunderstanding and
misinterpretation being very high. That is why newsgroup responses cannot
substantively contribute to your requirement.
Different organizations categorize and document their applications
differently. Some consider cataloging all aspects of the existing
applications while some just documents their names with perhaps say, usable
acronyms. Some companies introduce a temporal element to document the
current or historical status of applications used by them.
Even if you are doing it for personal use, I have no idea of your
expectations are. A google search returned a few shareware downloads that do
this sort of thing. I don't know what your application inventory would be
like. Perhaps someone who is familiar with developing it might be able to
help you with this.
--
Anith
with relationship for collecting Application Inventory ?
The goal is to collect all the Applications along with dependencies.
On the top of my head, I can think of a Server/Hardware Table, an
ApplicationName Table that would appear to be many to many i.e One Server
could host multiple apps and one App could be hosted on multiple servers.
Anyways, I am sure you know what Im talking about. But can someone help me
with all the different tables,etc. that could also include location of
servers, type of servers, type of apps, application dependencies, database
dependencies,etc.
ThanksPublic forums are not the right avenue to get this information since your
conceptual model & business rules are mostly transparent to others here.
If you are ill-equipped to develop and E-R model, the right approach is to
hire a qualified professional who can have direct access to your business
model.
--
Anith|||Anith,
This is just for my own collection of application inventory and not to be
consumed by the entire company. I was just looking for a very conceptual
model of the different entities with some common attributes. I dont think I
would need to hire an external consultant for this. Just want to get a start
on where to begin..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23wKTNzIVHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Public forums are not the right avenue to get this information since your
> conceptual model & business rules are mostly transparent to others here.
> If you are ill-equipped to develop and E-R model, the right approach is to
> hire a qualified professional who can have direct access to your business
> model.
> --
> Anith
>|||Not sure if the point is missed, but hardly anyone here is familiar with
your conceptual model other than you. A general advice that you receive here
may be based on what others perceive as the problem and may not be the
actual design
problem, not to mention the chances of misunderstanding and
misinterpretation being very high. That is why newsgroup responses cannot
substantively contribute to your requirement.
Different organizations categorize and document their applications
differently. Some consider cataloging all aspects of the existing
applications while some just documents their names with perhaps say, usable
acronyms. Some companies introduce a temporal element to document the
current or historical status of applications used by them.
Even if you are doing it for personal use, I have no idea of your
expectations are. A google search returned a few shareware downloads that do
this sort of thing. I don't know what your application inventory would be
like. Perhaps someone who is familiar with developing it might be able to
help you with this.
--
Anith
Data model for a web messaging application.
I need to develop an internal messaging sub-system that is similar to
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.
I am considering the following data model for the storage component,
and would appreciate community feedback:
Table layout for incoming and outgoing messages
************************************************
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
[RecipientID] [int] NOT NULL , // The userid ('Users'
Table)
[SenderID] [int] NOT NULL , // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL , // Only assigned if the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message */
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000),
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages =
log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-ItaiItai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||Uri, John and especially David! Thanks for the code review; I am now
one step further :)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google.com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further :)
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> > There is probably a
> > natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> > index may be useful, and a INT may not be sufficient for you messageid.
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John
Data model for a web messaging application.
I need to develop an internal messaging sub-system that is similar to
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table ? log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.
I am considering the following data model for the storage component,
and would appreciate community feedback:
Table layout for incoming and outgoing messages
************************************************
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
[RecipientID] [int] NOT NULL , // The userid ('Users'
Table)
[SenderID] [int] NOT NULL , // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL , // Only assigned if the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message */
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000),
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID = @.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID = @.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID = @.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID = @.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-ItaiItai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages => log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID => @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID => @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
>I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
See changes inline. . .
David
--Use Declarative referential integrity and cascading deletes
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[RecipientID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[SenderID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[GroupID] [uniqueidentifier] NULL ,
[SubmitDate] [smalldatetime] NOT NULL ,
[DeleteBySender] [bit] NOT NULL ,
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL ,
[Subject] [tinyint] NOT NULL ,
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message
change the error handling.
there was a mix of "nested transactions" and savepoints
which wouldn't really work.
Slso get rid of the @.out_errorcode parameter.
Any error will go to the client in a message anyway.
If you really need the error value to be returned to another
stored procedure, just return it as the return value of the
stored procedure.
*/
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
IF (@.@.error <> 0) goto eh
COMMIT TRANSACTION
RETURN 0
eh:
ROLLBACK TRANSACTION SendMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ReadMessage
Single value results should be returned in
output parameters instead of resultsets.
And only decrement log_NumberOfNewMessages the first time
*/
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_MessageText out varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION ReadMessageTrans
declare @.AlreadySeen bit
SELECT
@.OUT_MessageText = MessageText
@.AlreadySeen = SeenByRecipient
FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.AlreadySeen = 0)
BEGIN
UPDATE Messages SET
SeenByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION ReadMessageTrans
COMMIT TRANSACTION
RETURN 1
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
DECLARE @.DeleteBySender bit
DECLARE @.DeleteByRecipient bit
--do this in one query
SET
@.Recipient = RecipientID,
@.Sender = SenderID,
@.DeleteBySender = DeleteBySender
@.DeleteByRecipient = DeleteByRecipient
FROM Messages (updlock, holdlock)
WHERE MessageID = @.IN_MessageID
IF @.IN_DeleteOutgoingMessage = 1
BEGIN
if (@.DeleteByRecipient = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
IF (@.@.error <> 0) GOTO EH
END
ELSE --@.IN_IncomingMessage
BEGIN
if (@.DeleteBySender = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION DeleteMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages => log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID => @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID => @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||Uri, John and especially David! Thanks for the code review; I am now
one step further :)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google.com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further :)
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> > There is probably a
> > natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> > index may be useful, and a INT may not be sufficient for you messageid.
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table ? log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.
I am considering the following data model for the storage component,
and would appreciate community feedback:
Table layout for incoming and outgoing messages
************************************************
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
[RecipientID] [int] NOT NULL , // The userid ('Users'
Table)
[SenderID] [int] NOT NULL , // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL , // Only assigned if the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message */
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000),
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID = @.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID = @.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID = @.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID = @.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-ItaiItai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages => log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID => @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID => @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
>I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
See changes inline. . .
David
--Use Declarative referential integrity and cascading deletes
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[RecipientID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[SenderID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[GroupID] [uniqueidentifier] NULL ,
[SubmitDate] [smalldatetime] NOT NULL ,
[DeleteBySender] [bit] NOT NULL ,
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL ,
[Subject] [tinyint] NOT NULL ,
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message
change the error handling.
there was a mix of "nested transactions" and savepoints
which wouldn't really work.
Slso get rid of the @.out_errorcode parameter.
Any error will go to the client in a message anyway.
If you really need the error value to be returned to another
stored procedure, just return it as the return value of the
stored procedure.
*/
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
IF (@.@.error <> 0) goto eh
COMMIT TRANSACTION
RETURN 0
eh:
ROLLBACK TRANSACTION SendMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ReadMessage
Single value results should be returned in
output parameters instead of resultsets.
And only decrement log_NumberOfNewMessages the first time
*/
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_MessageText out varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION ReadMessageTrans
declare @.AlreadySeen bit
SELECT
@.OUT_MessageText = MessageText
@.AlreadySeen = SeenByRecipient
FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.AlreadySeen = 0)
BEGIN
UPDATE Messages SET
SeenByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION ReadMessageTrans
COMMIT TRANSACTION
RETURN 1
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
DECLARE @.DeleteBySender bit
DECLARE @.DeleteByRecipient bit
--do this in one query
SET
@.Recipient = RecipientID,
@.Sender = SenderID,
@.DeleteBySender = DeleteBySender
@.DeleteByRecipient = DeleteByRecipient
FROM Messages (updlock, holdlock)
WHERE MessageID = @.IN_MessageID
IF @.IN_DeleteOutgoingMessage = 1
BEGIN
if (@.DeleteByRecipient = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
IF (@.@.error <> 0) GOTO EH
END
ELSE --@.IN_IncomingMessage
BEGIN
if (@.DeleteBySender = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION DeleteMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.com...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages => log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID => @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID => @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID => @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID => @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai|||Uri, John and especially David! Thanks for the code review; I am now
one step further :)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google.com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further :)
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk>...
> > There is probably a
> > natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> > index may be useful, and a INT may not be sufficient for you messageid.
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John
Data model for a web messaging application.
I need to develop an internal messaging sub-system that is similar to
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.
I am considering the following data model for the storage component,
and would appreciate community feedback:
Table layout for incoming and outgoing messages
************************************************
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
[RecipientID] [int] NOT NULL , // The userid ('Users'
Table)
[SenderID] [int] NOT NULL , // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL , // Only assigned if the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message */
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000),
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages =
log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-Itai
Itai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai
|||1. MessageID serves as the PK
2. I am concerned with the overall design approach
tnx
-Itai
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Itai
I looked at your stored procedures
In the SendMessage stored procedure instead of update these tables I'd use
ON DELETE CASCADE ON UPDATE CASCADE when you design relationship between
users and messages tables
CREATE TABLE Messages_Users
(
[ID] INT NOT NULL PRIMARY KEY,
MessageId INT NOT NULL FOREIGN KEY REFERENCES Messages([MessageId ])ON
DELETE CASCADE ON UPDATE CASCADE,
UserId INT NOT NULL FOREIGN KEY REFERENCES Users([UserId ])ON DELETE
CASCADE ON UPDATE CASCADE,
......
......
)
"Itai Itai" <itaitai2003@.yahoo.com> wrote in message
news:OFNXqCUqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> 1. MessageID serves as the PK
> 2. I am concerned with the overall design approach
> tnx
> -Itai
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
>I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
See changes inline. . .
David
--Use Declarative referential integrity and cascading deletes
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[RecipientID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[SenderID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[GroupID] [uniqueidentifier] NULL ,
[SubmitDate] [smalldatetime] NOT NULL ,
[DeleteBySender] [bit] NOT NULL ,
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL ,
[Subject] [tinyint] NOT NULL ,
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message
change the error handling.
there was a mix of "nested transactions" and savepoints
which wouldn't really work.
Slso get rid of the @.out_errorcode parameter.
Any error will go to the client in a message anyway.
If you really need the error value to be returned to another
stored procedure, just return it as the return value of the
stored procedure.
*/
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
IF (@.@.error <> 0) goto eh
COMMIT TRANSACTION
RETURN 0
eh:
ROLLBACK TRANSACTION SendMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ReadMessage
Single value results should be returned in
output parameters instead of resultsets.
And only decrement log_NumberOfNewMessages the first time
*/
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_MessageText out varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION ReadMessageTrans
declare @.AlreadySeen bit
SELECT
@.OUT_MessageText = MessageText
@.AlreadySeen = SeenByRecipient
FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.AlreadySeen = 0)
BEGIN
UPDATE Messages SET
SeenByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION ReadMessageTrans
COMMIT TRANSACTION
RETURN 1
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
DECLARE @.DeleteBySender bit
DECLARE @.DeleteByRecipient bit
--do this in one query
SET
@.Recipient = RecipientID,
@.Sender = SenderID,
@.DeleteBySender = DeleteBySender
@.DeleteByRecipient = DeleteByRecipient
FROM Messages (updlock, holdlock)
WHERE MessageID = @.IN_MessageID
IF @.IN_DeleteOutgoingMessage = 1
BEGIN
if (@.DeleteByRecipient = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
IF (@.@.error <> 0) GOTO EH
END
ELSE --@.IN_IncomingMessage
BEGIN
if (@.DeleteBySender = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION DeleteMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai
|||Uri, John and especially David! Thanks for the code review; I am now
one step further
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk >...
> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?
|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google. com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk >...
>
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John
a web mail application but without SMTP support (e.g message routes
are confined to the webapp domain). The requirements are rather
simple: Each user (e.g mailbox) can view incoming messages and his
outgoing messages. Message quota is defined as the sum of all incoming
and outgoing messages per user
and tracked in the users' row (Users table log_TotalMessages). The
quota is enforced by the business logic layer and not by the DB.
I am considering the following data model for the storage component,
and would appreciate community feedback:
Table layout for incoming and outgoing messages
************************************************
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
[RecipientID] [int] NOT NULL , // The userid ('Users'
Table)
[SenderID] [int] NOT NULL , // The userid ('Users'
Table)
[GroupID] [uniqueidentifier] NULL , // Only assigned if the
user "replyed" to an incoming message
[SubmitDate] [smalldatetime] NOT NULL , // the date of the
message
[DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
one copy of each message I mark a message "to be deleted" and delete
only if both are true.
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
messages
[Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
list
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message */
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000),
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
SAVE TRANSACTION SendMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION SendMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION SendMessageTrans
END
/* ReadMessage */
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION ReadMessageTrans
SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_NumberOfNewMessages =
log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
SAVE TRANSACTION ReadMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION ReadMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION ReadMessageTrans
END
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit,
@.OUT_ERRCODE tinyint OUTPUT
)
AS
BEGIN TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
@.IN_MessageID)
SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
@.IN_MessageID)
IF (@.IN_DeleteIncomingMessage = 1)
BEGIN
IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
END
END
IF (@.IN_DeleteOutgoingMessage = 1)
BEGIN
IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
@.IN_MessageID) = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
@.IN_MessageID
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
END
END
SAVE TRANSACTION DeleteMessageTrans
SET @.OUT_ERRCODE = @.@.error
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION DeleteMessageTrans
END
ELSE
BEGIN
COMMIT TRANSACTION DeleteMessageTrans
END
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
Thanks in advance!
-Itai
Itai,shalom
What is a primary key of the table? So what are you actually asking?
Are you concerned about a perfomance of the stored procedures? Do they give
you a wrong output?
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai
|||1. MessageID serves as the PK
2. I am concerned with the overall design approach
tnx
-Itai
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Itai
I looked at your stored procedures
In the SendMessage stored procedure instead of update these tables I'd use
ON DELETE CASCADE ON UPDATE CASCADE when you design relationship between
users and messages tables
CREATE TABLE Messages_Users
(
[ID] INT NOT NULL PRIMARY KEY,
MessageId INT NOT NULL FOREIGN KEY REFERENCES Messages([MessageId ])ON
DELETE CASCADE ON UPDATE CASCADE,
UserId INT NOT NULL FOREIGN KEY REFERENCES Users([UserId ])ON DELETE
CASCADE ON UPDATE CASCADE,
......
......
)
"Itai Itai" <itaitai2003@.yahoo.com> wrote in message
news:OFNXqCUqEHA.3464@.TK2MSFTNGP14.phx.gbl...
> 1. MessageID serves as the PK
> 2. I am concerned with the overall design approach
> tnx
> -Itai
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
>I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
See changes inline. . .
David
--Use Declarative referential integrity and cascading deletes
CREATE TABLE [dbo].[Messages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[RecipientID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[SenderID] [int] NOT NULL REFERENCES USERS ON DELETE CASCADE,
[GroupID] [uniqueidentifier] NULL ,
[SubmitDate] [smalldatetime] NOT NULL ,
[DeleteBySender] [bit] NOT NULL ,
[DeleteByRecipient] [bit] NOT NULL ,
[SeenByRecipient] [bit] NOT NULL ,
[Subject] [tinyint] NOT NULL ,
[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE INDEX [Messages_RecipientID_IDX] ON
[dbo].[Messages]([RecipientID]) ON [PRIMARY]
CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
ON [PRIMARY]
/* Send Message
change the error handling.
there was a mix of "nested transactions" and savepoints
which wouldn't really work.
Slso get rid of the @.out_errorcode parameter.
Any error will go to the client in a message anyway.
If you really need the error value to be returned to another
stored procedure, just return it as the return value of the
stored procedure.
*/
CREATE PROCEDURE SendMessage (
@.IN_RecipientID int,
@.IN_SenderID int,
@.IN_GroupID uniqueidentifier,
@.IN_Subject tinyint,
@.IN_MessageText varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION SendMessageTrans
INSERT INTO Messages
(RecipientID,
SenderID,
GroupID,
SubmitDate,
Subject,
MessageText)
VALUES (@.IN_RecipientID,
@.IN_SenderID,
@.IN_GroupID,
GETDate(),
@.IN_Subject,
@.IN_MessageText)
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) goto eh
UPDATE Users
SET log_TotalMessages = log_TotalMessages + 1
WHERE usr_AccountNo = @.IN_SenderID
IF (@.@.error <> 0) goto eh
COMMIT TRANSACTION
RETURN 0
eh:
ROLLBACK TRANSACTION SendMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ReadMessage
Single value results should be returned in
output parameters instead of resultsets.
And only decrement log_NumberOfNewMessages the first time
*/
CREATE PROCEDURE ReadMessage (
@.IN_MessageID int,
@.IN_RecipientID int,
@.OUT_MessageText out varchar(2000)
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION ReadMessageTrans
declare @.AlreadySeen bit
SELECT
@.OUT_MessageText = MessageText
@.AlreadySeen = SeenByRecipient
FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.AlreadySeen = 0)
BEGIN
UPDATE Messages SET
SeenByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
UPDATE Users
SET log_NumberOfNewMessages = log_NumberOfNewMessages - 1
WHERE usr_AccountNo = @.IN_RecipientID
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION ReadMessageTrans
COMMIT TRANSACTION
RETURN 1
/* Delete Message */
CREATE PROCEDURE DeleteMessage (
@.IN_MessageID int,
@.IN_DeleteIncomingMessage bit,
@.IN_DeleteOutgoingMessage bit
)
AS
BEGIN TRANSACTION
SAVE TRANSACTION DeleteMessageTrans
DECLARE @.Recipient int
DECLARE @.Sender int
DECLARE @.DeleteBySender bit
DECLARE @.DeleteByRecipient bit
--do this in one query
SET
@.Recipient = RecipientID,
@.Sender = SenderID,
@.DeleteBySender = DeleteBySender
@.DeleteByRecipient = DeleteByRecipient
FROM Messages (updlock, holdlock)
WHERE MessageID = @.IN_MessageID
IF @.IN_DeleteOutgoingMessage = 1
BEGIN
if (@.DeleteByRecipient = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteBySender = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Sender
IF (@.@.error <> 0) GOTO EH
END
ELSE --@.IN_IncomingMessage
BEGIN
if (@.DeleteBySender = 1)
BEGIN
DELETE FROM Messages WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
ELSE
BEGIN
UPDATE Messages SET DeleteByRecipient = 1
WHERE MessageID = @.IN_MessageID
IF (@.@.error <> 0) GOTO EH
END
UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
WHERE usr_AccountNo = @.Recipient
IF (@.@.error <> 0) GOTO EH
END
COMMIT TRANSACTION
RETURN 0
EH:
ROLLBACK TRANSACTION DeleteMessageTrans
COMMIT TRANSACTION
RETURN 1
/* ListIncomingMessages */
CREATE PROCEDURE ListIncomingMessages (
@.IN_RecipientID int
)
AS
SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
= @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
/* ListOutgoingMessages */
CREATE PROCEDURE ListOutgoingMessages (
@.IN_SenderID int
)
AS
SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
= @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
|||Hi
I am not sure what you are requiring people to do regarding your post, you
are the only person that can do the analysis of what is required, if you
have captured them correctly then you will know what to store in the
database. That said, there does not seem to be any referential integrity
built into the DDL, FKs and PKs should be defined. There is probably a
natural key of RecipientID, SenderID, and SubmitDate so a covering unique
index may be useful, and a INT may not be sufficient for you messageid.
Whether just storing bits to indicated the actions or whether a date would
be better would depend on whatever auditing requirements you require.
In your stored procedures you should implement better error handling, any
statement may fail and you are only checking the result from a few. From
Books online "Because @.@.ERROR is cleared and reset on each statement
executed, check it immediately following the statement validated, or save it
to a local variable that can be checked later." you may not even be checking
what you think!.
John
"Itai" <itaitai2003@.yahoo.com> wrote in message
news:429f6e7d.0410030033.7573337c@.posting.google.c om...
> I need to develop an internal messaging sub-system that is similar to
> a web mail application but without SMTP support (e.g message routes
> are confined to the webapp domain). The requirements are rather
> simple: Each user (e.g mailbox) can view incoming messages and his
> outgoing messages. Message quota is defined as the sum of all incoming
> and outgoing messages per user
> and tracked in the users' row (Users table - log_TotalMessages). The
> quota is enforced by the business logic layer and not by the DB.
> I am considering the following data model for the storage component,
> and would appreciate community feedback:
>
> Table layout for incoming and outgoing messages
> ************************************************
> CREATE TABLE [dbo].[Messages] (
> [MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID
> [RecipientID] [int] NOT NULL , // The userid ('Users'
> Table)
> [SenderID] [int] NOT NULL , // The userid ('Users'
> Table)
> [GroupID] [uniqueidentifier] NULL , // Only assigned if the
> user "replyed" to an incoming message
> [SubmitDate] [smalldatetime] NOT NULL , // the date of the
> message
> [DeleteBySender] [bit] NOT NULL , // Since I want to maintain only
> one copy of each message I mark a message "to be deleted" and delete
> only if both are true.
> [DeleteByRecipient] [bit] NOT NULL ,
> [SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unread
> messages
> [Subject] [tinyint] NOT NULL , // Subject is derived from a fixed
> list
> [MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL
> ) ON [PRIMARY]
>
> CREATE INDEX [Messages_RecipientID_IDX] ON
> [dbo].[Messages]([RecipientID]) ON [PRIMARY]
> CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])
> ON [PRIMARY]
>
>
> /* Send Message */
>
> CREATE PROCEDURE SendMessage (
> @.IN_RecipientID int,
> @.IN_SenderID int,
> @.IN_GroupID uniqueidentifier,
> @.IN_Subject tinyint,
> @.IN_MessageText varchar(2000),
>
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION SendMessageTrans
> INSERT INTO Messages
> (RecipientID,
> SenderID,
> GroupID,
> SubmitDate,
> Subject,
> MessageText)
> VALUES (@.IN_RecipientID,
> @.IN_SenderID,
> @.IN_GroupID,
> GETDate(),
> @.IN_Subject,
> @.IN_MessageText)
>
> UPDATE Users
> SET log_NumberOfNewMessages = log_NumberOfNewMessages + 1
> WHERE usr_AccountNo = @.IN_RecipientID
> UPDATE Users
> SET log_TotalMessages = log_TotalMessages + 1
> WHERE usr_AccountNo = @.IN_SenderID
>
> SAVE TRANSACTION SendMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION SendMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION SendMessageTrans
> END
>
>
> /* ReadMessage */
> CREATE PROCEDURE ReadMessage (
> @.IN_MessageID int,
> @.IN_RecipientID int,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION ReadMessageTrans
> SELECT MessageText FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_NumberOfNewMessages =
> log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @.IN_RecipientID
> SAVE TRANSACTION ReadMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION ReadMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION ReadMessageTrans
> END
>
>
> /* Delete Message */
>
> CREATE PROCEDURE DeleteMessage (
> @.IN_MessageID int,
> @.IN_DeleteIncomingMessage bit,
> @.IN_DeleteOutgoingMessage bit,
> @.OUT_ERRCODE tinyint OUTPUT
> )
> AS
> BEGIN TRANSACTION DeleteMessageTrans
> DECLARE @.Recipient int
> DECLARE @.Sender int
> SET @.Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =
> @.IN_MessageID)
> SET @.Sender = (SELECT SenderID FROM Messages WHERE MessageID =
> @.IN_MessageID)
>
> IF (@.IN_DeleteIncomingMessage = 1)
> BEGIN
> IF((SELECT DeleteBySender FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Recipient
> END
> END
>
> IF (@.IN_DeleteOutgoingMessage = 1)
> BEGIN
> IF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =
> @.IN_MessageID) = 1)
> BEGIN
> DELETE FROM Messages WHERE MessageID = @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> ELSE
> BEGIN
> UPDATE Messages SET DeleteBySender = 1 WHERE MessageID =
> @.IN_MessageID
> UPDATE Users SET log_TotalMessages = log_TotalMessages - 1
> WHERE usr_AccountNo = @.Sender
> END
> END
>
> SAVE TRANSACTION DeleteMessageTrans
> SET @.OUT_ERRCODE = @.@.error
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION DeleteMessageTrans
> END
> ELSE
> BEGIN
> COMMIT TRANSACTION DeleteMessageTrans
> END
>
>
> /* ListIncomingMessages */
>
> CREATE PROCEDURE ListIncomingMessages (
> @.IN_RecipientID int
> )
> AS
> SELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID
> = @.IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC
>
> /* ListOutgoingMessages */
>
> CREATE PROCEDURE ListOutgoingMessages (
> @.IN_SenderID int
> )
> AS
> SELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID
> = @.IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESC
>
> Thanks in advance!
> -Itai
|||Uri, John and especially David! Thanks for the code review; I am now
one step further
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk >...
> There is probably a
> natural key of RecipientID, SenderID, and SubmitDate so a covering unique
> index may be useful, and a INT may not be sufficient for you messageid.
"Covering index" is something I lack to understand ... How is it
stored in a
b-tree structure, does the whole string composed of the diffrent
columns get saved as one key in each node? How does SQL server
(depending on the query's' where clause of course) 'extracts' the
right column and scan for its appropriate value within the index? What
are the questions to ask when considering a covering index as a design
requirement.
Regarding the INT data type for messageID, what would you suggest?
Messages will often be deleted but the counter value will always
progress...
I thought about using a uid, but they are not suitable for a Clustered
Index since they are not guaranteed to be chosen in incremental
order...
Thanks again
-Itai
BTW does anyone know how to dump a table to a text file using command
line with arguments?
|||Hi
I have not seen Davids reply!!
itaitai2003@.yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82@.posting.google. com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<41605341$0$21630$afc38c87@.news.easynet.co.uk >...
>
> "Covering index" is something I lack to understand ... How is it
> stored in a
> b-tree structure, does the whole string composed of the diffrent
> columns get saved as one key in each node? How does SQL server
> (depending on the query's' where clause of course) 'extracts' the
> right column and scan for its appropriate value within the index? What
> are the questions to ask when considering a covering index as a design
> requirement.
The uniqueness of the index would make sure no duplicates values of
the three combined columns are inserted into your database, this may
be important for maintaining integrity. I would expect that when you
are looking for a message you will be mainly searching on a
combination of these three columns. The query processor will decide on
whether an index is useful using various algorithms.
> Regarding the INT data type for messageID, what would you suggest?
> Messages will often be deleted but the counter value will always
> progress...
You would have to determine the number of records and what growth you
are expecting, but when you could be mailing a significant number of
recipients then the maximum number offered by an INT datatype will
probably get used up quickly, therefore BIGINT may be better.
> I thought about using a uid, but they are not suitable for a Clustered
> Index since they are not guaranteed to be chosen in incremental
> order...
> Thanks again
> -Itai
>
> BTW does anyone know how to dump a table to a text file using command
> line with arguments?
BCP, DTS or even osql will do this, look at books online for more
information on these.
John
Subscribe to:
Posts (Atom)