Ways of storing data...

[Kryptix]

So I have a friend system, it's just a table with 2 columns: user (int), friend (int)

 

Now it currently has over 2 million rows and seems pretty pointless having a row per entry because the data doesn't need to be searched in any way. The data doesn't need to be joined or anything.

 

I'm considering adding a new column to the users table called 'friends' and just store the data like: <FRIEND ID>;<FRIEND ID>;<FRIEND ID>;<FRIEND ID>;<FRIEND ID>;<FRIEND ID>;<FRIEND ID>;

 

Is this a bad idea? Which would use less disc space and/or be more efficient?

 

When you login to the game all it currently does is:

 

result = db.getQuery("SELECT * FROM `friends` WHERE `user`= '" + userID + "'");
while (result.next())
player.addFriend(result.getInt("friend"));

 

So instead of that I'd just use Java's equivalent of PHP's explode() looping through the ID's and adding them.

 

If there's no problem in doing this, which data type is the best to use for this kind of stuff?

 

As I said there's literally no other usage, I will never need to use the friend system for anything else except from the above code.

[The Little Guy]

The way you have it now would be most efficient. Remember a cell is NOT a table row, so don't turn it into one with coma separated values or any other separator.

[fenway]

As long as you don't need to "parse" the row as part of any query -- then there's nothing wrong with that at all.

[Kryptix]

As long as you don't need to "parse" the row as part of any query -- then there's nothing wrong with that at all.

Which would be better to use in your opinion?

[kickstart]

Hi

 

Seems a bit strange to have a table of ID fields (which are basically meaningless) and to never join these to a table of people to get a meaningful value for them (such as name). And if you do get the value you either need to use a join (efficient), do an extra query (inefficient) or do a query per row (hideously inefficient).

 

All the best

 

Keith