Database Design Help/Suggestions

[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

[masterofollies]

I would go with 2 seperate tables myself

[bazz]

Hi,

Please post your tables as they are currently and I'll check to see if I can suggest something better. I just can't get a handle on it from your first post :(

like this:

table name
| id | field1 | field2 | etc
| 01 | value | value | etc


bazz

[shyam]

CREATE TABLE users (
`user_id` INT() NOT NULL AUTO_INCREMENT
, `profile_name` CHAR(16) NOT NULL
, `faction` ENUM ('allies','axis') NOT NULL
, `doctrine_id` int NOT NULL
, `division_id` int
, PRIMARY KEY (`user_id`);
create table doctrines(
`doctrine_id` int not null auto_increment
, `doctrine_desc` char(15) not null);
create table divisions(
`division_id` int not null auto_increment
, `division_desc` char(15) not null);
alter table users add constraint doc_fkey doctrine_id references doctrines(doctrine_id);
alter table users add constraint div_fkey division_id references divisions(division_id);

[Draygon]

Thank you shyam...that will get me going. Now to get this process to become automated so I dont have to go in and manually choose which division to put them in when they sign up :)