mysql - storing comma separated string more efficiently

Question

I am working on an application where users 'fight' to own larger parts of the map. Each map is made of a 1000 regions. Now my initial plan was to store the areas a user owns as a comma separated string e.g

a user owns regions one to ten 1,2,3,4,5,6,7,8,9,10

Problem is a user could potentially own all 1000 which means it could be a string 3893 characters long... I stored it as a varchar(3893)... which seems horribly inefficient.

I did consider a binary solution e.g

1111111111 followed by 990 0's

Which is a good solution if only one user owns the whole grid as that's 1000 characters but if its 4 or more then its less efficient.

Also I need to consider a way that is easy to compute back to a comma separated string as that is how my javascript will use it.

Thanks,

Answer 1

Why not to store Ids of user-owned regions in a separate table? UserID|OwningLandPlotID

You can then select them all and implode() in PHP to produce comma separated string. What server environment do you have?

Answer 2

Why not normalize your table? Have a table that simply matches pairs of numbers - one the ID of a player, the other the ID of a region. If a player controls 5 regions, they'll have 5 rows in the table. If they control 1000, they have 1000 rows. That's how relational databases are designed and optimized to work.

Example:

table `users`:

ID  Name
------------
1   John
2   Jessie
3   James

table `regions`:

ID  Name
------------
1   France
2   China
3   Australia

table `ownership`:

Owner  Place
------------
1      1
1      3
3      2

Thus, John currently owns France and Australia, and James owns China.

Not only does this simplify your column types, but since it's normalized, you have much more flexibility in the kinds of queries you can run - for instance, you can ask "what's the name of the person who owns region X" or "how many territories does person Y own" without ever having to actually manually parse anything yourself - the DB can do that processing for you.

Answer 3

would it not be better to have a user -> region mapping table?

eg

CREATE TABLE `usertoregion` (
  `iUser` int(11),
  `iRegion` int(11)
);

You can then easily get a list back and quickly find which users own which regions etc.