I am converting a spreadsheet of game notes into a database.
I have a table of character Classes for an RPG (fighter, mage, etc)
One of the columns shows the Races that a Class can be in a comma seperated list:
ClassId ClassName Race
------------- ------------- ------------
1 Rogue Man
2 Mage Man,Elf
3 Warrior Man,Dwarf,Elf
I'm new to SQL and not sure how to translate this into a database file.
Here is what I'm thinking:
1. I make a 3rd table that holds the relation.
I think this is the most proper solution. The drawback for me are that I would have to create and manage an extra table. Also, to me the comma separated list like seen above is the preferable way for me to display the data and I'm not sure what other ways there would be to display the three possible Races together on one row (I don't think SQL/SQLite has syntax to concatenate strings or build lists?)
2. I make a column for each race and use a boolean 0 or 1 value
This could be pretty clear. The only disadvantage I see is that I would have to create new columns whenever adding new races.
3. I just leave the Race column as a comma seperated value
I could still query the column with statements like
select * from Class where Race like '%Elf%';
But I think this would make it hard to put any kind of constraints on the field (like a FOREIGN KEY constraint to make sure the Race exists in the Race table).
Thoughts or suggestions are appreciated.