CODEWITHSUNDEEP
mysqldatabasedatabase-design
Mark
Mark

Reputation: 5653

mysql - storing comma separated string more efficiently

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,

Upvotes: 0

Views: 637

Answers (3)

Amber
Amber

Reputation: 526603

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.

Upvotes: 9

Rik Heywood
Rik Heywood

Reputation: 13972

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.

Upvotes: 1

Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29985

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?

Upvotes: 2

Related Questions