[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Advice needed: Transaction Log .. how far to bring it

Lim Siew Yin

3/16/2007 2:47:00 AM

Hi ppl,

Regarding the subject, would appreciate your input/feedback. Thanks.

Senario:
Database contains tables to keep info on Purchasing, Sales, Customers, etc
(normal stuff)
Am hoping to use a Transaction Log as a huge reference table where I can
quickly retrieve all relevant documents for a set of related events.
eg. PO with its Supplier Invoices, Goods Receive Advices and etc...
Everything seems fine... but since my Sales info are also in the log, am
thinking of putting in details like product quantities, unitprice, discounts
and etc into it as well.
(that way, i can easily derive sales figures for particular customers or
incentive calculation for reps)
But that will mean i have to start logging in the PRODUCT DETAIL level.
(if a PO consists of 10 items.. i'll have > 10 records for it)
Is this wise? The old system i am gonna replace separates the Sales Log
(down to product detail) from the other transactions.
(as incentive calculation is troublesome... more attention is being placed
on sales)
But Credit Notes is killing that design as it is not in the sales log.
Also, since i started to learn about the goodness of Views... couldn't help
but think.. are Transation Logs still needed? :P
Would it be better if i create Views in place of Transaction Log instead? I
can then have a view for the Purchasing Office (ignoring sales records),
a view for Sales Reps (just sales, returns and etc) and etc... Is this idea
ok? Or does transaction log still have its purposes?

Thank you for your time.


Limsy the noob... obviously.
3 Answers

David Portas

3/16/2007 4:43:00 AM

0

On 16 Mar, 02:46, "Lim Siew Yin" <limsyn.nos...@gmail.com> wrote:
> Hi ppl,
>
> Regarding the subject, would appreciate your input/feedback. Thanks.
>
> Senario:
> Database contains tables to keep info on Purchasing, Sales, Customers, etc
> (normal stuff)
> Am hoping to use a Transaction Log as a huge reference table where I can
> quickly retrieve all relevant documents for a set of related events.
> eg. PO with its Supplier Invoices, Goods Receive Advices and etc...
> Everything seems fine... but since my Sales info are also in the log, am
> thinking of putting in details like product quantities, unitprice, discounts
> and etc into it as well.
> (that way, i can easily derive sales figures for particular customers or
> incentive calculation for reps)
> But that will mean i have to start logging in the PRODUCT DETAIL level.
> (if a PO consists of 10 items.. i'll have > 10 records for it)
> Is this wise? The old system i am gonna replace separates the Sales Log
> (down to product detail) from the other transactions.
> (as incentive calculation is troublesome... more attention is being placed
> on sales)
> But Credit Notes is killing that design as it is not in the sales log.
> Also, since i started to learn about the goodness of Views... couldn't help
> but think.. are Transation Logs still needed? :P
> Would it be better if i create Views in place of Transaction Log instead? I
> can then have a view for the Purchasing Office (ignoring sales records),
> a view for Sales Reps (just sales, returns and etc) and etc... Is this idea
> ok? Or does transaction log still have its purposes?
>
> Thank you for your time.
>
> Limsy the noob... obviously.

In SQL Server the Transaction Log is a data file or set of files that
support backup and recovery. Please read the documentation to
understand how those features work. I think you have misunderstood the
concept but I'm not going to attempt a full explanation here. It's
much better that you learn from Books Online.

In most scenarios you can't use the log for runtime queries against
business data. In fact there is no supported method to do this.

If you actually meant something other than "transaction log" then
please rephrase your question and repost a better explanation.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Lim Siew Yin

3/16/2007 5:18:00 AM

0


Errm... i think you have misunderstood me. Sorry i didnt explain what it
meant to me.
What i meant by Transaction Log is a table meant solely for logging
transactions.

Limsy

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1174020178.432187.167480@e1g2000hsg.googlegroups.com...
> On 16 Mar, 02:46, "Lim Siew Yin" <limsyn.nos...@gmail.com> wrote:
>> Hi ppl,
>>
>> Regarding the subject, would appreciate your input/feedback. Thanks.
>>
>> Senario:
>> Database contains tables to keep info on Purchasing, Sales, Customers,
>> etc
>> (normal stuff)
>> Am hoping to use a Transaction Log as a huge reference table where I can
>> quickly retrieve all relevant documents for a set of related events.
>> eg. PO with its Supplier Invoices, Goods Receive Advices and etc...
>> Everything seems fine... but since my Sales info are also in the log, am
>> thinking of putting in details like product quantities, unitprice,
>> discounts
>> and etc into it as well.
>> (that way, i can easily derive sales figures for particular customers or
>> incentive calculation for reps)
>> But that will mean i have to start logging in the PRODUCT DETAIL level.
>> (if a PO consists of 10 items.. i'll have > 10 records for it)
>> Is this wise? The old system i am gonna replace separates the Sales Log
>> (down to product detail) from the other transactions.
>> (as incentive calculation is troublesome... more attention is being
>> placed
>> on sales)
>> But Credit Notes is killing that design as it is not in the sales log.
>> Also, since i started to learn about the goodness of Views... couldn't
>> help
>> but think.. are Transation Logs still needed? :P
>> Would it be better if i create Views in place of Transaction Log instead?
>> I
>> can then have a view for the Purchasing Office (ignoring sales records),
>> a view for Sales Reps (just sales, returns and etc) and etc... Is this
>> idea
>> ok? Or does transaction log still have its purposes?
>>
>> Thank you for your time.
>>
>> Limsy the noob... obviously.
>
> In SQL Server the Transaction Log is a data file or set of files that
> support backup and recovery. Please read the documentation to
> understand how those features work. I think you have misunderstood the
> concept but I'm not going to attempt a full explanation here. It's
> much better that you learn from Books Online.
>
> In most scenarios you can't use the log for runtime queries against
> business data. In fact there is no supported method to do this.
>
> If you actually meant something other than "transaction log" then
> please rephrase your question and repost a better explanation.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> --
>
>
>
>


Razvan Socol

3/16/2007 7:29:00 AM

0

If you already store that data in some other tables, it would not be a
good idea to create a Transactions table that contains a copy of all
that data. Just create one or more views to retrieve the data in an
appropriate format.

Razvan