Lua MySQL Tables

[Extrodus]

So basically what I'm doing is creating tables for two new systems that in most simplest terms, replicates the player skills and it will be coded the same way. Attributes and Talents will be defined by number, and just like skills a player will start with all of them at a default number.

So when a player is created, the tables for skills, attributes, and talents will all be filled accordingly with the default information.

The reason for this post is I want to make sure I am defining things properly.

Currently the query is:

CREATE TABLE IF NOT EXISTS `player_attributes` (
  `player_id` int(11) NOT NULL,
  `attribute_id` smallint(5) NOT NULL DEFAULT '0',
  `points` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `player_id_2` (`player_id`, `attribute_id`),
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `player_talents` (
  `player_id` int(11) NOT NULL,
  `talent_id` smallint(5) NOT NULL DEFAULT '0',
  `points` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `player_id_2` (`player_id`, `talent_id`),
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

What I am here to ask, is UNIQUE KEY even required for this?

CREATE TABLE IF NOT EXISTS `player_attributes` (
  `player_id` int(11) NOT NULL,
  `attribute_id` smallint(5) NOT NULL DEFAULT '0',
  `points` int(11) NOT NULL DEFAULT '0',
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `player_talents` (
  `player_id` int(11) NOT NULL,
  `talent_id` smallint(5) NOT NULL DEFAULT '0',
  `points` int(11) NOT NULL DEFAULT '0',
  FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

My second question is, what do you guys think the best option for setting the default value.
I was thinking an onLogin script that checks if it's the first login, and if so - adds entries to the table for the player. Any suggestions?

 

[Codex NG]

So basically what I'm doing is creating tables for two new systems that in most simplest terms, replicates the player skills and it will be coded the same way. Attributes and Talents will be defined by number, and just like skills a player will start with all of them at a default number.

So when a player is created, the tables for skills, attributes, and talents will all be filled accordingly with the default information.

The reason for this post is I want to make sure I am defining things properly.

The primary key is used to identify a row of data in a table. It is used whenever you need to refer to a particular row, eg. in other tables or by application code etc. In order to identify a row, the values of a PK must be unique. Furthermore, they can't be null, because most dbms treat null as not equal to null (since null typically means "unknown"). A table can only have one PK. All tables in your databse should have a PK (although this is not enforced by most dbms), and PK can span multiple columns.

Unique key constraints are used to ensure that data is not duplicated in two rows in the database. One row in the database is allowed to have null for the value of the unique key constraint. Although a table should have a PK, it need not have any additional unique keys. However, tables can have more than one unique key if that meets your needs. Like PKs, unique keys can span multiple columns.

It is also worth knowing that, by default, many dbms index and physically order tables on disk using the PK. This means that looking up values by their PK is faster than using other values in a row. Typically, however, you can override this behaviour if required.

Source: http://stackoverflow.com/questions/2973420/what-is-the-differen

My second question is, what do you guys think the best option for setting the default value.
I was thinking an onLogin script that checks if it's the first login, and if so - adds entries to the table for the player. Any suggestions?

Yes onLogin would be the best option, you could always set a storage value after the players logins so the data isn't overwritten the next time they login, you could do it when they create their character as well but there could be security issues there.

Using a script in lua allows you more control on how the information is set or if it is set at all.

 

[Extrodus]

@Codex NG - Alrighty, yeah I didn't really think they were needed in this scenario. But it was based from the code in schema.sql for TFS since some of the "player" tables like "player_depotitems" used the Unique key.

Yeah, I was thinking onLogin would be the best bet and exactly - easily configurable for any wanted changes.