CODEWITHSUNDEEP
mysql
user3140714
user3140714

Reputation: 85

Is my current points system good in terms of performance?

I'm developing simple points system

Table structure:

user_id     type    points

column 'type' is an ENUM field with 7 options (post, comment, attachement_submit, etc) .

Simple query structure to update points:

INSERT INTO points (user_id, type, points) VALUES ('x', 'x', 'x')
ON DUPLICATE KEY UPDATE x='x', x='x', x='x'

My question is = is my current structure good in terms of performance? there will be a lot of queries (7 different rows for one user) or not?

If not, can somebody give me any alternative to my current approach?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Performance considerations should based on how the data is going to be used. Relational databases are flexible in how they store data. This flexibility is a good thing, because different applications have different needs resulting in different data structures.

Your question gives no mention of what you are doing with the data, except that you want to store seven individual values with the ability to update each one individually. For that rather narrow problem, your data structure is quite reasonable.

If I were working on such a system, I would be thinking about storing historical data as well. That would include -- at the minimum -- a time stamp of when a value changed. Then, all the data operations would be inserts.

A common alternative to your structure would be a single table with seven columns. You don't provide enough information to determine if that is a more reasonable approach.

Upvotes: 1

Related Questions