Atm I don't have access to any PC and I only can work with my phone
CREATE TABLE User (
UserID INT PRIMARY KEY,
DiscordUserID VARCHAR(255),
DiscordServerID VARCHAR(255)
);
CREATE TABLE Inventory (
InventoryID INT PRIMARY KEY,
UserID INT UNIQUE,
BalanceID INT,
FOREIGN KEY (UserID) REFERENCES User(UserID),
FOREIGN KEY (BalanceID) REFERENCES Balance(BalanceID)
);
CREATE TABLE Balance (
BalanceID INT PRIMARY KEY,
InventoryID INT,
BalanceAmount DECIMAL(10, 2),
FOREIGN KEY (InventoryID) REFERENCES Inventory(InventoryID)
);
CREATE TABLE Potion (
PotionID INT PRIMARY KEY,
PotionName VARCHAR(255)
);
CREATE TABLE PotionInstruction (
PotionInstructionID INT PRIMARY KEY,
PotionID INT UNIQUE,
PreparationTime INT,
IngredientAmount INT,
FOREIGN KEY (PotionID) REFERENCES Potion(PotionID)
);
CREATE TABLE Ingredient (
IngredientID INT PRIMARY KEY,
IngredientName VARCHAR(255)
);
CREATE TABLE InventoryPotion (
InventoryPotionID INT PRIMARY KEY,
InventoryID INT,
PotionID INT,
PotionQuantity INT,
FOREIGN KEY (InventoryID) REFERENCES Inventory(InventoryID),
FOREIGN KEY (PotionID) REFERENCES Potion(PotionID)
);
CREATE TABLE InventoryIngredient (
InventoryIngredientID INT PRIMARY KEY,
InventoryID INT,
IngredientID INT,
IngredientQuantity INT,
FOREIGN KEY (InventoryID) REFERENCES Inventory(InventoryID),
FOREIGN KEY (IngredientID) REFERENCES Ingredient(IngredientID)
);
CREATE TABLE PotionInstructionIngredient (
PotionInstructionIngredientID INT PRIMARY KEY,
PotionInstructionID INT,
IngredientID INT,
FOREIGN KEY (PotionInstructionID) REFERENCES PotionInstruction(PotionInstructionID),
FOREIGN KEY (IngredientID) REFERENCES Ingredient(IngredientID)
);
CREATE TABLE Shop (
ShopItemID INT PRIMARY KEY,
ItemType VARCHAR(255), -- 'Potion' oder 'Ingredient'
ItemID INT,
Price DECIMAL(10, 2),
FOREIGN KEY (ItemID) REFERENCES Potion(PotionID),
FOREIGN KEY (ItemID) REFERENCES Ingredient(IngredientID)
);
PS I am not berry professional with DB if someone find a mistake pls comment ty