Help design a table for representing range

[kishanvkumar]

Hi,
I would like to get help in designing a table for representing a range in a column. Lets say we have a excel sheet of a matrix like below.

GameName | agegroup 20-24 | agegroup 25-29
------------------------------------------
Cricket | ------5--------|-----7------
Football | ------3--------|-----9------

Lets say the numbers represent the number of medals(just for example sake).
How best to design the table so that when the inputs are 'Cricket' and age '27', I should get the result back as '7'. The problem here is to represent a range as a column.
Soemthing like each column represents a lower/upper range. But I am not able to comprehend how to design a table for this.
Any help is appreciated.
Note : -- are just for formatting. Only numbers should be considered.

Thanks.

[tesuji]

hi,

at first sight your table may look like:

medals (gamename, medal, loage, hiage)
Primary key: gamename, loage.

The select statement to get the medals for given game and age is:

select medal from medals where gamename="Cricket" and :age between loage and hiage;

Consider age is not an attribute of a table but a variable depending on your programming language (java, c++, php etc) or interactive system (e.g. sql management studio) where the select statement will be applied to. I myself often use c++ together with odbc. Here the host variable age must be indicated by a preceding colon like :age.

-- tesu

[kishanvkumar]

Hi,
Thanks for your response. I understood your solution and would work. But the difficulty is, there are two use cases for this. One is for the select query and the other is to display a screen the same as shown above. For this to happen, we have to transpose the rows into columns for display with the current solution. The query to transpose is complex and we have the same requirement for 15 other screens similarly. Also users could edit this value in the screen and have to save it correctly. so its 70% use case if for displaying/editing in the manner above (excel/grid format) and 30% for the data to be used for the select query with game and age.
Just wanted to know if a design can work to be simple for both the display as well as querying ? Also the requirement is to accommodate dynamic addition of age groups. The current solution will accommodate this easily, but transposing seems complex for the display requirement.

Thanks for your time.

[urtrivedi]

detailtable (game,winnername,age)

cricket,abc, 25
basketball,abc, 28

you should store in this manner.

what you are asking is report. so we may retrived any kind of information from above table in any format using queires.

so you should use above table to store basic info.

[kishanvkumar]

Its just not a report alone, The tabular grid is editable and the value has to be stored back.

[urtrivedi]

then you just create table as you have shown in your thread.

[tesuji]

hi kishanvkumar

Could you tell us something more about your task, particularly what database, language?
How do you fill the grid via select statement? Did you understand the meaning of host variable :age? Do you use such variables?

As for java or c++ one can fill the grid within a programming loop what seems to be easier doing the transposition (which should also be able using PHP).

-- tesu

[kishanvkumar]

Hi,
The DB is DB2 and the front end is Flex. There are two screen. One screen is the data provider screen (adming screen) which looks similar to excel sheet above. Its a flex datagrid.The users will enter values in the grid depending on range/game. Also they can add new age ranges on the fly and it should save to DB.
The next screen is the data usage screen, where say the user enters two text boxes (game name, actual age ex: Cricket, 27) and should get the appropriate medal count.

One solution is to have each range in rows as you have described. Makes the 2nd requirement easier, direct fetch query. But the display grid needs rows to be transposed to columns.

The other is to have table design similar to grid. Not deciding to add DB columns on the fly. Thinking of having a upper limit of columns already defined( say 10, age range cannot go above it) and use a metadata table to map the actual usage. In this design, displaying grid becomes easier as its a direct mapping. The second requirement will be a little complex by combining metadata table and actual table.

Looking for any other design which can accommodate the requirements.

Thanks for your time.