 |
|
| Computers Forum Index » Computer - Databases » Bonus points database solution... |
|
Page 1 of 1 |
|
| Author |
Message |
| Justas Butkus... |
 Posted: Wed Jul 22, 2009 10:33 am |
|
|
|
Guest
|
Hello to all,
I would like to ask one question, about rational database design.
The system I am about to develop has several different applications -
from things measurement (like in length/width/etc...) to some changing
of their properties (i.e. painting, changing size, etc.).
What I have a problem is - bonus points system.
There are owners of things (lets call entity: owner), things
themselves (say: thing) and bonus points for speed. That means:
_owner_ is informed about something, that he must complete in one week
(month, year, etc.). If _owner_ get things done in first 10% of period
- he get 5 bonus points, next 20% - 4 points and so on until 0 points
if process is completed at the very end of the period assigned.
My problem is: there are different operations, from where bonus points
might come and I am interested in storing this kind of information.
I.e.: {4} points for {measuring} your {Ming dynasty vase} in {3 days}
of {10 days} period.
I am thinking about something on the lines of:
-----
| owners
| id
| name
| bonus_points_used -- increase number, then collected points are
used
-----
-----
| things
| id
| title
| owner_id (ref. owners.id)
-----
-----
| categories
| id
| title
-----
-----
| assignments
| id
| thing_id (ref. things.id)
| category_id (ref. categories.id)
| message TEXT
| created unix_timestamp
| expiration unix_timestamp
-----
----
| points
| assignment_id (ref. assignments.id)
| ammount number[2] -- count of points given for this completion
| completion_time unix_timestamp
-----
Assignments are of textual kind - system administrator/interested
person send an assignment (at that time "created" field is filled with
current timestamp) and defines a date of expiration. Category is kind
of pseudo table/field. In my design it defines the legal entity, that
will be responsible for helping in task completion and its
verification (that's what I called "measurement" in my example).
At the time user completes the task - bonus points are written.
Conversion from time to points ammount rules are defined in
application layer.
Later owner might reclaim his points (this is in application, or
humans layer  ), and the ammount of points reclaimed will be written
to "owners" table (this field should not exceed the SUM(ammount) from
points table). Points themselves, then it comes to reclaiming, do not
differ. Other information apart from sum of points is for mere
statistics.
I am thinking, whereas this kind of solution is good from relational
point of view. And any other point of view, in fact.
I hope, that somebody will have time to read and write a comment. :)
--
Thank you very much in advance,
Justas B. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Mon Nov 30, 2009 1:58 pm
|
|