Database design and custom query for this use case ?
-
I refer to the use case as per Swagger documentation Swagger UI[^] I need help to know the following: 1) whether the table pets, table users and table orders below is correct
Create table pets (
petId INTEGER IDENTITY PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
PHOTOURL VARCHAR(30) NOT NULL,
User userId,
TAGS VARACHR(20) NOT NULL,
STATUS ENUM(20)
PRIMARY KEY (petId),
FOREIGN KEY (userId) REFERENCES Users(userId)
);CREATE TABLE Orders (
OrderID int PRIIMARY KEY,
OrderNumber int NOT NULL,
petId int,
quantity int NOT NULL,
shipDate DATE NOT NULL,
STATUS VARCHAR(30) NOT NULL, // can we use Enum here ?
PRIMARY KEY (OrderID),
FOREIGN KEY (petId) REFERENCES Pets(petId)
Not sure to include User based on the Swagger document
);CREATE TABLE Users(
UserID int PRIMARY KEY,
UserName VARCHAR(30) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
EMAIL VARCHAR(30) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
PHONE VARCHAR(30) NOT NULL,
UserStatus VARCHAR(30) NOT NULL,- whether store entity is needed ? Please help me on above as I am not good with DB and have problem in understanding swagger documentation. 3) If I were to use JPA data in Spring, do I use Custom query for querying the status ? Tks