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.