I have characters I want to store in a database. Each character has attributes. The thing that makes it difficult for me is that each character has different attributes depending on their level.
For example: Character A - Level = 1 - Hitpoints = 100 - Mana = 50
and then later they could upgrade: Character A - Level = 2 - Hitpoints = 120 - Mana = 60
I have a bunch of different characters. I feel like I should have each character be a its own Table. Does that sound like a good solution or should I organize it differently?
The attributes -- hitpoints, mana, etc. -- are the same for those characters, they just have different high-values depending on level. You would certainly not want separate tables just for that. A separate control table that had character level and max attributes would make more sense.
It's essentially going to be a lookup table. I'm going to input all the stats for a character up to a certain level. I would like to get that data using php and then show that data on a website.
I think I know what you are trying to do here, from the information you have given you just need one table with four columns, one for the name and one each for each attribute, as your attribute names will be the same for each character (just the values that differ) then you should be good.
If you then add another attribute just add another column.
there are different ways to skin this cat, depends on your requirements and if you need to report on things certain ways. 1 of many ways of doing it is here
create table #Characters( characterid INT IDENTITY(1,1),
alphasoup NCHAR(1) PRIMARY KEY )
create table #Mana( manaid INT IDENTITY(1,1), manavalue int not null PRIMARY KEY )
create table #Hitpoints ( Hitpointid INT IDENTITY(1,1),
Hitpointvalue int not null PRIMARY KEY )
create table #Levels ( Levelid INT IDENTITY(1,1),
Levelvalue int not null PRIMARY KEY )
create table #CharacterAttributes (characterid int not null, nlevel int not null,
Hitpoints int not null, Mana int not null)
insert into #Mana
select 50 union
select 60 union
select 70 union
select 80
insert into #Hitpoints
select 100 union
select 120 union
select 140 union
select 160
insert into #Levels
select 1 union
select 2 union
select 3 union
select 4
DECLARE @asciiCode INT= 65
WHILE @asciiCode <= 90
BEGIN
INSERT #Characters
SELECT CHAR(@asciiCode)
SELECT @asciiCode = @asciiCode + 1
END
insert into #CharacterAttributes(characterid, nlevel, Hitpoints, Mana)
select c.characterid, l.Levelvalue, hp.Hitpointvalue, m.manavalue
from #Characters c
cross apply #Levels l
cross apply #Mana m
cross apply #Hitpoints hp
where alphasoup = 'A'
union
select c.characterid, l.Levelvalue, hp.Hitpointvalue, m.manavalue
from #Characters c
cross apply #Levels l
cross apply #Mana m
cross apply #Hitpoints hp
where alphasoup = 'B'
select *
From #CharacterAttributes
drop table #CharacterAttributes
drop table #Characters
drop table #Levels
drop table #Mana
drop table #Hitpoints
Yes, the names/char values should be encoded into numeric values, not just "name" but also "movement", "type" and "rarity".
Other than that, those rows look fine in a single table, I don't see any need for separate tables. "Bombers" themselves could all be in the same table, too, even if different levels.
