relationship and build database

Question

For an excercise I need to build something like :

For a course I need to create a review that is made up out of certain reviewlines and feedbackscores.

This review object (unique instance) needs to be filled in by a list of customers.

Depending on the course the review is for, the review will change (e.g.for one course the number of reviewlines and feedbackscores will change). Each customer can be enrolled in more then one course and each review is specific for him.

Now how do I need to see the relationsship between "review" object (unique instance) and "customer" if I want to use JPA to save this all to the db?

• A customer can have more then one review he/she needs to fill in.
• A certain review object needs to be filled in by many customers (but this is a review object with a certain build [reviewlines and feedbackscores]) and unique for him.

Maybe I see it to complex but what is the best way to build this?

Answer 1

Try the following:

I think it's covered all your design points.

I am trying to read between the lines of your comments, and I think you want to implement a system where you capture a number of 'rules' for the Review (I'm guessing, but examples may be that reviews can be up to n lines, there must be at least m CustomerReviews before the Review gains a degree of quality). If this is indeed the case, I have created a ReviewTemplate class:

• ReviewTemplate would have attributes/columns for each of value you would need. These attributes/columns are duplicated on Review
• Populate ReviewTemplate with a number of rows, then create a row in Course and link it to one ReviewTemplate
• When a Course needs a Review, copy the fields from the ReviewTemplate into the Review
• In Java, implement the business rules for Review using the copied values - not the values on ReviewTemplate.

Why copy the values? Well, I bet that at some point, users want to edit the ReviewTemplate table. If so, what happens to the Review objects using the edited ReviewTemplates? Does the modified value on ReviewTemplate somehow invalidate past Reviews and break your business logic? No, because you copied the rule values to Review and so past Reviews will not change.

EDIT: Answers to specific questions

How do you see the duplicating? I can create an entity ReviewTemplate with the specified attributes. In this entity there will be a relationship with reviewlines and feedbackscores.

I see each ReviewTemplate as holding prototypical values for a particular 'type' of Review, which just might include a default reviewLine (but that might not make sense) and a default feedbackScore. When you create the Review, you would do the following:

1. Instantiate the Review and populate with values from ReviewTemplate

2. Instantiate as many CustomerReview objects as you need, linking them to the relevant Customer objects (I infer this step from your previous comments. It might also make sense to omit this step until a Customer voluntarily elects to review a Course)

   1. (If appropriate) Populate the CustomerReview attribute feedbackScore with the default value from ReviewTemplate
   2. Instantiate CustomerReviewLine records as appropriate

If you follow this approach, you do not need to add a relationship between ReviewTemplate and CustomerReviewLines.

When I e.g. state that customers 1 to 4 need to fill in the review 4 specific "objects" need to be created that will hold the information and also 4 sets of the needed reviewlines and feedbackscores need to be created so they all can hold the information.

Absolutely.

I just don't know how to implement this is a JPA structure so the information is hold in the db ... ?

JPA allows you to attack the problem in many ways, but the best practice is to manually create both the DB schema and the Java classes (eg see https://stackoverflow.com/a/2585763/1395668). Therefore, for each entity in the diagram, you need to:

1. Write SQL DDL statements to create the table, columns, primary key and foreign keys, and
2. Write a Java class denoted with the @entity annotation. Within the class, you will also need to annotate the id (primary key) with @id and the relationships with @OneToMany or @ManyToOne (theirs additional parameters in the annotation to set as well).

Now, on the JPA side, you can do things like:

ReviewTemplate template = course.getReviewTemplate(); //assuming the variable course
Review review = new Review();
review.setCourse(course);
review.setRuleOne(template.getRuleOne());
// Copy other properties here

EntityManager em = // get the entity manager here
em.persist(review);

// Assume a set or list of customers
for (Customer customer : customers) {
    CustomerReview cr = new CustomerReview();
    cr.setReview(review);
    cr.setCustomer(customer); 
    cr.setFeedbackScore(template.getDefaultFeedbackScore());
    // set other CustomerReview properties here

    em.persist(cr);

    // You can create CustomerReviewLine here as well

If written inside a standard EJB Session Bean, this will all be nicely transacted, and you will have all your new records committed into the DB.

EDIT 2: Additional question

(I'm assuming that the second comment completely supersedes the first)

So when I create a reviewtemplate and I link it to a bunch of customers I write the template to the db and create a bunch of reviews based on the template but linked to the specific customer and with his own unique reviewlines and feedbackscores. Like I see it now the reviewline (more a question or discription) is the same for each review (of a template), it is only the score that changes between the customers

I finally think I understand ReviewLine. I had thought it a place where the Customer enters lines of text the comprise the CustomerReview. I now believe that ReviewLine is a specific question that the Customer is asked, and which the Customer provides a feedbackScore.

With this understanding, here is an updated ER/Class diagram.

Note that there are some significant changes - there are several more tables:

1. ReviewLineTemplate provides a place for template questions to be stored on a ReviewTemplate

2. When a Review is instantiated/inserted (which is a copy of a specific ReviewTemplate), the ReviewLineTemplates are copied as ReviewLines. The copy operation allows two important features:

   1. On creation, a Review and its ReviewLines can be customized without affecting the ReviewTemplate or ReviewLineTemplate
   2. Over time, the ReviewTemplate and ReviewLineTemplate can be updated, edited and continually improved, without changing the questions that the Customer has already answered. If CustomerFeedbackScore were linked to ReviewLineTemplate directly, then editing the ReviewLineTemplate would change the question that the Customer has answered, silently invalidating the feedbackScore.

3. FeedbackScore has been moved to a join-table between ReviewLine and CustomerReview.

Note that this model is fully denormalised which makes it more 'correct' but harder to build a GUI for. A common 'optimization' might be to introduce:

• 10 (say) columns on ReviewTemplate and Review called reviewLine1 through reviewLine10.
• 10 (say) columns on CustomerReview called feedbackScore1 through feedbackScore10.
• Remove the ReviewTemplateLine, ReviewLine and CustomerReviewLine tables

Doing so is not normalised, and may introduce a set of other problems. YMMV

Answer 2

The structure of data always depends on the requirements, and there never exists a "one-and-only" solution. So, do you need maximised atomiticy or a high performance data system?

The fastest and easiest solution would be not using a database, but hash tables. In your case, you could have something like 3 hash tables for customer, review, and probably another one for the n:n relationship. Or if you're using a database, you could just store an array of the review-primary-keys in one field in the customer table.

However, we all learn in school to do atomicity, so let's do that (I just write the primary/foreign keys!):

• Customer (unique_ID, ...)
• Review (unique_ID, ...)
• Customer_Review (customer_ID, review_ID, ...) --> n:n-relationship

The Customer_Review describes the n:n-relationship between customers and reviews. But if there is only one customer per review possible, you'll do that like this:

• Customer (unique_ID, ...)
• Review (pk: unique_ID, fk: customer_ID, ...) --> 1:n-relationship

However, I suggest you need to learn ERM as a good starting point: http://en.wikipedia.org/wiki/Entity_relationship_model

Answer 3

You need a ManyToMany relation :

• One customer -> several reviews.
• One review -> several customers.

So you will have 3 tables in your database schema : Customer, review and a junction table with the customer ID and the review ID.

See Wikipedia : Many to Many