0

Situation

I am trying to figure out a rating system right now that contains ratings for servers by players. A user would be able to rate a server in several different categories. Maintainability, performance, and compactness of the storage of the ratings are what I am trying to find a balance for right now. Hopefully we can come up with some good solutions to this.

MB required = (bytes per entry) x (500 reviews) x (4000 servers) / (1024) / (1024)

Method 1: Maintainable bridge table Allows storing of any number of categories, searching is easy, categories are allowed to have additional attributes as specified in their own table. would require 64 different entries to be equivalent to the holding power of the other methods.

[int Server_ID 4 bytes]

[int Account_ID 4 bytes]

[int rating 2 bytes]

[bigint last_updated 8 bytes]

[int category_id 4 bytes]

Total space required: 2929.7 mb.

enter image description here

Method 2: SET column If storing less than 64 categories, I could have a SET column to act as a bit flag table.

[int Server_ID 4 bytes]

[int Account_ID 4 bytes]

[SET ratings 8 bytes] (probably less)

[bigint last_updated 8 bytes]

Total space required: 45.77mb.

SET method

Method 3: Multiple bit columns I could just use a bunch of different bit columns that have names on them. Maybe use a comment to pull out a description of the category when displaying it in the actual application.

[int Server_ID 4 bytes]

[int Account_ID 4 bytes]

[multiple bit ratings: 8 bytes??? bytes]

[bigint last_updated 8 bytes]

Total space required???: 45.77mb.

enter image description here

Pangamma
  • 731
  • 12
  • 28
  • 2
    Storage is cheap. Do you really care about a couple of GiB? Remember Knuth's maxim: "*premature optimisation is the root of all evil*". Build it to work, and optimise only where necessary. – eggyal Mar 10 '14 at 23:50
  • 1
    I would agree with @eggyal. Storage optimization is about the last thing I would look at when doing schema design. I would first design the DB objects to give me the most maintainability and code flexibility for known current and near-term use cases. Once you have the rough schema, than you can look at thing like best data types for columns, most appropriate indexing schemes, etc. Does your application need server rating categories? Does if make you application better by giving better user experience or making code more reusable/maintainable? If so, go with that approach. – Mike Brant Mar 10 '14 at 23:53
  • Thank you for your input, guys. I'd accept one of the answers if it wasn't just a reply! +1 up vote to both of you. – Pangamma Mar 11 '14 at 02:31

1 Answers1

0

Use the bridge table approach if you want to have a wide range of attributes applied to each rating category. The bridge table would be the most maintainable option out of the three. Certainly the easiest for others to understand. If space is no concern, and you do not suspect that the table will grow to 128,000,000 entries, try using a bridge table.

Use the SET approach if you know you will never need over 64 categories, or if data space storage is a high concern.

enter image description here

Pangamma
  • 731
  • 12
  • 28
  • I think you're wrong about the hypothetical speed. Most queries on a `SET` column would not be sargable (so would require a full table scan), whereas the keys against which a "bridge" table is joined are typically indexed (so would be highly performant). – eggyal Mar 11 '14 at 21:05
  • That's true, yeah. The values I would be using would all be booleans though. I would be searching by server_id, or by account_id. After getting a list of those items I would then search through the bits. Then again, I would be wanting to perform a summation on certain values... perhaps I could index the highest 100 values in a table. Updated once daily with a cron-job... The space saved for this is just so enticing. – Pangamma Mar 13 '14 at 07:08
  • Then again again, It will be quite a while before I will have 64 categories, OR enough servers for storage space to become an issue. I also have to consider that not every server is going to have 64 entries, because few servers would get approvals in all 64 categories.I suppose I will build it with a bridge for now, but will probably switch to bit flags at a later time in the future when it becomes necessary. – Pangamma Mar 13 '14 at 07:32
  • You say that "*the space saved is just so enticing*", but could you elaborate on what exactly is enticing about saving 3GiB? With 3TiB drives costing about £50 (and cloud hosting services offering 15GB+ free), quibbling over pennies just doesn't make sense to me. As I said before, **storage is cheap**. – eggyal Mar 13 '14 at 08:10