Omar Martinez Omar Martinez - 23 days ago 6
SQL Question

Creating a struct type as a column value for T-SQL?

I'm making a database for my game and I need to store a lot of c style structs.

struct stats{
int strength, stamina, agility, intelligence, etc..;
}


I've been looking at how to create user defined data types in t-sql and MSDN specifically says that user defined data types CANNOT be used as a column type:

A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

Is there not a work around, or some other way to create a struct based system in there? I also need a struct for skills and i have around 120+ skills that need to be stored under my player table. My desired table would look something like this:

CREATE TABLE [dbo].[Player](
player_name nvarchar (20),
gold int,
player_stats stats, // array of 4 ints
player_skills skills, // array of 120+ tinyints
player_location location, // array of 3 floats (x,y,z)
player_customization customization, // array of 20+ tinyints
player_equipment equipment, // array of 8 ints
etc....)


Is there a simple way of doing this or do i just need to add all those as individual column types? Thanks for the help.

Answer

This is your table:

CREATE TABLE [dbo].[Players](
    player_name nvarchar (20),
    gold int,
    player_stats stats,                 // array of 4 ints
    player_skills skills,               // array of 120+ tinyints
    player_location location,           // array of 3 floats (x,y,z)
    player_customization customization, // array of 20+ tinyints
    player_equipment equipment,         // array of 8 ints
    etc....
)

This suggests that you should have several other tables and columns:

  • Player_Skills and Skills: One row per player and per skill, with perhaps a skill level column as well.
  • Location: One row for each location, with information about the location. LocationId would be in the Players table.
  • Player_Equipment and Equipment: One row per player and per equipment.

I don't know what player_stats and player_customization are. Perhaps they are just appropriate columns for Players; perhaps they should be their own tables.

Your thinking of a relational database in terms of programming constructs such as "structures" is not appropriate.

Comments