SQLTeam.com | Weblogs | Forums

Database setup


#1

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?


#2

could you describe the use case a bit more? What is the big picture?

FWIW I cannot conceive of a need for what you are trying to do here.


#3

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.


#4

do you want to track historical data or overwrite existing data?


#5

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.


#6

hi, i think you should try firebase, at least easier for you in the future to embed into mobile platform


#7

I don't need firebase. I just would like to know a good way to setup my database to fit my requirements.


#8

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.


#9

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

#10

The problem is that I have different attributes for different levels for a character.

I have this so far:

but it's weird because I have the name being repeated and many other columns too. That's why I don't think that this is a good solution.


#11

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.


#12

yes, it does looks like a lookup table make more sense, then you simply join those tables together and get the values out.