-2

I have a table 'users' with usual fields plus 'owned_items' and 'rating' fields and a table 'items' with their id's, and some other properties. User can posses many different items of each type and its rating is calculated by summing up the all these amounts:

rating = N1 + N2 + N3 + ...

I store the items the user owns as a string of the type:

3 4 56 22 6 67 ...

in the field 'owned_items'.

Then if I need a rating (very often), each time I turn the string in an array of numbers, calculate their sum and write the value in 'rating' field. The same is with changing the number of items of each type for a given user.

Is there a better way? May be using the relations between 'users' and 'items' tables?

Thank you.

Vincent Vega
  • 57
  • 7
  • 1
    Storing data as text is one column is worst idea ever(CSV/ space separated). Now you need to parse it using some ugly way and do sum. Data should be atomic, please read about data normalisation and save your time in the future asking why it is working so slow or how to update specific value inside string. – Lukasz Szozda Aug 12 '17 at 15:46
  • 1
    @lad2025 . . . There are some worse ideas outside the world of databases, but it is definitely a really bad idea in this domain. – Gordon Linoff Aug 12 '17 at 15:48
  • @GordonLinoff I am really curious about other nightmares you've encountered(nested and/or multiple triggers/misuse of cursors/eav everywhere/reusing fileds/row-by-row processing/...)? – Lukasz Szozda Aug 12 '17 at 15:51
  • 1
    thank you, I started to read about data normalisation and will read more hoping to find the better way. – Vincent Vega Aug 12 '17 at 16:00
  • Do I understand correctly that I need another table which has 'user_id', 'item_id' and 'amount' columns? Combination of first two is thus unique. – Vincent Vega Aug 13 '17 at 04:18

1 Answers1

0

One has to follow this example and then everything works, i.e. create a join table with additional fields:

https://book.cakephp.org/3.0/en/orm/associations.html#using-the-through-option

Vincent Vega
  • 57
  • 7