Solved

form with 2 levels of sub form

Posted on 2014-02-18
7
482 Views
Last Modified: 2014-03-23
i'm recording payment to employees ,
i desgined 2 tables
CREATE TABLE V_STAT_M  
(ENTRY_ID  numeric(6) not null,
ENTRY_NO NUMBER(10),
ENTRY_DATE DATE,
BENIFICIARY VARCHAR2(100),
Posting_date date,
posted_by VARCHAR2(50),
CONSTRAINT V_STAT_M_ID_pk PRIMARY KEY (ENTRY_ID));



CREATE TABLE V_STAT_D 
(SUB_ID  numeric(15) not null,
MAIN_ID NUMBER(5),
V_STAT_TYPE NUMBER(1),
EMP_NO NUMBER(6),
RESRV NUMBER(2),
ACC_NO NUMBER(5),
COST_CENTER NUMBER(5),
AMOUNT NUMBER(10,2),
REMARK varchar2(255),
Posting_date date,
posted_by VARCHAR2(50),
CONSTRAINT SUB_ID_pk PRIMARY KEY (SUB_ID),
CONSTRAINT V_STAT_FK
   FOREIGN KEY (MAIN_ID) 
   REFERENCES V_STAT_M(ENTRY_ID)
);

Open in new window


sycle of work will be like this
V_STAT_M  will has one record per each payment
V_STAT_D will record payment details per employee
here is the issue :
each employee will have many records in same transaction of v_stat_m
because we will pay him many types of benifits (=v_stat_d.acc_no)
for table design , those 2 tables is enough for me to record every thing
but my issue is in the form
i need form ( main for table v-stat_m) to record payment information
and 2 subform
one subform   i will record employee ID , then in some non_database items will display its
additional information from employee table
the 2nd form will record multi records per employee ( its main will be the 1st subform)

do you think that i should create 3 tables instead of 2 :
1- v_stat_m  : Entry_ID PK
2-emp_rec  :  EMP_ID PK , V_STAT_M.ENTRY_ID FK
3-v_stat_d :   SUB_ID PK , EMP_REC.EMP_ID FK

or it is better to keep 2 tables only , but how to design the 1st sub_form ?
0
Comment
Question by:NiceMan331
  • 4
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39870228
Which tool or program will you use to build the forms?  Is that Oracle Forms or something else?  Also, which version is this?

Do you ever plan to query these table with queries something like this: "where posting_date is null"?  If yes, be aware that those queries will get slower and slower as these tables grow in size.  You may want to add another column named: "unposted", as a VARCHAR2(1) with a default value of "Y".  Then, either add a database trigger or make sure that your application logic that sets posting_date to a non-null value also updates the "unposted" column to null.  Then, create an index on the "unposted" column, and change your queries from:

"where posting_date is null"

to:
"where unposted = 'Y'"

This will allow those queries to remain fast even as the tables get lots of records.
0
 

Author Comment

by:NiceMan331
ID: 39870917
Thanx for the good advise                                 Which tool or program will you use to build the forms?  Is that Oracle Forms or something else?  Also, which version is this?   Yes , it is oracle form 6 i
0
 

Author Comment

by:NiceMan331
ID: 39870931
Do you ever plan to query these table with queries something like this: "where posting_date is null"?                                       No , I will use posting date as a reference only , I will not query for it , I will query on a level of employee , such as last payment information
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39871168
Oracle Forms6i can very easily create a master-detail form for you for these two tables.  It should recognize (and build) the foreign key relationship for you.  Or, you can manually create the relationship between the tho data blocks.
0
 

Author Comment

by:NiceMan331
ID: 39871188
yes , i know how to build master/details form
but what i don't know is : how to build 2 subforms and link between them
second , still i wish to know , do i need a three tables , or those 2 is enough ?
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39871491
I'm not sure what exactly you mean by a "sub-form" or how exactly you want them be "linked".  Are you sure that a two-block, master-detail form cannot do what you want?  From my understanding of the requirements that you described, a two-block, master-detail form should be fine (and would probably be the best option in my opinion).  But, you could use a three blocks if you want, with a middle block to display some items for each employeee that you only want to be displayed once.

No, you don't need three tables for what you have described here.
0
 

Author Comment

by:NiceMan331
ID: 39872482
yes , exactly three blocks , my explanation was wrong cause still my habit to say : form & sub_form as in MS ACCESS
ok , now if i used 3 blocks , the middle block how it will save the data of employee
i mean : the middle block will be one record each employee , then the last block will be all records for the specific record of employee
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now