?
Solved

form with 2 levels of sub form

Posted on 2014-02-18
7
Medium Priority
?
505 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 35

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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 35

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 35

Accepted Solution

by:
Mark Geerlings earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

850 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