MYSQL - SImple database design

I would like to develop a system, where user will get the data dynamically(what I mean dynamic is, without reloading pages, using AJAX.. but well, it does not matter much).

My situation is like this. I have this table, I called it "player", in this player table, I will store the player information like, player name, level, experience etc.

Each player can have different clothes, start from tops(shirts), bottoms, shoes, and hairstyle, and each player can have more than 1 tops, bottoms, shoes etc.

What I am hesitated or not very sure about is, how do you normally store the data? My current design is like this:

Player Ta开发者_StackOverflow社区ble
===========================================================================================
id   |  name   | (others player's info)  |      wearing                   | tops | bottoms
===========================================================================================
  1  | player1 |                         | top=1;bottom=2;shoes=5;hair=8  | 1,2,3| 7,2,3


Tops Table
=====================
id  |  name   | etc...
=====================
 1  | t-shirt | ...

I am not sure if this design is good. If you are the database designer, how would you design the database? Or how you will store them?

Please advise. Thanks

---

In short: instead of putting the column "wearing" in, you should make a table that connects the 2 tables (players and clothes.)

You'd get

players
id name
1  player1

clotheTypes
1  top
2  bottom

clothes
id  name   type
1   shirt1 1
2   shirt2 1
3   pants1 2

wearing
playerId ClotheId
1        1
1        2

But please, do read the article taht @badcat shared!

edit: as requested, some hints:

You don't want this generally:

id name    computer
1  john    work, laptop, desktop, oldcomputer

Because you that should be in a seperate table. Now you can actually add, later on, the brand and speed of the computers: you can't the way you're doing it right now.

Person
id name
1  john

computer
id name    owner
1  work     1
2  laptop   1
3  desktop  1
....

---

Seems like you are new to this.

Please read this article on Wikipedia about database normalization:

http://en.wikipedia.org/wiki/Database_normalization

---

Personally I would not do it like in the example above, because you'd have to update the players' table every time some T-Shirt is added or removed.

---

You need to create an additional tables for clothes, something like this:

player

id_player  //1
name       //Jhon
level      //6
experience //13

player_cloth

id_player   //1
id_clothe   //5

cloth_types

id_cloth    // 5
type        // hat
description // very warm 

Once you have created the tables you need to relate this data, you only need the id_player value.