Unable to Normalize

[Agentsmith]

Hi,

I have a table storing score and level information of a user. Both are integers. Level value depends on score and mapping is not expressable as a mathematical function. There are min, max values of points for every level.

Please help me to design the dabase table. My problem is, if use level as a column in user table, it doesnt normalize as 2NF as level changes as per score and not as per primary key only (which is say - username). Please suggest me some alternative so that i can define properly normalized tables.

I am new to this forum, so plee forgive me if this is too obvisous or repeated question.

[urtrivedi]

Two ways to do this, as fields value is fluctuating,
1) One is keep level field in usermaster table and whenever score is updated, update level column in usermaster, using insert/update/delete database trigger.

2) another way is, do not create level field in any table
rather, create one view say user_level by joining usermaster table, score table and levelmaster table(with max min values defined for each table). this view will always show u appropriate user level depending on his/her score and min-max value from levelmaster table.

[smantscheff]

You say that the level cannot expressed by a mathematical function, but it can be expressed by a function using the TRUNCATE() and CEILING() functions. So you can define level as a user defined function of score and use it in views and queries.