Check if item was buyed via Database

Hey,

Today I would like to check if item was buyed (via database)



Here I have some Hats in my shop, I just want to check if player has already bought this items to lock the possibility to buy it again.

I imagine a system with (1/0)
0 : Player didn't bought this item
1 : Player bought this item so lock the possibility to buy it again.

My system to check if player has bought at least 1 time works perfectly.
But now I would like to select the value (0 or 1) and set conditions


This works perfectly :

Code:

@EventHandler
    public void onInventoryClickShop2(InventoryClickEvent e)
    {
        Player p = (Player)e.getWhoClicked();
        if (!ChatColor.stripColor(e.getInventory().getName()).equalsIgnoreCase("Quake Shop")) {
            return;
        }
        e.setCancelled(true);
        if (e.getCurrentItem().getItemMeta().getDisplayName().contains(ChatColor.RED + "Lantern Hat"))
        {
            int balance1 = this.sql.getBalance(p);
            if (balance1 == 0)
            {
                p.sendMessage("Vous n'avez pas d'argent sur votre compte pour cet achat");
                return;

            }
            if (balance1 < this.pricehat2) {
                p.sendMessage("Vous n'avez pas assez d'argent sur votre compte pour cet achat");
                return;
            }
            int newbalance = balance1 - this.pricehat1;
            if (balance1 >= this.pricehat2)
            {
               
                // If (Value = 0)
               
                if (e.getCurrentItem().getItemMeta().getDisplayName().contains(ChatColor.RED + "Lantern Hat")) {}
                p.sendMessage(ChatColor.GOLD + "You purchased Lantern Hat!");
               
                // if (Value = 1)
                //e.setCancelled(true);
                //p.closeInventory();
                //p.sendMessage("You already bought this item.");
               
            }
            try
            {
                PreparedStatement rs = SQLConnection.connection.prepareStatement("UPDATE joueurs SET coins = ? WHERE uuid = ?");
                rs.setInt(1, newbalance);
                rs.setString(2, p.getUniqueId().toString());
                rs.executeUpdate();
                rs.close();
            }
            catch (SQLException event)
            {
                event.printStackTrace();
            }

            try
            {
                PreparedStatement prst = SQLConnection.connection.prepareStatement("UPDATE joueurs SET lantern = ? WHERE uuid = ?");
                prst.setInt(1, 1);
                prst.setString(2, p.getUniqueId().toString());
                prst.executeUpdate();
                prst.close();
            }
            catch (SQLException event)
            {
                event.printStackTrace();
            }
        }
    }

But how to check if the value in my database is 0 to buy it ?
And how to check if the value in my database is 1 to closeInventory N






Thanks for the help !

 

You're using Statements as if they are PreparedStatements. You say "WHERE uuid=?" but you never define what "?" should be. You need to do that before executing the query.

 

@Caedus

I don't define what ? should be. My event works perfectly

I changed my way to check buyed Items and not buyed item. It's easier.

I have now a code, it works

But I need to include conditions : (if value = 0 : buy the item) or (else : p.close Inventory)

How can I wrote it ?

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

@xFoundation
In your post you calculate the new balance to match the balance after a purchase, but you never use that value. Shouldn't it be newBalance >= pricehat2 ?

As for your reformatted question, a simple select query to fetch everything from the table would be enough. I don't see how that would be any more difficult as for fetching the balance of a player.

Code:java

 
Connection connection = ...;
 
String uuid = ...;
 
boolean purchasedLantern = false,
        purchasedSpaceman = false;
 
String sql = "SELECT * FROM `table name here` WHERE `uuid` = ? LIMIT 1;";
try {
    PreparedStatement statement = connection.prepareStatement(query);
    statement.setString(1, uuid);
 
    ResultSet result = statement.executeQuery();
    if (result != null && result.next()) {
        purchasedLantern = result.getInt("lantern") > 0;
        purchasedSpaceman = result.getInt("spaceman") > 0;
    }
} catch (SQLException e) {
    e.printStackTrace();
}
 
if (purchasedLantern) {
    // blabla
}
 

 

@ShaneCraftDev


I tried it but when I do "else" , it doesn't work

Code:

boolean purchasedLantern = false,
                    unpurchasedLantern = false;

            String sql = "SELECT * FROM joueurs WHERE `uuid` = ? LIMIT 1;";
            try {
                PreparedStatement statement = SQLConnection.connection.prepareStatement(sql);
                statement.setString(1, p.getUniqueId().toString());

                ResultSet result = statement.executeQuery();
                if (result != null && result.next()) {
                    purchasedLantern = result.getInt("lantern") == 1;
                    unpurchasedLantern = result.getInt("lantern") == 0;
                }
            } catch (SQLException event) {
                event.printStackTrace();
            }

            if (unpurchasedLantern) {
                p.sendMessage(ChatColor.GOLD + "You purchased Lantern Hat!");
            } else {
                p.sendMessage(ChatColor.GOLD + "You already purchased Lantern Hat!");
            }
        }
    }

 

@xFoundation
I think you need to learn the basic fundamentals of programming. For what reason do you think there is a need to have a value stored for "purchased" and "unpurchased"? Checking of purchased is true or false would do the same.

Code:

if (purchasedLantern) { /* purchased */ } else { /* not purchased */ } 

 

@ShaneCraftDev

Sorry if i look like an idiot. I'm coding a plugin for the fun.

Now I have this in my code:

Code:

// If player click on the item called Spaceman Hat--------------------------------------------------
    @EventHandler
    public void onInventoryClickShop(InventoryClickEvent e)
    {
        Player p = (Player)e.getWhoClicked();
        if (!ChatColor.stripColor(e.getInventory().getName()).equalsIgnoreCase("Quake Shop")) {
            return;
        }
        e.setCancelled(true);
        if (e.getCurrentItem().getItemMeta().getDisplayName().contains(ChatColor.RED + "Spaceman Hat"))
        {
         
            // If player doesn't have enough money --------------------------------------------------
         
            int balance = this.sql.getBalance(p);
            if (balance == 0)
            {
                p.sendMessage("Vous n'avez pas d'argent sur votre compte pour cet achat");
                return;

            }
            if (balance < this.pricehat1) {
                p.sendMessage("Vous n'avez pas assez d'argent sur votre compte pour cet achat");
                return;
            }

            // Buying the item and update his balance and set 1 in the database (player buy it) --------------------------------------------------
         
            int newbalance = balance - this.pricehat1;
            if (balance >= this.pricehat2)
            {

                if (e.getCurrentItem().getItemMeta().getDisplayName().contains(ChatColor.RED + "Lantern Hat")) {}
                p.sendMessage(ChatColor.GOLD + "You purchased Lantern Hat!");

            }
            try
            {
                PreparedStatement rs = SQLConnection.connection.prepareStatement("UPDATE joueurs SET coins = ? WHERE uuid = ?");
                rs.setInt(1, newbalance);
                rs.setString(2, p.getUniqueId().toString());
                rs.executeUpdate();
                rs.close();
            }
            catch (SQLException event)
            {
                event.printStackTrace();
            }

            try
            {
                PreparedStatement prst = SQLConnection.connection.prepareStatement("UPDATE joueurs SET spaceman = ? WHERE uuid = ?");
                prst.setInt(1, 1);
                prst.setString(2, p.getUniqueId().toString());
                prst.executeUpdate();
                prst.close();
            }
            catch (SQLException event)
            {
                event.printStackTrace();
            }

            // If player already buy the item --------------------------------------------------

            boolean purchasedSpaceman = false;

            String sql = "SELECT * FROM joueurs WHERE `uuid` = ? LIMIT 1;";
            try {
                PreparedStatement statement = SQLConnection.connection.prepareStatement(sql);
                statement.setString(1, p.getUniqueId().toString());

                ResultSet result = statement.executeQuery();
                if (result != null && result.next()) {
                    purchasedSpaceman = result.getInt("spaceman") > 0;
                }
            } catch (SQLException ev) {
                ev.printStackTrace();
            }

            if (purchasedSpaceman) { /* purchased */ } else { /* not purchased */ }
            p.sendMessage(ChatColor.GOLD + "You already purchased Lantern Hat!");
        }
    }

In my game chat, it say both message in the same time:

 

@xFoundation
There is no reason why it would not print. The message is prompted to the user outside of the scope which checks if the user has purchased the item or not. Your provided code is a bit all over the place, I have cleaned it up a bit for you with a working example (might have mistaken some fields in your db table "joueurs", I assumed coins is in stored in this table as well.

Code:java

 
private static final String QUAKE_SHOP = "Quake Shop";
 
private static final String[] ITEM_NAMES = ["Lantern Hat", "Spaceman Hat"];
 
@EventHandler
public void onInventoryClickEvent(InventoryClickEvent event) {
    Inventory inventory = event.getInventory();
    // you have to be carefull with checking by name, because people can rename any chest/furnace/hopper...
    // best would be to make a class that extends from Inventory and check if the inventory is an instance of your inventory class
    if (QUAKE_SHOP.equalsIgnoreCase(ChatColor.stripColor(inventory.getName()))) {
        // event.setCancelled(true); // optional
        return; // not our shop, stop function
    }
 
    Player player = event.getWhoClicked();
    String uuid = player.getUniqueId().toString();
 
    Connection connection = ...;
 
    int balance = -1;
    boolean purchasedLantern = false,
           purchasedSpaceman = false;
 
    String query = "SELECT * FROM `joueurs` WHERE `uuid` = ? LIMIT 1;";
    try {
       PreparedStatement statement = connection.prepareStatement(query);
       statement.setString(1, uuid);
 
       ResultSet result = statement.executeQuery();
       if (result != null && result.next()) {
          balance = result.getInt("coins");
           purchasedLantern = result.getInt("lantern") > 0;
           purchasedSpaceman = result.getInt("spaceman") > 0;
       }
    } catch (SQLException ex) {
       e.printStackTrace();
    }
 
    // at this point we have fetched everything at once.
 
    if (balance > 0) {
        ItemStack stack = event.getCurrentItem();
        // you can strip the color directly, we already know we have the correct inventory
        String itemName = ChatColor.stripColor(stack.getItemMeta().getDisplayName());
 
        final String[] names = ITEM_NAMES;
        if (names[0].equalsIgnoreCase(itemName)) {
            // the item name is lantern hat
            if (!purchasedLantern) {
                // the player has not yet purchased the lantern hat item
                int laternCost = 80; // temporary
 
                int newBalance = balance - lanternCost;
                if (newBalance >= lanternCost) {
                    // we can afford the lantern
 
                    query = "UPDATE `joueurs` SET `coins` = ?, `lantern` = 1 WHERE `uuid` = ?;";
                    try {
                        PreparedStatement statement = connection.prepareStatement(query);
                        statement.setInt(1, newBalance);
                        statement.setString(2, uuid);
                        statement.executeNonQuery();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            } else {
                // the player has already purchased the lantern hat item
            }
        }
 
        // repeat the above, but for "spaceman hat"
        if (names[1].equalsIgnoreCase(itemName)) {
            // the item name is spaceman hat
            if (!purchasedSpaceman) {
                // the player has not yet purchased the spaceman hat item
            } else {
                // the player has already purchased the spaceman hat item
            }
        }
    } else {
        // the player has no money at all.
    }
}
 
 

 

@xFoundation Why exactly are you using a prepared statement for this? The players UUID will never be cause of an injection. If it was player chat of course but for just checking against a UUID I see no need.

Anyway to the issue, you set it to be bought and then you check if it's been bought which will of course be true as it just happened.

 

Thanks to spend time @ShaneCraftDev to help me, it's cool

I changed little things, it caused some errors like:

Code:

private static final String[] ITEM_NAMES = ["Lantern Hat", "Spaceman Hat"];

I wrote:

Code:

private static final String[] ITEM_NAMES = {"Lantern Hat", "Spaceman Hat"};

You said:

Code:

statement.executeNonQuery();

I wrote:

Code:

statement.executeNonQuery();

@bwfcwalshy

I'm fixing this issue :d

EDIT: I used your example but nothing happened in game :/

Code:

private static final String QUAKE_SHOP = "Quake Shop";
    
    private static final String[] ITEM_NAMES = {"Lantern Hat", "Spaceman Hat"};

Code:

@EventHandler
    public void onInventoryClickEvent(InventoryClickEvent event) {
        Inventory inventory = event.getInventory();
        // you have to be carefull with checking by name, because people can rename any chest/furnace/hopper...
        // best would be to make a class that extends from Inventory and check if the inventory is an instance of your inventory class
        if (QUAKE_SHOP.equalsIgnoreCase(ChatColor.stripColor(inventory.getName()))) {
            event.setCancelled(true);// event.setCancelled(true); // optional
            return; // not our shop, stop function
        }
    
        Player player = (Player) event.getWhoClicked();
        String uuid = player.getUniqueId().toString();
    
        Connection connection = SQLConnection.connection;
    
        int balance = -1;
        boolean purchasedLantern = false,
               purchasedSpaceman = false;
    
        String query = "SELECT * FROM `joueurs` WHERE `uuid` = ? LIMIT 1;";
        try {
           PreparedStatement statement = connection.prepareStatement(query);
           statement.setString(1, uuid);
    
           ResultSet result = statement.executeQuery();
           if (result != null && result.next()) {
              balance = result.getInt("coins");
               purchasedLantern = result.getInt("lantern") > 0;
               purchasedSpaceman = result.getInt("spaceman") > 0;
           }
        } catch (SQLException e) {
           e.printStackTrace();
        }
    
        // at this point we have fetched everything at once.
    
        if (balance > 0) {
            ItemStack stack = event.getCurrentItem();
            // you can strip the color directly, we already know we have the correct inventory
            @SuppressWarnings("unused")
            String itemName = ChatColor.stripColor(stack.getItemMeta().getDisplayName());
    
            final String[] names = ITEM_NAMES;
            if (names[0].equalsIgnoreCase(ChatColor.RED + "Lantern Hat")) {
                // the item name is lantern hat
                if (!purchasedLantern) {
                    // the player has not yet purchased the lantern hat item
                    int pricehat2 = 700; // temporary
    
                    int newBalance = balance - pricehat2;
                    if (newBalance >= pricehat2) {
                        player.sendMessage(ChatColor.GOLD + "You purchased Lantern Hat");// we can afford the lantern
    
                        query = "UPDATE `joueurs` SET `coins` = ?, `lantern` = 1 WHERE `uuid` = ?;";
                        try {
                            PreparedStatement statement = connection.prepareStatement(query);
                            statement.setInt(1, newBalance);
                            statement.setString(2, uuid);
                            statement.executeQuery();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                } else {
                    player.sendMessage(ChatColor.GOLD + "You already purchased Lantern Hat");// the player has already purchased the lantern hat item
                }
            }
    
            // repeat the above, but for "spaceman hat"
            if (names[1].equalsIgnoreCase(ChatColor.RED + "Spaceman Hat")) {
                // the item name is spaceman hat
                if (!purchasedSpaceman) {
                    // the player has not yet purchased the spaceman hat item
                    int pricehat1 = 700; // temporary
                   
                    int newBalance = balance - pricehat1;
                    if (newBalance >= pricehat1) {
                        player.sendMessage(ChatColor.GOLD + "You purchased Spaceman Hat");// we can afford the lantern
    
                        query = "UPDATE `joueurs` SET `coins` = ?, `spaceman` = 1 WHERE `uuid` = ?;";
                        try {
                            PreparedStatement statement = connection.prepareStatement(query);
                            statement.setInt(1, newBalance);
                            statement.setString(2, uuid);
                            statement.executeQuery();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
               
                } else {
                    player.sendMessage(ChatColor.GOLD + "You already purchased Spaceman Hat");// the player has already purchased the spaceman hat item
                }
            }
        } else {
            player.sendMessage(ChatColor.GOLD + "You don't have money");// the player has no money at all.
        }
    }

 

@xFoundation
Yes, I am stupid. We check if the name of the inventory is equal to our desired one, but obviously we should check if it's not. Something else I overlooked was checking if the ItemStack instance is null. Replace the following

Code:java

 
    if (QUAKE_SHOP.equalsIgnoreCase(ChatColor.stripColor(inventory.getName()))) {
        // event.setCancelled(true); // optional
        return; // not our shop, stop function
    }
 

with

Code:java

 
    if (!QUAKE_SHOP.equalsIgnoreCase(ChatColor.stripColor(inventory.getName()))) {
        // event.setCancelled(true); // optional
        return; // not our shop, stop function
    }
 
    ItemStack stack = event.getCurrentItem();
    // or use event.getCursor() which also returns an ItemStack
    // By just looking at the API reference, I have no idea which one you should pick
    if (stack == null || (stack != null && stack.getType() == Material.AIR)) {
        // event.setCancelled(true); // optional
        return; // no item was selected, cancel the event
    }
 

 

@ShaneCraftDev

I did what you said but nothing happens :/



I just can pick up items :/

 

@xFoundation
You asked for how to check if someone has bought an item, what I wrote does that check. Nowhere did you state people are not allowed to pick up items.

 

@ShaneCraftDev

I added this:

Code:

@EventHandler
    public void onClickInv(InventoryClickEvent e) {
        e.setCancelled(true);
    }

So now people can no longer pick up item in all inventories.

But the messages I written doesn't appear :/ when you wrote it:
// we can afford the lantern
// the player has already purchased the lantern hat item

Here is the entire code:

Code:

@EventHandler
    public void onInventoryClickEvent(InventoryClickEvent event) {
        Inventory inventory = event.getInventory();
        // you have to be carefull with checking by name, because people can rename any chest/furnace/hopper...
        // best would be to make a class that extends from Inventory and check if the inventory is an instance of your inventory class
        if (!QUAKE_SHOP.equalsIgnoreCase(ChatColor.stripColor(inventory.getName()))) {
            // event.setCancelled(true); // optional
            return; // not our shop, stop function
        }

        ItemStack istack = event.getCurrentItem();
        // or use event.getCursor() which also returns an ItemStack
        // By just looking at the API reference, I have no idea which one you should pick
        if (istack == null) {
            // event.setCancelled(true); // optional
                // no item was selected, cancel the event
            return;
        }

        Player player = (Player) event.getWhoClicked();
        String uuid = player.getUniqueId().toString();

        Connection connection = SQLConnection.connection;

        int balance = -1;
        boolean purchasedLantern = false,
                purchasedSpaceman = false;

        String query = "SELECT * FROM `joueurs` WHERE `uuid` = ? LIMIT 1;";
        try {
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, uuid);

            ResultSet result = statement.executeQuery();
            if (result != null && result.next()) {
                balance = result.getInt("coins");
                purchasedLantern = result.getInt("lantern") > 0;
                purchasedSpaceman = result.getInt("spaceman") > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // at this point we have fetched everything at once.

        if (balance > 0) {
            ItemStack stack = event.getCurrentItem();
            // you can strip the color directly, we already know we have the correct inventory
            @SuppressWarnings("unused")
            String itemName = ChatColor.stripColor(stack.getItemMeta().getDisplayName());

            final String[] names = ITEM_NAMES;
            if (names[0].equalsIgnoreCase(ChatColor.RED + "Lantern Hat")) {
                // the item name is lantern hat
                if (!purchasedLantern) {
                    // the player has not yet purchased the lantern hat item
                    int pricehat2 = 700; // temporary

                    int newBalance = balance - pricehat2;
                    if (newBalance >= pricehat2) {
                        player.sendMessage(ChatColor.GOLD + "You purchased Lantern Hat");// we can afford the lantern

                        query = "UPDATE `joueurs` SET `coins` = ?, `lantern` = 1 WHERE `uuid` = ?;";
                        try {
                            PreparedStatement statement = connection.prepareStatement(query);
                            statement.setInt(1, newBalance);
                            statement.setString(2, uuid);
                            statement.executeQuery();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }
                } else {
                    player.sendMessage(ChatColor.GOLD + "You already purchased Lantern Hat");// the player has already purchased the lantern hat item
                }
            }

            // repeat the above, but for "spaceman hat"
            if (names[1].equalsIgnoreCase(ChatColor.RED + "Spaceman Hat")) {
                // the item name is spaceman hat
                if (!purchasedSpaceman) {
                    // the player has not yet purchased the spaceman hat item
                    int pricehat1 = 700; // temporary

                    int newBalance = balance - pricehat1;
                    if (newBalance >= pricehat1) {
                        player.sendMessage(ChatColor.GOLD + "You purchased Spaceman Hat");// we can afford the lantern

                        query = "UPDATE `joueurs` SET `coins` = ?, `spaceman` = 1 WHERE `uuid` = ?;";
                        try {
                            PreparedStatement statement = connection.prepareStatement(query);
                            statement.setInt(1, newBalance);
                            statement.setString(2, uuid);
                            statement.executeQuery();
                        } catch (SQLException ex) {
                            ex.printStackTrace();
                        }
                    }

                } else {
                    player.sendMessage(ChatColor.GOLD + "You already purchased Spaceman Hat");// the player has already purchased the spaceman hat item
                }
            }
        } else {
            player.sendMessage(ChatColor.GOLD + "You don't have money");// the player has no money at all.
        }
    }

 

@xFoundation
It should be

Code:

stack == null || (stack != null && stack.getType() == Material.AIR)

And to stop people from taking items, calling

Code:

event.setCancelled(true);

at the bottom of the function will prevent the player from taking any item from the inventory.

Running the following without all of the db component seems to work just fine

Code:java

 
@EventHandler
public void onInventoryClickEvent(InventoryClickEvent event) {
    Inventory inventory = event.getInventory();
 
    // you have to be carefull with checking by name, because people can rename any chest/furnace/hopper... 
    // best would be to make a class that extends from Inventory and check if the inventory is an instance of your inventory class
    if (!QUAKE_SHOP.equalsIgnoreCase(ChatColor.stripColor(inventory.getName()))) {
        // event.setCancelled(true); // optional
        return; // not our shop, stop function
    }
 
    ItemStack stack = event.getCurrentItem();
    if (stack == null || (stack != null && stack.getType() == Material.AIR)) {
        return;
    }
 
    int balance = 800;
    boolean purchasedLantern = false,
           purchasedSpaceman = false;
 
    // at this point we have fetched everything at once.
    System.out.println(stack);
    if (balance > 0) {
        // you can strip the color directly, we already know we have the correct inventory
        String itemName = ChatColor.stripColor(stack.getItemMeta().getDisplayName());
 
        final String[] names = ITEM_NAMES;
        if (names[0].equalsIgnoreCase(itemName)) {
            // the item name is lantern hat
            if (!purchasedLantern) {
                // the player has not yet purchased the lantern hat item
                int lanternCost = 80; // temporary
 
                int newBalance = balance - lanternCost;
                if (newBalance >= lanternCost) {
                    // we can afford the lantern
 
                    System.out.println("purchased lantern hat");
                }
            } else {
                // the player has already purchased the lantern hat item
                System.out.println("unbable to purchase lantern hat");
            }
        } 
 
        // repeat the above, but for "spaceman hat"
        if (names[1].equalsIgnoreCase(itemName)) {
            // the item name is spaceman hat
            if (!purchasedSpaceman) {
                // the player has not yet purchased the spaceman hat item
            } else {
                // the player has already purchased the spaceman hat item
            }
        }
    } else {
        // the player has no money at all.
    }
 
    event.setCancelled(true);
}
 
 

 

@bwfcwalshy
A prepared statement is precompiled, which makes it faster if you store it.

@xFoundation @ShaneCraftDev
Your database design seems strange. If you add another item, you need to add a column!
I would make a table "Items" which contains all items (lantern head, etc.) and an unique id for them.
Then make a table "Purchases" and put the player UUID and the ID of the item in it. Maybe make an unique purchase id too.

Wouldn't that be nicer?

Code:

CREATE TABLE Items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(64) NOT NULL
);

CREATE TABLE Purchases (
    purchase_id INT PRIMARY KEY AUTO_INCREMENT,
    player_uuid VARCHAR(36) NOT NULL,
    item_id INT,
    FOREIGN KEY (`item_id`)
        REFERENCES Items (`item_id`)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Then you can just add new Items in the "Items" table and player purchases in the "Purchases" table. A check for ownership would mean checking if there is a key with the "item_id" and "player_uuid" in "Purchases".

Correct me if I'm wrong!

 

@I Al Istannen
I did not create that table, I just adapted to his "design". It's not like I do not how to use design in SQL.. If you intent to improve his design, what advantage does adding that additional table have over his current table as there is no foreign key to the "player" table where the users total balance is stored as the player is only ommited to have one purchase of each unique item. I would advice you to use better naming conventions for the table and column names in your design. However I think OP is not that "advanced" in programming.

 

@ShaneCraftDev

Although you did just change what he said, I think his post was for OP's benefit, and I concur that OP is probably not very good at programming. He is right though, and the technique he applied is called database normalisation, and it does improve database handling, and allow him to add new items without issue. The names he has for his tables and columns are standard for SQL, although this is different from Java.

You are right that there should be a reference to the players table, though.

 

@ShaneCraftDev @mythbusterma
I totally skipped over his "players" table. In fact I am still not sure where it is, but mythbusterma is right, you should reference that with the "player_uuid" probably.

@ShaneCraftDev
I know you just adapted to his design, but I would argue that OPs design is not that great and wanted to show him a different, maybe better working way. I tagged you, as I wanted you to have a look over it, I am just learning to take some steps in SQL.

Naming conventions:
I looked around and the longer I looked the more variations I saw. I haven't found anything "official", so if you could provide me with some links I would be grateful!

I appreciate that you two took the time and had a look at it, it is always great to get some feedback, especially when learning new things!

Have a nice day

 

@I Al Istannen
The original table name for players was called "joueurs", which is French for "players".

Naming conventions:
There are many debates about SQL naming conventions such as plurals or singular names for tables. I devote myself to using singular names in any entity relational database. When you think about mapping tables to classes or using ORM, you do not want your classes to contain plurals either. I wouldn't program a "Players" class, as the class would only contain information about 1 player. Using ORM with a framework like EntityFramework in C# would create such classes automatically for you. You then have to manually add a mapping entry and refactor the class name to change the plural name to a singular one.
(I know LINQ to SQL tries to fix such mistakes for you, but that might not succeed as one can register a "mouse" entity, but the plural of mouse becomes mice.)

I would not recommend prefixing column names as well, if we take this simple query we can see why prefixing a column name is redundant:

Code:sql

SELECT b.balance, c.* FROM `bankaccount` b, `customer` c WHERE b.customer_id = c.id;

vs

Code:sql

SELECT b.bankaccount_balance, c.* FROM `bankaccount` b, `customer` c WHERE b.customer_id = c.customer_id;

We know the alias "b" is referencing the "bankaccount" table, making the column "balance" prefixed with the name of the table redundant. The only column with a table name prefix should be that of a foreign key.

However, a lot of programmers prefix their primary key with the table name. The only reason for prefixing the primary key to match the foreign key would be for automatic joins. Personally I do not like to prefix my primary key either. As when I think about classes and mapping my tables to them, I would not create a "Customer" class with a property named "CustomerId" (c#). I'd rather call Id on an instance, as I know which class that instance is referencing. Another reason (personally!) to not prefix column names is the fact that when you want to implement inheritance. If your abstract class "Vehicle" would contain properties such as VehicleId, VehicleFuel... That would look strange when you map that table to a class that would extend from Vehicle e.g. "Truck". Now the class Truck would contain properties such as VehicleId, VehicleFuel, ..., TruckSomething (some unique field only trucks have and regular vehicle does not).

 

@ShaneCraftDev
Oh, french... Thanks!

To the plurals:
I would call a class holding multiple Player instances "Players". Which is why naming a table "Players" makes sense to me, as it contains lots of "Player"s.

I agree that keys should be made with the singular, so "player_id", and yes, just "id" probably looks nicer.

To the tables:
I think "bankaccount" looks terrible. "BankAccount", "bank_account", "bankAccount" whatever, but just "bankaccount" confuses me for a second, till I manage to find the correct word borders.

Prefixes:
Well, apart from the primary key they indeed look redundant and I haven't added them in my draw above. In fact, I have never seen this used, only read about it. It may also be nicer for the primary key, yea.

Regarding inheritance:
Well, I know of no real way to do that in normal relational databases.
If you have this relationship:

(Yes, the attributes may apply to other things too, just assume they are special to their class)

Then I would make a table Vehicle, GroundVehicle and FlyingVehicle.
Vehicle has a key, and the speed. GroundVehicle has the same key and the wheelAmount. FlyingVehicle is similiar to GroundVehicle.
To reconstruct all data about a GroundVehicle you need to formulate a join above all super classes.

I wonder how C# ORM solves this, but I guess that goes a bit too far offtopic...

Thanks for your response though!

 

@I Al Istannen

You can create other tables for land and ground vehicle that reference an ID in vehicle, and contain the extra information, also adding a field to vehicle to specify which one it is.

All languages have ORMs, there's plenty of them. For example JOOQ or Hibernate in Java. I don't think many ORMs respect inheritance, and just store them in a dumb way (or may require that classes are final), but I don't know.

@ShaneCraftDev

Like you said, a lot of things like naming and pluralisation are personal preference in SQL. I do believe it is relatively standard to have all lower case, underbar separated names, however.

 

@mythbusterma
That was pretty much what I wanted to convey with the text under the image. It looks like I failed
I haven't thought about a column for the type, it makes sense though.

Have a nice day