Hi All,
I am working on Web Application which deals with history data for
reports and keeping track of changes.
Current Solution :

1) For each Entity I am having a column TID (Tracking ID) which
keep on increasing for an instance of Entity. (so history and present
data in the same table) . It makes querying the data difficult.
2) I am also using month year table for Managing data i.e
<TableNameMMYYYY> for a given month and year

Purposed Soluion :

1) Using a seperate table so that history and present data is
placed seperately .

2) Store All data in one table <TableName>

Please guide me on advantages and disadvantages you pin point in the
two approaches.

With warm regards
Jatinder

I'm not sure I understand your description - you say that you have
history and present data in the same table, but then you say you have
separate month/year tables. You should probably give some more
information about what your tables look like (ie a simplified CREATE
TABLE script), what data you have in each one, how many rows you have
per month etc. It's also a good idea to mention which version of MSSQL
you have.

Simon

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> Current Solution :
>
> 1) For each Entity I am having a column TID (Tracking ID) which
> keep on increasing for an instance of Entity. (so history and present
> data in the same table) . It makes querying the data difficult.
> 2) I am also using month year table for Managing data i.e
><TableNameMMYYYY> for a given month and year
>
> Purposed Soluion :
>
> 1) Using a seperate table so that history and present data is
> placed seperately .
>
> 2) Store All data in one table <TableName>
>
> Please guide me on advantages and disadvantages you pin point in the
> two approaches.

It's a little unclear what you mean, but anyway having a table for each
month is not a good idea. Well, if you need to distribute the data it
could be, but in such case you should unite the data in a partitioned
view, and all your queries should use that view.

As for having a current data in a separate table, and adding this
data to a history table at the end of a day in a maintenance job can
sometimes be useful. As a matter of fact, this is routine in our
system.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks Erland,
Sorry If my description was not able to make the point / approach
clear. I am using a single table for a given month to hold that month's
changes/transactions
and for Enity there is no month year table

Erland I wish to ask you ;can I mail you my queries (I know posting
here on newsgroup will fetch me many answers) because most of the time
You,David Protas ,Anith and Celko are the ones who are active on these
newsgroups and provide elaborate and good answers . Everytime your
answer give something new to learn and you people bring bitter reality
to the poster especially Celko but that's what required . You all guys
doing great job.
Please keep reading my silly questions / answers
With warm regards
Jatinder

Erland Sommarskog wrote:
> jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> > Current Solution :
> >
> > 1) For each Entity I am having a column TID (Tracking ID) which
> > keep on increasing for an instance of Entity. (so history and present
> > data in the same table) . It makes querying the data difficult.
> > 2) I am also using month year table for Managing data i.e
> ><TableNameMMYYYY> for a given month and year
> >
> > Purposed Soluion :
> >
> > 1) Using a seperate table so that history and present data is
> > placed seperately .
> >
> > 2) Store All data in one table <TableName>
> >
> > Please guide me on advantages and disadvantages you pin point in the
> > two approaches.
>
> It's a little unclear what you mean, but anyway having a table for each
> month is not a good idea. Well, if you need to distribute the data it
> could be, but in such case you should unite the data in a partitioned
> view, and all your queries should use that view.
>
> As for having a current data in a separate table, and adding this
> data to a history table at the end of a day in a maintenance job can
> sometimes be useful. As a matter of fact, this is routine in our
> system.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

jsfromynr wrote:
[snip]
> Erland I wish to ask you ;can I mail you my queries (I know posting
> here on newsgroup will fetch me many answers) because most of the time
> You,David Protas ,Anith and Celko are the ones who are active on these
> newsgroups and provide elaborate and good answers .

I'm hoping that Erland encourages you to continue posting your
questions here on the newsgroups - My first half-hour/hour of the
morning, I spend reading the newsgroups. There's lots of interesting
problems, questions and answers.

Occasionally, I try to help people using some of the knowledge I've
picked up along the way, but a lot of the time I just sit back and try
to absorb the knowledge that these people are kind enough to share - it
makes for a great learning experience. I would say that the schemas I
have worked on in the last few months are literally miles ahead of the
garbage I was producing two years ago :-)

Keep up the good work everybody, and keep the dicussions on usenet.

Just my two-penneth

Damien.

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> Sorry If my description was not able to make the point / approach
> clear. I am using a single table for a given month to hold that month's
> changes/transactions

As I said, that is a design that leads to problem, unless you unite
the tables in a partitioned view.

> Erland I wish to ask you ;can I mail you my queries

I prefer if you keep it to the newsgroups. Then other people can assist.
And, as Damien testified, other people can also benefit from the
exchange.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp