I have three tables in my database:
COMPANY(ID, NAME)
COUPON(ID, TITLE)
COMPANY_COUPON(COMPANY_ID, COUPON_ID)
Company can create coupons, so when it creates coupon, coupon's ID and ID of the company will be added into COMPANY_COUPON table. When company deletes coupon it also will be deleted from COUPON and COMPANY_COUPON due to FOREIGN KEY. When I delete company it also deleted from COMPANY and COMPANY_COUPON due to FOREIGN KEY, but all its coupons stay in COUPON table, I tried to add into COUPON table FOREIGN KEY(ID) REFERENCES COMPANY_COUPON(COUPON_ID), but because of it I can't insert new coupons into COUPON table.
Will be happy for answers how to create right tables and how to add something that will help to existing tables. Maybe some joins can help with it, I'm really not strong in sql command yet, sorry...
Also, i was using this method to join COMPANY_ID and COUPON_ID in COMPANY_COUPON table, maybe there is some better way to do it.
@Override
public void linkCompanyCoupon(long companyID, long couponID) throws CouponSystemException {
Connection connection = pool.getConnection();
String CREATE_COUPON = "INSERT INTO COMPANY_COUPON (COMPANY_ID, COUPON_ID) VALUES (?,?)";
try (PreparedStatement pstmt = connection.prepareStatement(CREATE_COUPON)) {
pstmt.setLong(1, companyID);
pstmt.setLong(2, couponID);
pstmt.executeUpdate();
} catch (SQLException e) {
throw new CouponSystemException("Linking company to the coupon is failed. ", e);
} finally {
pool.returnConnection(connection);
}
}
This one is creates coupon, and inside it i use linkCompanyCoupon();
public void createCoupon(Coupon coupon) throws CouponSystemException {
// Checking if name of the new coupon is not duplicate
Optional<Coupon> isCouponTitleDuplicate = Optional.ofNullable(couponDBDAO.getCouponByTitle(coupon.getTitle()));
if (!isCouponTitleDuplicate.isPresent()) {
couponDBDAO.createCoupon(coupon);
companyDBDAO.linkCompanyCoupon(loggedCompany, couponDBDAO.getCouponByTitle(coupon.getTitle()).getId());
} else {
throw new CouponSystemException("Coupon title: " + coupon.getTitle()
+ " already exists in database, titles and IDs of the coupons must be unique.");
}
}