0

I'm developing an Administration plugin for Bukkit Servers. I want to give it the functionality to Ban (normal, temp and ip), Mute (normal and temp), and Warn players. To store all this information, I want to use an SQL Database (SQLite or MySQL).

Information could be stored in 2 ways.

Option 1:

4 Smaller Tables:

  • playerlist [id,player,ip,lastlogin] (Used to maintain a list of every player who's joined)
  • banlist [id,player,ip,creationTime,reason,endTime,type] (Keep track of all ban info)
  • mutelist [id,player,creationTime,reason,endtime,type] (Keep track of all mute info)
  • warnlist [id,player,warns]

This would require me to query each table separately and then be parsed from each individual ResultSet.

Option 2:

1 Large Table

  • playerlist [id,player,ip,lastlogin,banReason,banEndTime,banType,muteReason,muteEndTime,muteType, warns]

This would only require me to query 1 large table for all the necessary information which could then be parsed from a single ResultSet.

What I want to know is - What would be the best option overall? From what I've seen, people's answers vary depending on how people are using their database and size of it. Since I'm new to SQL databases, I'm unsure of which would be good in situation.

Kyle Colantonio
  • 454
  • 1
  • 3
  • 21

1 Answers1

0

I would recommend option 1 as it is more Normalized then option 2.

Advantages of Option 1:

1) minimal Data redundancy. (for eg Ban information will appear only once in the banlist table)

2) Single version of Truth. (Since a event is logged only once in a table, a Single Version of Truth is maintained)

3) Faster updates (Since information is not stored at multiple places, Making any updates would be much faster)

A de-normalized model is adopted in a system where you run analytical queries which requires joining multiple tables. Since Joining large tables would result in performance issue, a de-normalized model is adopted.

rogue-one
  • 11,259
  • 7
  • 53
  • 75