Re: Schema Design Question

kumar_kmk_at_hotmail.com (Kumar) wrote in message news:<bce41b15.0112261307.7e005b56_at_posting.google.com>...
> Greetings All
> I have a database design issue and hope one of you can answer my
> question.
> I have data comming to me in form shown below
>
> Input1: (Name,DOB,Sal)
> String Date Int
> ---------------------------
> a 12/12/2001 3000
> b 12/13/2001 4000
> c 12/14/2001 5000
>
> Input2: (company,turnover,rate,years)
> String Int Real Int
> ---------------------------
> a 12 1.2 5
> b 4000 2.5 6
> c 5000 3.4 7
>
> So I keep getting data like these tables with different column names
> and different types of data. I have to store this data in database
> tables. One solution is to keep creating tables for each input I
> receive clearly this is inefficient as I get 100 inputs like this per
> day. Is there any schema design where by I can capture details such
> that this data belongs to Input2 with name company and data type
> String.
>
> I might get down the line some more entries for Input2.
> d 6000 3.5 8
> So my final aim is to get data for following queries..
>
> 1. get all company names for input2. Now the query should return me
> a,b,c,d
> 2. get average of rate of company2.
>
> Any thoughts...
> Thanks

hi kumar
wish u happy new year

for this create a table with the name test and with the following columns
code, c1,c2,c3,...c10 all with varchar2 data types in code you will enter the following data Input1(Name,dob,sal) and in the rest of the columns you wil enter the data according to the order in code i.e name in c1 ,dob in c2,sal in c3 so on
so the table will look like this

code c1 c2 c3 c4

Input1(Name,dob,sal)			a       12/12/2001     3000
Input1(Name,dob,sal)			b       12/13/2001     4000
Input1(Name,dob,sal)			c       12/14/2001     5000
Input2(company,turnover,rate,years)	a       12        	1.2   5
Input2(company,turnover,rate,years)     b       4000      	2.5   6
Input2(company,turnover,rate,years)     c       5000      	3.4   7
Input2(company,turnover,rate,years)	d      	6000  		3.5   8

before we go for the quries first we should take all the distinct codes, for this execute the following select statement

select distinct code from test
Input1(Name,dob,sal)
Input2(company,turnover,rate,years)

and then take a print out of this query
now if you require all the company names for the input2 then following select statement is executed.
select c1 from test where substr(code,1,6)='Input2' c1 because as per the entry in the code for input2 first we entred the company name

and for any calculations on the data we should use conversion functions like to_number, to_date to convert varchar2 to number or date

regards
raghu
Certified DBA Received on Thu Dec 27 2001 - 07:30:30 CET