[sklett]

I just happened to run a query for a specific transaction:

Code:
SELECT tl.transaction_line_id, tl.amount, tl.transaction_order FROM TRANSACTIONS AS t
INNER JOIN TRANSACTION_LINES AS tl ON tl.Transaction_id = t.transaction_id
WHERE t.Transaction_ID = 8528
Here are the results (I hope they are formatted decent)

Code:
transaction_line_id amount transaction_order
0 225 0
1 -225 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 0 19
My main question is why does a transaction with a SINGLE line item have 20 line item records? I can speculate what some of them are:

0) the item sold

1) a credit that might have been applied

19) shipping (that was zeroed out)

What about 2-18? What are they, why are they there?

Another question: Am I correct to assume that the PK for transaction_lines is a composite of transaction_id + transaction_line_id? If not, where is the PK for this view/table?

Thanks for any help,

Steve
This is a cached copy. Click here to see the original post.

[sklett]

RE: Curious… why so many transaction_lines?

My main question is why does a transaction with a SINGLE line item have 20 line item records? I can speculate what some of them are:

0) the item sold

1) a credit that might have been applied

19) shipping (that was zeroed out)

Wow, wasn’t expecting this! Turns out all those lines are the members of my assembly that I sold! :0|

Makes sense and I can see why they did it that way, but man… sure creates a lot of records considering that’s all we sell (assemblies) and the average assembly has 20+ members.

We’ve been selling through a distributer model (less orders with larger quantities) but will soon be selling direct (MUCH higher volume of transactions with qty = 1).

So let’s say we do 10k transactions a month, each transaction carries 20 line records….. NetSuite, you cool with 2.4 million records a year for one account? What about our storage?

I don’t think this is good….

[longlam]

RE: Curious… why so many transaction_lines?

Yes, all the assembly lines are there for cogs reasons. I forgot which table column it is but one of them will like you know if it is an assembly and which line item the top level assembly is. This is important for anyone that does sales reports based on this number because you maybe double counting items sold if you take both the assembly and line items into consideration!

[sklett]

RE: Curious… why so many transaction_lines?

Hi, thanks for the reply.

I can see how the current design works, I’m only concerned that we are going to eat up space quick with a ton of duplicate data.

Time will tell!

[Author]

Posts