Need to make a database with arbitrary pieces of info

arbitrary pieces of info in each row

[kuper20]

Hi,

So I'm keep running into the same problem recently in my databases. The thing is I need to have an arbitrary number of pieces of information stored in a place where I really only have one cell available. For example: I have a player, and I want to find all of the games they play by a gameid and link their profile to that game page.

My short term solution was to put all the game id's in that one cell separated by spaces and I have a little function that stores and receives them, but now I have the same problem with videos. I want to have all the videos of a player show up on their profile, and again that's an arbitrary number and I have a feeling there's a better way to do it.

Here's my example:
playerid ¦ name ¦ gamesplayed ¦ videos
3563 ¦ joe ¦ battleJoe battleJoe2 ¦ www.example.com

This is kind of what it's like now.

One other solution that I've thought of is making a new table for every piece of arbitrary information I need. So I have a gamesplayed table which has

playerid ¦ battleJoe ¦ battleJoe2 ¦ battleJoe3 ¦ ....
3563 ¦ 0 ¦ 1 ¦ 0 ¦ ...

with a zero or a one depending on if they've played it or not, but then I'd have to add a new column and it would change for all the players and I'm not sure if that's a good way to do it. I'd also have a lot of different tables I'd have to take care of.

Any help and/or suggestions is greatly appreciated!

[edited by: tedster at 5:03 am (utc) on June 16, 2008]
[edit reason] use example.com - it can never be owned [/edit]

[LifeinAsia]

You should definitely restructure the data and make it more normalized and easier to maintain/query!

Quick suggestion:
Players (table)
- playerid
- name

Games (table)
- gameid
- gamename

GamesPlayed (table)
- playerid
- gameid

Videos (table
- videoid
- videoname

etc.

[kuper20]

ok, now can you explain your table structure a little more? I want it to be completely dynamic so I need to think about adding and removing players, games, and videos.

So say I wanted to add another gameid corresponding to a certain player id in the GamesPlayed table. I would add a column with a title of the game name and put everyone at 0 except the certain player I would want to add, where the cell would be 1. Now I think I run a query like "UPDATE GamesPlayed SET `Game1`=1 WHERE playerid=1" if some player has now played game1, but how do I loop through and echo all of the games a player has played? I would think "SELECT * FROM GamesPlayed WHERE playerid=1", but then to output I need to know the title of the column...which is dynamic...can I loop through the Games table using the gamename which should be the same as the column title for GamesPlayer? would that work?

Also I'd want to be able to do it the other way by seeing what players played a certain game, but I don't know how to do that unless I make yet another table.

[LifeinAsia]

Nope- with the way I mentioned, you never need to add columns. If you add a new game, then you add a new ROW to the Games table. You can add as many players or games as you want and never need to change the table structure.

To update the database to show playerid 1 has played gameid 10, you would use the following:
INSERT GamesPlayed (playerid, gameid)
VALUES (1, 10)

To see all the games that playerid 1 has played:
SELECT Games.gamename
FROM Games INNER JOIN GamesPlayed ON Games.gameid=GamesPlayed.gameid
WHERE GamesPlayed.playerid=1

To show all the players who have played gameid 10:
SELECT Players.name
FROM Players INNER JOIN GamesPlayed ON Players.playerid=GamesPlayed.playerid
WHERE GamesPlayed.gameid=10

I would suggest you read up on some relational database management concepts to really unlock the power of the database. Otherwise, you're building inefficiency into the system, which will just compound as time goes on.

The main thing you have to do is change your perspective from linear programming to set theory. It can be a jump, but once you make it, everything clicks into place nicely.