Database Design Help

[Draygon]

I am not a expert in sql or php and I took this project on with some knowledge of both. I am having some trouble designing a database for a campaign I am working on for a game (a mod to a mod basically). In this database I have 5 columns for the users table. I have am thinking of creating it like this:

CREATE TABLE users ('user_id' INT() NOT NULL AUTO_INCREMENT, 'profile_name) CHAR(16) NOT NULL, 'faction' ENUM ('allies','axis') NOT NULL, 'doctrine' CHAR(15) NOT NULL, 'division' CHAR(15), PRIMARY KEY ('user_id'))

Now there are a few problems with that, and thats why I am asking for help here. Specifically the doctrine and division columns are what I am having a problem with. Each of the (doctrine and division) actually have a set number of choices to choose from (there are only 6 doctrines (3 for each faction (axis, allies)) and there are 12 divisions to choose from (6 per faction).

Someone suggested to me that I should create 2 seperate tables for the doctrines and the divisions (and have each doctrine and division have their own unique ID) and have them be foreign keys and tie them into the users table. I dont know how to do this, or how I would use the table in this way.

This will all be used for a form where it asks the user for the name they want to use during the campaign (profile_name) and they choose their faction (axis, allies) once they choose the faction it will then display the doctrines and division for that faction that they can choose from (they can make 3 choices (3 doctrine, 3 division) and once they do that they hit register it will then do some checks and then insert the information into the table that I set up.

I appreciate any help that is offered or any suggestions that any of you may have.

Thanks,
Dray

[chaosprime]

A design for the type of schema that was recommended to you might look like the following. I've made various changes to what you provided in order to correct SQL syntax and according to my personal ideas about best practices; hope you don't mind the latter.

CREATE TABLE `user` (
    `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `profile_name` CHAR(16) NOT NULL,
    `faction_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_id`),
    KEY `profile_name` (`profile_name`),
    KEY `faction_id` (`faction_id`)
);

CREATE TABLE `faction` (
    `faction_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `faction_name` CHAR(8) NOT NULL,
    PRIMARY KEY (`faction_id`),
    KEY `faction_name` (`faction_name`)
);

CREATE TABLE `doctrine` (
    `doctrine_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `faction_id` INT UNSIGNED NOT NULL,
    `doctrine_name` CHAR(15) NOT NULL,
    PRIMARY KEY (`doctrine_id`),
    KEY `faction_id` (`faction_id`),
    KEY `doctrine_name` (`doctrine_name`)
);

CREATE TABLE `division` (
    `division_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `faction_id` INT UNSIGNED NOT NULL,
    `division_name` CHAR(15) NOT NULL,
    PRIMARY KEY (`division_id`),
    KEY `faction_id` (`faction_id`),
    KEY `division_name` (`division_name`)
);

CREATE TABLE `user_doctrine` (
    `user_doctrine_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `doctrine_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_doctrine_id`),
    KEY `user_id` (`user_id`),
    KEY `doctrine_id` (`doctrine_id`)
);

CREATE TABLE `user_division` (
    `user_division_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `division_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`user_division_id`),
    KEY `user_id` (`user_id`),
    KEY `division_id` (`division_id`)
);

How you use this is:

First, you prepopulate your faction, doctrine, and division tables with the appropriate values. In the doctrine and division tables, you set faction_id to the ID from the faction table for the faction that you want to have that doctrine or division. Once a user chooses a faction, then you can retrieve their faction information, and the divisions and doctrines appropriate to them, with queries like these:

SELECT `faction`.*
    FROM `user`
    LEFT JOIN `faction` USING (`faction_id`)
    WHERE `user_id` = your_properly_sanitized_user_ID_goes_here

SELECT `doctrine`.*
    FROM `user`
    LEFT JOIN `doctrine` USING (`faction_id`)
    WHERE `user_id` = your_properly_sanitized_user_ID_goes_here

SELECT `division`.*
    FROM `user`
    LEFT JOIN `division` USING (`faction_id`)
    WHERE `user_id` = your_properly_sanitized_user_ID_goes_here

Then, to assign users doctrines and divisions, you insert rows into the user_doctrine and user_division tables. These tables implement what are called many-to-many relationships in database design; that is, one user can have more than one doctrine and more than one division (and of course doctrines and divisions are associated with many users). Since you describe people being able to select several doctrines and divisions, these become appropriate. An individual row in user_doctrine says "this user_id has this doctrine_id associated with it".

I'm sure this is a lot to absorb, but don't worry, you can handle it. This is how it's done when you're serious about it. :)