[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

form with 2 levels of sub form

Posted on 2014-02-18
7
Medium Priority
?
503 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

656 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