JPA Structures Generating Bad Database Design
In my project, the administrator (User) can set up to receive scheduled emails about any user he chooses.
I need a database with the following design:
TABLE User (
UserId INT PRIMARY KEY AUTO_INCREMENT,
Email VARCHAR,
FirstName VARCHAR,
LastName VARCHAR
IsAdmin BOOL,
...
)
TABLE Email_Schedule (
ScheduleId INT PRIMARY KEY AUTO_INCREMENT, /* this is not necessary */
AdminId INT, /* could be replaced by a composite foreign primary keys */
UserId INT,
FOREIGN KEY (AdminId) REFERENCES User (UserId),
FOREIGN KEY (UserId) REFERENCES User (UserId)
)
Following code in my Java classes for JPA entity:
@Entity
public class Email_Schedule {
@Id
private int scheduleId;
@ManyToOne(targetEntity = User.class)
private List<User> admins = new LinkedList<>();
@ManyToOne(targetEntity = User.class)
private List<User> users = new LinkedList<>();
public Email_Schedule() {
super();
}
public Email_Schedule(User admin, User user) {
super();
this.admins.add(admin);
this.users.add(user);
}
// setters and getters...
creates a database with the following schema:
TABLE USER (
...
)
TABLE SCHEDULE (
ScheduleId INT PRIMARY KEY AUTO_INCREMENT
)
TABLE Email_Schedule (
ScheduleId INT,
Users INT,
Admins INT,
FOREIGN KEY (ScheduleId) REFERENCES SCHEDULE(ScheduleId),
FOREIGN KEY (Users) REFERENCES USER (UserId),
FOREIGN KEY (Admins) REFERENCES USER (UserId)
)
My question is, why did he create a useless table for ScheduleId
and reference it from another table and not just use it directly in the table Email_Schedule
?
The problem seems to be related to ScheduleId
.. I tried not to use it while building IdClass
, but I had various errors and incorrect database constructs.
EclipseLink uses TABLE
sequence generation for scheduleId
. This appears to be the default.
You can use a table to generate an ID in any database. This strategy is completely portable across databases and will be automatically generated for you when schema generation is enabled.
According EclipseLink documentation, you may need to use a generating strategy IDENTITY
for scheduleId
to avoid TABLE
appraoch.
@GeneratedValue(strategy=GenerationType.IDENTITY)
Note that if you are using a strategy AUTO
like below, then even so, EclipseLink can choose a strategy TABLE
for generating the ID.
@GeneratedValue(strategy=GenerationType.AUTO)
Using the default generation strategy
Specifying an AUTO strategy allows EclipseLink to select a strategy to use. Typically, EclipseLink chooses TABLE as its strategy because it is the most portable strategy. However, when AUTO is specified, schema generation must be used at least once for the default table to be created in the database.
More details here in the PrimaryKey and GeneratedValue Documentation
source to share