Is there a more effective approach?

Question

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 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.

What data does the users table contain? – Bas van Ommen Apr 08 '11 at 08:04 — Bas van Ommen, Apr 08 '11 at 08:04

score 3 · Accepted Answer · answered Apr 08 '11 at 08:03

3

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.

answered Apr 08 '11 at 08:03

janhink

4,943
3
29
37

And it would be beneficial to add indexes on `t_user_ships.user_id`, `t_users/user_id`, `t_user_ships.ship_id` and `t_ships.ship_id` – SimonDowdles Apr 08 '11 at 08:13

score 2 · Answer 2 · answered Apr 08 '11 at 08:11

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

score 1 · Answer 3 · edited May 23 '17 at 12:04

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.

score 0 · Answer 4 · answered Apr 08 '11 at 08:06

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.

score 0 · Answer 5 · answered Apr 08 '11 at 08:06

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.

score 0 · Answer 6 · answered Apr 08 '11 at 08:08

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!

Is there a more effective approach?

6 Answers6