table design idea [message #226741] |
Mon, 26 March 2007 06:53  |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
Hi...
EX > desc bill
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
MEMBER_ID NOT NULL VARCHAR2(7)
ITEM_ID NOT NULL NUMBER(3)
AMOUNT NUMBER(10,2)
M NOT NULL VARCHAR2(3)
Y NOT NULL NUMBER(4)
EX > select * from bill;
MEMBER_ ITEM_ID AMOUNT M Y
------- ---------- ---------- --- ----------
2006001 15 19900 APR 2007
2006001 8 100 APR 2007
2006001 12 50 APR 2007
2006001 13 20 APR 2007
2006001 17 40 APR 2007
2006001 18 50 APR 2007
2006002 12 50 APR 2007
2006002 13 20 APR 2007
2006002 17 40 APR 2007
2006002 18 50 APR 2007
2006003 12 50 APR 2007
2006003 13 20 APR 2007
2006003 17 40 APR 2007
2006003 18 50 APR 2007
2006004 12 50 APR 2007
2006004 13 20 APR 2007
2006004 17 40 APR 2007
2006004 18 50 APR 2007
2006005 12 50 APR 2007
2006005 13 20 APR 2007
2006005 17 40 APR 2007
2006005 18 50 APR 2007
2006006 12 50 APR 2007
2006006 13 20 APR 2007
2006006 17 40 APR 2007
2006006 18 50 APR 2007
2006001 16 305.1 APR 2007
2006003 16 294.93 APR 2007
2006001 14 833.33 APR 2007
2006002 14 833.33 APR 2007
2006003 14 833.33 APR 2007
2006004 14 833.33 APR 2007
2006005 14 833.33 APR 2007
2006006 14 833.33 APR 2007
34 rows selected.
EX > desc recd
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
MEMBER_ID VARCHAR2(7)
RECD_AMOUNT NUMBER(10,2)
RECD_DATE DATE
EX > select * from recd;
MEMBER_ RECD_AMOUNT RECD_DATE
------- ----------- ---------
2006001 100 01-APR-07
2006002 200 02-APR-07
2006001 300 08-APR-07
EX > desc net
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
MEMBER_ID VARCHAR2(7)
CURRENT_BILL NUMBER(10,2)
ARREARS NUMBER(9,2)
SURCHARGE NUMBER(6,2)
RECD NUMBER(10,2)
BALANCE NUMBER(10,2)
M VARCHAR2(3)
Y NUMBER(4)
EX > select * from net
2 /
MEMBER_ CURRENT_BILL ARREARS SURCHARGE RECD BALANCE M Y
------- ------------ ---------- ---------- ---------- ---------- --- ----------
2006001 21298.43 APR 2007
2006002 993.33 APR 2007
2006003 1288.26 APR 2007
2006004 993.33 APR 2007
2006005 993.33 APR 2007
2006006 993.33 APR 2007
6 rows selected.
EX > spool off
My question is Is the above table design OK. if I need to extract the following info
member_id , amount_receivable, amount_received, arrears, surcharge, balance
Please help me out...
rzkhan
|
|
|
|
|