Make a primary key for the Player
table that isn't a VARCHAR
. A VARCHAR
column isn't a good primary key; a better key type would be an INT
(or a BIGINT
if you expect more than 2^31-1 players ;)). Key lookup with integers is faster compared to key lookup with strings. Also consider that you will have to reference players by their primary key in other tables (detail tables).
The easiest way is to have SQL Server create such an integer key for you. You can do this by adding an IDENTITY
column to the table, and make that column the primary key. In that case, for each insert into the player table a key will be assigned to the player. You will have to add an INDEX
on the table for the player_name
column though as at some point you will have to look up players by their name.
Eg:
CREATE TABLE player(
player_id INT IDENTITY(1,1) NOT NULL,
player_name NVARCHAR(128),
CONSTRAINT PK_player PRIMARY KEY CLUSTERED (player_id)
);
CREATE INDEX
IX_player_name
ON
player(player_name);
As Gordon already pointed out, structs or arrays of structs can't be stored in a table as such. A good way of designing your tables is for each logically grouped set of data for a player, create a (detail) table that links to the player table. Relational databases have an easier time with tables that have as few columns as possible.
For a group of information (a struct) that does not change like a player_location
you could argue to store this information directly in the player table (columns X
, Y
and Z
). Better still is to have that logically grouped set of information in a separate table player_location
. You would then have a 1:1 relation between the player table and the location table.
CREATE TABLE player_location(
player_id INT NOT NULL,
x FLOAT NOT NULL,
y FLOAT NOT NULL,
z FLOAT NOT NULL,
CONSTRAINT PK_player_location PRIMARY KEY CLUSTERED (player_id)
CONSTRAINT FK_player_location FOREIGN KEY(player_id) REFERENCES player(player_id)
);
Other groups of information like player_equipment
will continually grow, as I assume players will pick up equipment as they play through the game. Another aspect is that you will probably add new equipment as the game grows (through mods say). Say you have 100 types of equipment now but in your expansion you would add 100 new types of equipment. Say you take the previous advice, create a table with 100 columns and then when you release the mod you would then have to add 100 new columns.
Having so many columns in a table is a bad idea in itself, plus you would have to alter the equipment table when you release the mod. Another aspect in that way of thinking is that you would have to store 100 (or 200 after the mod) columns of data that would not necessarily contain any relevant information. A player might only have 20 equipment and you would have to store 100 (200) columns. That would be a waste of disk/memory space.
A better way to model this is to have a definition table that defines the equipment and a player-equipment table that stores what equipment the player is holding. Example:
CREATE TABLE equipment(
equipment_id INT NOT NULL,
equipment_name NVARCHAR(128) NOT NULL,
CONSTRAINT PK_equipment PRIMARY KEY CLUSTERED (equipment_id)
);
CREATE TABLE player_equipment(
player_id INT NOT NULL,
equipment_id INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT PK_player_equipment PRIMARY KEY CLUSTERED (player_id,equipment_id),
CONSTRAINT FK_player_equipment FOREIGN KEY(player_id) REFERENCES player(player_id),
CONSTRAINT FK_equipment FOREIGN KEY(equipment_id) REFERENCES equipment(equipment_id)
);
When you release a new mod, you would add new equipment in the equipment
table and when a player picks up new equiment you would add a row to the player_equipment
table referencing the equipment by its ID.