2

I need to create a database to store details about what regions game characters have been in and how they got there. I am planning to store the character UUID as a binary(16) index, the region name as varchar(25), and the unix time as an int; other fields aren't fully decided yet.

I also need to store the entire history of every region a user has ever been to along with the same details.

This system will deal with incoming data from many sources simultaneously (at least a hundred) as the characters move between regions.

Most queries will only want to know about things relating to a character, but some will want to know the last agents uploaded for a given region. I intend to store this information in separate table(s).

An associate believes that it would be more efficient for me to use a table for every region, but I am concerned because I do not see this usually done.

So basically what I want to know if in my case is it more efficient to do as he said and use separate tables for every region.

iceimperator12
  • 21
  • 1

2 Answers2

3

I would use one table, to have one per region would mean any expansion down the track would require new tables, which in turn means more coding. With one table per entity type you can allow for many regions to be added and removed by just inserting or deleting rows from the database.

TheRealTy
  • 2,409
  • 3
  • 22
  • 32
0

Creating a table for every region very well might be more efficient, but that is going to cause a headache in terms of design and implementation.

Now, for every region, it must encode the table name it is referencing rather than just using a foreign key (the region's ID). Similarly, for every region that you decide to add later (expansions, etc), you will need to both add new code, as well as new tables to interact with a fundamentally identical operation.

I would only do this if you determine that it is a bottleneck. Pre-optimizing on this scale is generally a mistake. After all, it's easy to take the good, normalized design and make it a bit little more domain specific, but it's harder to do the reverse.

pickypg
  • 22,034
  • 5
  • 72
  • 84