-5
\$\begingroup\$

Here are the commands I ran to start setting up a database I need.

CREATE TABLE user ( id smallint unsigned not null auto_increment, constraint pk_user primary key (id) );

CREATE TABLE discord_user ( id varchar(20) not null, owner smallint unsigned, constraint pk_discord_user primary key (id), constraint fk_discord_user_owner foreign key (owner) references user(id) );

CREATE TABLE player ( uuid varchar(36) not null, owner smallint unsigned, constraint pk_player primary key (uuid), constraint fk_player_owner foreign key (owner) references user(id) );

CREATE TABLE kit (id mediumint unsigned not null auto_increment, designer smallint unsigned, name varchar(30) not null, disguise enum('ARMOR_STAND','ARROW','BAT','BLAZE','BOAT','CAVE_SPIDER','CHICKEN','COW','CREEPER','DONKEY','DROPPED_ITEM','EGG','ELDER_GUARDIAN','ENDER_CRYSTAL','ENDER_DRAGON','ENDER_PEARL','ENDER_SIGNAL','ENDERMAN','ENDERMITE','EXPERIENCE_ORB','FALLING_BLOCK','FIREBALL','FIREWORK','FISHING_HOOK','GHAST','GIANT','GUARDIAN','HORSE','IRON_GOLEM','ITEM_FRAME','LEASH_HITCH','MAGMA_CUBE','MINECART','MINECART_CHEST','MINECART_COMMAND','MINECART_FURNACE','MINECART_HOPPER','MINECART_MOB_SPAWNER','MINECART_TNT','MULE','MUSHROOM_COW','OCELOT','PAINTING','PIG','PIG_ZOMBIE','PLAYER','PRIMED_TNT','RABBIT','SHEEP','SILVERFISH','SKELETON','SKELETON_HORSE','SLIME','SMALL_FIREBALL','SNOWBALL','SNOWMAN','SPIDER','SPLASH_POTION','SQUID','THROWN_EXP_BOTTLE','UNDEAD_HORSE','VILLAGER','WITCH','WITHER','WITHER_SKELETON','WITHER_SKULL','WOLF','ZOMBIE','ZOMBIE_VILLAGER') not null, helmet enum('CHAINMAIL_HELMET','DIAMOND_HELMET','GOLD_HELMET','IRON_HELMET','LEATHER_HELMET'), chestplate enum('CHAINMAIL_CHESTPLATE','DIAMOND_CHESTPLATE','GOLD_CHESTPLATE','IRON_CHESTPLATE','LEATHER_CHESTPLATE'), leggings enum('CHAINMAIL_LEGGINGS','DIAMOND_LEGGINGS','GOLD_LEGGINGS','IRON_LEGGINGS','LEATHER_LEGGINGS'), boots enum('CHAINMAIL_BOOTS','DIAMOND_BOOTS','GOLD_BOOTS','IRON_BOOTS','LEATHER_BOOTS'), base_melee_damage tinyint unsigned not null, base_arrow_damage tinyint unsigned, base_knockback_taken_multiplier float(4,2) not null, base_melee_knockback_dealt_multiplier float(4,2) not null, constraint pk_kit primary key (id), constraint fk_kit_designer foreign key (designer) references user(id));

I want to move them all into one file and run them as an SQL program to create this database automatically in the future.

I don't actually know what SQL should look like, style-wise.

  1. Where do I break lines?

  2. How are my naming conventions?

  3. How are comments used in SQL?

  4. Do my datatypes look suitable or are there better ways for me to represent the objects I want?

\$\endgroup\$

closed as off-topic by 200_success, Mast, rolfl Oct 17 '18 at 6:06

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – 200_success, Mast, rolfl
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    \$\begingroup\$ "I don't know how to write SQL" — so did you write this code yourself or not? \$\endgroup\$ – 200_success Oct 4 '18 at 3:51
  • \$\begingroup\$ @200_success I wrote it myself \$\endgroup\$ – theonlygusti Oct 4 '18 at 21:00
2
\$\begingroup\$
  1. For formatting, I would download the following tool, Poor Man's T-Sql Formatter and on GitHub, if you're unsure how to format your SQL. I use it when I have to edit other developers code. It should work fine for MySQL.

Here are the settings I used

screenshot

In one click it will format your SQL to the following:

CREATE TABLE user (
    id SMALLINT unsigned NOT NULL auto_increment
    , CONSTRAINT pk_user PRIMARY KEY (id)
    );

CREATE TABLE discord_user (
    id VARCHAR(20) NOT NULL
    , OWNER SMALLINT unsigned
    , CONSTRAINT pk_discord_user PRIMARY KEY (id)
    , CONSTRAINT fk_discord_user_owner FOREIGN KEY (OWNER) REFERENCES user(id)
    );

CREATE TABLE player (
    uuid VARCHAR(36) NOT NULL
    , OWNER SMALLINT unsigned
    , CONSTRAINT pk_player PRIMARY KEY (uuid)
    , CONSTRAINT fk_player_owner FOREIGN KEY (OWNER) REFERENCES user(id)
    );

CREATE TABLE kit (
    id mediumint unsigned NOT NULL auto_increment
    , designer SMALLINT unsigned
    , name VARCHAR(30) NOT NULL
    , disguise enum('ARMOR_STAND', 'ARROW', 'BAT', 'BLAZE', 'BOAT', 'CAVE_SPIDER', 'CHICKEN', 'COW', 'CREEPER', 'DONKEY', 'DROPPED_ITEM', 'EGG', 'ELDER_GUARDIAN', 'ENDER_CRYSTAL', 'ENDER_DRAGON', 'ENDER_PEARL', 'ENDER_SIGNAL', 'ENDERMAN', 'ENDERMITE', 'EXPERIENCE_ORB', 'FALLING_BLOCK', 'FIREBALL', 'FIREWORK', 'FISHING_HOOK', 'GHAST', 'GIANT', 'GUARDIAN', 'HORSE', 'IRON_GOLEM', 'ITEM_FRAME', 'LEASH_HITCH', 'MAGMA_CUBE', 'MINECART', 'MINECART_CHEST', 'MINECART_COMMAND', 'MINECART_FURNACE', 'MINECART_HOPPER', 'MINECART_MOB_SPAWNER', 'MINECART_TNT', 'MULE', 'MUSHROOM_COW', 'OCELOT', 'PAINTING', 'PIG', 'PIG_ZOMBIE', 'PLAYER', 'PRIMED_TNT', 'RABBIT', 'SHEEP', 'SILVERFISH', 'SKELETON', 'SKELETON_HORSE', 'SLIME', 'SMALL_FIREBALL', 'SNOWBALL', 'SNOWMAN', 'SPIDER', 'SPLASH_POTION', 'SQUID', 'THROWN_EXP_BOTTLE', 'UNDEAD_HORSE', 'VILLAGER', 'WITCH', 'WITHER', 'WITHER_SKELETON', 'WITHER_SKULL', 'WOLF', 'ZOMBIE', 'ZOMBIE_VILLAGER') NOT NULL
    , helmet enum('CHAINMAIL_HELMET', 'DIAMOND_HELMET', 'GOLD_HELMET', 'IRON_HELMET', 'LEATHER_HELMET')
    , chestplate enum('CHAINMAIL_CHESTPLATE', 'DIAMOND_CHESTPLATE', 'GOLD_CHESTPLATE', 'IRON_CHESTPLATE', 'LEATHER_CHESTPLATE')
    , leggings enum('CHAINMAIL_LEGGINGS', 'DIAMOND_LEGGINGS', 'GOLD_LEGGINGS', 'IRON_LEGGINGS', 'LEATHER_LEGGINGS')
    , boots enum('CHAINMAIL_BOOTS', 'DIAMOND_BOOTS', 'GOLD_BOOTS', 'IRON_BOOTS', 'LEATHER_BOOTS')
    , base_melee_damage TINYINT unsigned NOT NULL
    , base_arrow_damage TINYINT unsigned
    , base_knockback_taken_multiplier FLOAT(4, 2) NOT NULL
    , base_melee_knockback_dealt_multiplier FLOAT(4, 2) NOT NULL
    , CONSTRAINT pk_kit PRIMARY KEY (id)
    , CONSTRAINT fk_kit_designer FOREIGN KEY (designer) REFERENCES user(id)
    );


  1. Naming conventions should use the following rules. Reference
    • Ensure the name is unique and does not exist as a reserved word.
    • Keep the length to a maximum of 30 bytes—in practice this is 30 characters unless you are using multi-byte character set.
    • Names must begin with a letter and may not end with an underscore.
    • Only use letters, numbers and underscores in names.
    • Avoid the use of multiple consecutive underscores—these can be hard to read.
    • Use underscores where you would naturally include a space in the name (first name becomes first_name).
    • Avoid abbreviations and if you have to use them make sure they are commonly understood.
    • Also checkout the book Clean Code


  1. MySQL Server supports three comment styles:

The naming convention you use should be clear enough that almost makes comments unnecessary.

  • From a “#” character to the end of the line.

  • From a “-- ” sequence to the end of the line. In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.8.5.6, “'--' as the Start of a Comment”.

  • From a /* sequence to the following */ sequence, as in the C programming language. This syntax allows a comment to extend over multiple lines because the beginning and closing sequences need not be on the same line.


  1. Choosing the Right Type for a Column Reference

    • For optimum storage, you should try to use the most precise type in all cases. For example, if an integer column is used for values in the range from 1 to 99999, MEDIUMINT UNSIGNED is the best type. Of the types that represent all the required values, this type uses the least amount of storage.

    • All basic calculations (+, -, *, and /) with DECIMAL columns are done with precision of 65 decimal (base 10) digits.

    • If accuracy is not too important or if speed is the highest priority, the DOUBLE type may be good enough. For high precision, you can always convert to a fixed-point type stored in a BIGINT. This enables you to do all calculations with 64-bit integers and then convert results back to floating-point values as necessary.

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.