a very simple Oracle question

a very simple Oracle question

Post by Dee » Mon, 04 May 1998 04:00:00



Hi,

I am a student and trying to develop an Oracle application for filling up
weekly timesheets. While designing database, I havefollowing  tables...

Employee table
Name
address
salary
designation
dateofJoin

Project table
ID
Name

Activity table
ID
Activityname

subactivity table
ID
name

Now my transaction table will be
WTS table
Name
Date
project
activity
subactivity
hrs

If I design my transaction table like this, then their is lot of duplication
of data.
Transaction table data may be like this

Name      Date            Project    Activity    Subactivity    hrs

John        01/01/97     ABC        AA           AC                    2
John        01/01/97     ABC        AA           BC                    1
John        01/01/97     ABC        AG           AC                    2
John        01/01/97     DEF        AA           AC                    2
John        01/01/97     DEF        AA           AT                    2
John        01/02/97     ABC        AA           AC                    2
John        01/02/97     ABC        AA           AC                    2
John        01/02/97     GHI          AN          TT                    2
John        01/02/97     ABC        AL           AC                    2
Tom        01/01/97     ABC        AA           AI                    2
Tom        01/01/97     ABC        AS           AC                    2
Tom        01/01/97     JJJ        AA           AC                    2
Tom        01/01/97     JJJ        AT            AC                    2
Tom        01/01/97     LLL        AA           AC                    2
Tom        01/01/97     LLL        AK           AC                    2

 I see that 2 employees will enter almost 20 records for just 1-2 days time
sheet with lot of data which is same in every record. What is the efficient
way of doing this.
Any help will be apperitiated.

DEE

 
 
 

a very simple Oracle question

Post by Steve Dav » Wed, 06 May 1998 04:00:00


Dee,
Add an ID column of type number(#) to your person table.
Choose # to allow the maximum number of people you will EVER have in
the system e.g. number(4) will allow up to 9999, number(5)- up to
99999 etc.  Then, store the person_id in the timesheet table instead
on the name.

Replace the project id column with a column of type number(#) [or add
a column if the character ID has some meaning]. Again, choose # to
allow the maximum number of projects you will ever have.

If subactivity and activity are dependant, it may be worth storing
combinations, rather than both values.
i.e. if the possible combinations of activity/subactivity are
XX PP
XX QQ
YY RR
YY SS
ZZ TT
create a table activity_combionations containing activity,
subactivity, id:
XX PP  1
XX QQ  2
YY RR  3
YY SS  4
ZZ TT  5
Then store the ID in the timesheet table instead of activity and
subactivity.

If the possible combinations are
XX PP
XX QQ
XX RR
XX SS
XX TT
YY PP
YY QQ
YY RR
YY SS
YY TT
ZZ PP
ZZ QQ
ZZ RR
ZZ SS
ZZ TT
Don't bother with the extra table - just treat activity and
subactivity the same as project, and store a numeric ID instead of the
character code.

HTH,
Steve

<snip>

Quote:>Name      Date            Project    Activity    Subactivity    hrs
>John        01/01/97     ABC        AA           AC                    2
>John        01/01/97     ABC        AA           BC                    1
>John        01/01/97     ABC        AG           AC                    2
>John        01/01/97     DEF        AA           AC                    2
>John        01/01/97     DEF        AA           AT                    2
>John        01/02/97     ABC        AA           AC                    2
>John        01/02/97     ABC        AA           AC                    2
>John        01/02/97     GHI          AN          TT                    2
>John        01/02/97     ABC        AL           AC                    2
>Tom        01/01/97     ABC        AA           AI                    2
>Tom        01/01/97     ABC        AS           AC                    2
>Tom        01/01/97     JJJ        AA           AC                    2
>Tom        01/01/97     JJJ        AT            AC                    2
>Tom        01/01/97     LLL        AA           AC                    2
>Tom        01/01/97     LLL        AK           AC                    2
> I see that 2 employees will enter almost 20 records for just 1-2 days time
>sheet with lot of data which is same in every record. What is the efficient
>way of doing this.
>Any help will be apperitiated.
>DEE


 
 
 

1. A few simple Oracle questions

Our organization is relatively new to Oracle but fairly seasoned with SQL Server
development.  We have a few questions we need to understand to make the
transition as quickly as possible.  Please respond directly via email as I do
not read this newsgroup regularly,

Sincerely,
Mark Eaton

-----------------------------------------------------------------------
SQL*Plus related
--------------------------
How do I tell the Server to return only a fixed number of rows in response to a
query.
In isql (Sybase) I would say
set rowcount 10
select fld1 from table1 where fld2 > 100
This would return the first 10 rows that fulfilled the condition.
How do I achieve the same in SQL*Plus. There does not seem any
environment variable that I can set.
------------------------------------------------------------------------
OCI related
-----------------
OCIStmntExecute( ) returns the status of executing a SQL command.
Is there a call to find the number of result rows before doing a fetch ?
( equivalent to a Sybase DBCOUNT () ) which would facilitate doing a
single memory allocation for storing the results
------------------------------------------------------------------------
Oracle Database Server - Datatypes related
--------------------------------------------------------------
User defined types in Oracle refer to composite fields.
In Sybase they are more like typedefs in C/C++
e.g I would define a user defined type ut_zip as char(6)
This way whenever I had addresses the zip field would be of type ut_zip.
This facilitated a single point of change to the zip fields and also
maintenance / searching the database for zip fields.
How can I achieve the same in Oracle ?
------------------------------------------------------------------------

2. Find Max Amt with Corresponding Date

3. newbie - simple oracle questions

4. Multiple record date dange problem

5. sort-of-simple Oracle questions

6. VB Client to Oracle Server Methods

7. Help! Simple Oracle Question!

8. Do you have a sample code of timestamp?

9. SQL7, SIMPLE SIMPLE SIMPLE question

10. Simple Question...hopefully simple answer

11. A simple question, really simple

12. Simple Set statement this is very simple question

13. VB5 Pro Masked Edit control - A simple, simple question