How to design database with january-december for every entry Need help for designing my database, I have a table named transaction with columns:  tr_id, item, item_cost, item_quantity, total and my problem is:

I need to divide the item_quantity from january to december:

for example:

tr_id: 1 
item: sock 
item_cost(each): 20 
item_quantity: 12 
total: 240 
jan: 
feb: 
mar: to dec:


I need a suggestion on how to design a database with this kind of entries.
You can build a view that "unpivots" the table and divides item_quantity and total by 12 for each row. The syntax would look something like this:

CREATE VIEW transactions_pivot
AS

SELECT t.td_id, t.item, t.item_cost, x.m AS month_no, t.item_quantity/12
FROM transactions AS t
CROSS JOIN (
    SELECT 1 AS m UNION ALL
    SELECT 2 AS m UNION ALL
    SELECT 3 AS m UNION ALL
    SELECT 4 AS m UNION ALL
    SELECT 5 AS m UNION ALL
    SELECT 6 AS m UNION ALL
    SELECT 7 AS m UNION ALL
    SELECT 8 AS m UNION ALL
    SELECT 9 AS m UNION ALL
    SELECT 10 AS m UNION ALL
    SELECT 11 AS m UNION ALL
    SELECT 12 AS m) AS x


The CROSS JOIN is like a regular JOIN, but without any conditions, so it produces a cartesian product (12 rows for each row in "t"). There are prettier (and faster) ways of writing the code on different platforms, but this syntax is ANSI SQL compatible.

On a side-note: I wouldn't include "total" as a column, since that's calculated from the number of items multiplied by the item price.