Array Type Field - MySQL

[Ziimac]

Well, I'm making a game, and I want multiple users to be able to control one planet. The thing is, I also want users to be able to control multiple planets. Everything was going fine until I was making the db, and realized I have no clue as to how I'd add an array type of field... see, I got planet id (pid), which is auto_increment. Then I have user id (uid) to connect the planet to the user... or on this case users.

Since, a planet can have multiple users, but these aren't pre-defined, is where I'm having a problem. So enums and sets won't work. I also can't create say 10 fields for one planet, since the number of people one planet can be up to the total land on the planet (approx 400). This sounds like I'm just forgetting something, but eh...

[Piranha]

When doing many-to-many relations you do a table in between, like this:

CREATE TABLE planet (
   id int PRIMARY KEY
);

CREATE TABLE user (
   id int PRIMARY KEY
);

CREATE TABLE planet_user (
   planetid int,
   userid int,
   PRIMARY KEY (planetid, userid),
   FOREIGN KEY (planetid) REFERENCES planet (id),
   FOREIGN KEY (userid) REFERENCES user (id)
);

You should search for "database normalisation" to get an idea about how to design databases correct, otherwise you will run into trouble later on. You should follow at least up to 3 NF or BCNF, 4 NF and 5 NF is not used that much.

[NogDog]

Sounds like you want a separate relation table. So at a minimum you'd have 3 tables:

planet_table
----------------
planet_id   (auto-increment, primary key)
...other fields that define a planet....

player_table
----------------
player_id (auto-increment, primary key)
player_name
player_password
...etc......

player_to_planet
--------------------
player_id
planet_id
....any other fields you need to define this relation...

Then if you need to know all of the players associated with a given planet:

SELECT player_name FROM player_table INNER JOIN player_to_planet USING(player_id)
WHERE planet_id = 123;

(If you're using MySQL, see http://dev.mysql.com/doc/refman/4.1/en/join.html for more info on the JOIN syntax.)

[Ziimac]

Thanks, both of you.