[sammp]

I see so many gamemodes that get posted to this forum. They all work. Their databases work. So what's the problem?

I'm going to keep this short, sweet and to the point.

First of all, lets look at this MySQL table creation:

Code:

DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `DatabaseID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(20) NOT NULL,
  `Password` varchar(256) NOT NULL,
  `RegisterIP` varchar(20) NOT NULL,
  `LastIP` varchar(20) NOT NULL,
  `PosX` float(50,5) NOT NULL,
  `PosY` float(50,5) NOT NULL,
  `PosZ` float(50,5) NOT NULL,
  `PosA` float(50,5) NOT NULL,
  `Health` float(50,5) NOT NULL,
  `Armour` float(50,5) NOT NULL,
  `Money` int(11) NOT NULL,
  `Accent` int(11) NOT NULL,
  `Skin` int(11) NOT NULL,
  `AdminLevel` int(11) NOT NULL,
  PRIMARY KEY (`DatabaseID`)
);

I'm going to quickly show you how to make this table more RDB-like.

In any database - data should only be stored once. There's multiple areas where we could adjust this table to relfect that statement:

To keep it quick, let us simply evaluate the Admins side of this table. We can remove admin-related data from the accounts table and put it in its own table. This table will simply reference the ID of the account (which I'm not sure why the developer made it DatabaseID), and give them a level:

Code:

drop table if exists 'admins';

create table 'admins' (
	'DatabaseID' int,
	'level' int not null,
	
	foreign key (DatabaseID) references accounts(DatabaseID) on delete cascade
);

The last line in there makes DatabaseID a foreign key that references the DatabaseID in the accounts table. The statement 'on delete cascade' means that when I delete the row from the parent table, the corresponding row in admins will drop too.


We can simply tidy up our accounts table by removing the 'AdminLevel' field in the accounts table. Just think about the cardinality of a field, and its optionality. Why give every single player a 'Weapons' field in a roleplay server database if not every player is definitely going to carry a weapon? Why not create another table called 'player_weapons'?

This is only one example so it might become a bit unclear. However, you should 100% follow this when you're creating databases. If you're inputting the same value more than once - then your database design doesn't follow the conventional RDB design and performance will suffer greatly when your database needs to search through thousands of records. This design I've showed you allows me to search ONLY through the admins table if I need to list the administrators, and stops me from searching an entire table of accounts - the majority of whom are not administrators. It's faster, tidier and is far more plausable than the first table I shown you. There's much more that could be done with that table - but you can work that one out.

[Yashas]

https://en.wikipedia.org/wiki/Database_normalization

Every schema can be expressed in the third normal form. Such a schema will be a well-designed schema most of the times. An easy way to start would be to merge all the relations into one and write down the functional dependencies on paper and then slowly split them into separate relations to satisfy 2NF and then 3NF.

[Whitley]

Thanks, that's helpful!