Is there a more effective approach?

I recently started on creating a 'game' using PHP and a mysql database. In this game, there are ships, which each player can build and control.

Right now I have several this set up in 开发者_Python百科my database like this:

Ships table, contains there columns:

ID, name, credits cost, mineral cost, building time, description, speed, power, shield

The users table holds:

Ship_01_amount .... ship_08_amount

Where the number corresponds to the id of the ship.

I was wondering if there is a more efficient way of doing this, while not screwing things up.

Since now, if I wish to add a new ship, I have to alter my table.

---

You have to split the information about users and their ships into a third table. So you would have following tables:

t_ships: ship_id, name, credit_cost, mineral_cost, etc
t_users: user_id, name, etc
t_user_ships: ship_id, user_id, amount

This way when adding a ship you would just update the amount in the table t_user_ships.

---

It would help a lot to read up on database normalization. A good resource is as follows: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

While I know this is not answering your question directly, it will help with future predicaments like these. A Google search for "one to many mysql" or "many to one mysql" will pull up some useful resource.

Kind regards

---

Search for "what is many to many relationship".

Or check relevant question/answer: resolve-many-to-many-relationship

As all other have answered, a "middle" or "intermediate" table is the key to the solution. Any info (like the amount you have) about a particular ship of a particular user can be added as a field in that table. You could add colour-of-flag-raised, current-speed, captain's-name, date-of-first-sail, etc, whatever your game needs.

---

Yes you can use a many-to-many relationship by creating a new table mapping between users and ships User_has_ship(userId, shipId). You can use foreign key constraints in MYSQL to check that the user and the ship exists.

Altough check for user ships now needs a table join, so you have to do like this

SELECT * FROM User u, User_has_ship us WHERE u.Id = us.userId and userId=?

You should replace ? in userId with the id for the user you are currently checking.

---

Make this basically into three tables.

• Table 1: Your current ships table.
• Table 2: User-Table (User-Data + User-ID and such).

• Table 3: Ships_User_META

  SHIP-ID | USER ID
  --------+--------
      1   |   1
      2   |   1
      3   |   1
      1   |   2
  

...and so on.

Therefore you can easily add and remove ships to users.

---

You seem to have a table with unitary ship descriptions, and a table owning references to ships as columns. What you have here is relation between your user and ship references. Extending this requires altering your table schema. You advise the following : Create a table user_ships with three columns : - a user_id field, - a ship_id field, - an amount field

Every time you need to know the amount of ships belonging to a single player you can do a SELECT on users, ships and user_ships tables joined with corresponding ids. When a user builds a new model of ship, INSERT a record in user_ships table. When a user loses or builds more ships, UPDATE the corresponding record. That's it!