?
Solved

form with 2 levels of sub form

Posted on 2014-02-18
7
Medium Priority
?
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 shows how to recover a database from a user managed backup
Suggested Courses

771 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