SQL with variable amounts of fields
-
I am working on a simple game where you take creatures into battle and they fight against each other. Everything is working fine minus figuring out the best way to store the results. I want something similar to this data structure. int ID (identity) int AttackerID int DefenderID int AttackerCreatureID1 int AttackerCreatureID2 int AttackerCreatureID3 int DefenderCreatureID1 int DefenderCreatureID2 int DefenderCreatureID3 and then more for the creatures that died, ect The problem is, I want the characters to be able to buy more battle slots and bring more into battle at a time. So, say they know can bring 5 creatures in, I don't want to have to make a bunch of new columns. Using XML this exercise would be pretty easy since you can just add a couple extra tags here and there whenever you want. Like:
12
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>I've done lots of work with SQL before but never come across doing something like this with variable amounts of data column wise... Any help would be awesome! Thanks ahead of time.
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
-
I am working on a simple game where you take creatures into battle and they fight against each other. Everything is working fine minus figuring out the best way to store the results. I want something similar to this data structure. int ID (identity) int AttackerID int DefenderID int AttackerCreatureID1 int AttackerCreatureID2 int AttackerCreatureID3 int DefenderCreatureID1 int DefenderCreatureID2 int DefenderCreatureID3 and then more for the creatures that died, ect The problem is, I want the characters to be able to buy more battle slots and bring more into battle at a time. So, say they know can bring 5 creatures in, I don't want to have to make a bunch of new columns. Using XML this exercise would be pretty easy since you can just add a couple extra tags here and there whenever you want. Like:
12
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>I've done lots of work with SQL before but never come across doing something like this with variable amounts of data column wise... Any help would be awesome! Thanks ahead of time.
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
You could try this: Procedure with following params int ID (identity) int AttackerID int DefenderID NVARCHAR AttackerCreatureIDs (this would be comma separated ids) NVARCHAR DefenderCreatureIDs (this would be comma separated ids) then in your proc, split the Ids string the below function could be used
CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(Id int identity(1,1), Value nvarchar(100))
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))ReturnEND
GO -
You could try this: Procedure with following params int ID (identity) int AttackerID int DefenderID NVARCHAR AttackerCreatureIDs (this would be comma separated ids) NVARCHAR DefenderCreatureIDs (this would be comma separated ids) then in your proc, split the Ids string the below function could be used
CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(Id int identity(1,1), Value nvarchar(100))
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))ReturnEND
GOI thought about doing that but it feels dirty, doesn't feel very relational databaseish...
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
-
I am working on a simple game where you take creatures into battle and they fight against each other. Everything is working fine minus figuring out the best way to store the results. I want something similar to this data structure. int ID (identity) int AttackerID int DefenderID int AttackerCreatureID1 int AttackerCreatureID2 int AttackerCreatureID3 int DefenderCreatureID1 int DefenderCreatureID2 int DefenderCreatureID3 and then more for the creatures that died, ect The problem is, I want the characters to be able to buy more battle slots and bring more into battle at a time. So, say they know can bring 5 creatures in, I don't want to have to make a bunch of new columns. Using XML this exercise would be pretty easy since you can just add a couple extra tags here and there whenever you want. Like:
12
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>I've done lots of work with SQL before but never come across doing something like this with variable amounts of data column wise... Any help would be awesome! Thanks ahead of time.
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
It looks to me as if you've got a many to many relationship there. A battle joins many attackers to many defenders. So you would have a table Battles which has a unique BattleId, plus AttackerId and DefenderId plus whatever other information you want to hold against a battle (date, location, whatever). Then you have a number of options. One option would be to have an Attackers table which holds BattleId and CreatureId for all the attackers, and another table Defenders which holds BattleId and CreatureId for all the defenders. Or another option would be to have just one table BattleCreatures which holds BattleId, ArmyId and CreatureId where ArmyId is either the AttackerId or the DefenderId depending which side the creature is on. Whether you separate out Attackers from Defenders into two separate tables or whether you have them all together in one table is a bit of a judgement call. There are arguments for and against both options.