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!