Home » SQL & PL/SQL » SQL & PL/SQL » table design idea
table design idea [message #226741] Mon, 26 March 2007 06:53 Go to next message
rzkhan
Messages: 345
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
Re: table design idea [message #226746 is a reply to message #226741] Mon, 26 March 2007 07:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you planning to populate/update the net table from recd table ?

[Updated on: Mon, 26 March 2007 07:18]

Report message to a moderator

Re: table design idea [message #226916 is a reply to message #226741] Mon, 26 March 2007 23:01 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
yes. of course...

rzkhan
Previous Topic: Semantics
Next Topic: PLS-00410
Goto Forum:
  


Current Time: Sat Dec 03 18:00:28 CST 2016

Total time taken to generate the page: 0.04566 seconds