Re: Data model for a web messaging application.

Hi

I have not seen Davids reply!!
itaitai2003_at_yahoo.com (Itai) wrote in message news:<429f6e7d.0410050519.28073d82_at_posting.google.com>...
> Uri, John and especially David! Thanks for the code review; I am now
> one step further :)
>
> "John Bell" <jbellnewsposts_at_hotmail.com> wrote in message news:<41605341$0$21630$afc38c87_at_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 Received on Tue Oct 05 2004 - 22:14:31 CEST