Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

In the CustomerTransactions entity, I have the following field to record what the customer bought:

@ManyToMany
private List<Item> listOfItemsBought;

When I think more about this field, there's a chance it may not work because merchants are allowed to change item's information (e.g. price, discount, etc...). Hence, this field will not be able to record what the customer actually bought when the transaction occurred.

At the moment, I can only think of 2 ways to make it work.

  1. I will record the transaction details into a String field. I feel that this way would be messy if I need to extract some information about the transaction later on.
  2. Whenever the merchant changes an item's information, I will not update directly to that item's fields. Instead, I will create another new item with all the new information and keep the old item untouched. I feel that this way is better because I can easily extract information about the transaction later on. However, the bad side is that my Item table may contain a lot of rows.

I'd be very grateful if someone could give me an advice on how I should tackle this problem.

UPDATE: I'd like to add more information about the current design.

public class Customer implements Serializable {
    @OneToMany
    private List<CustomerTransactions> listOfTransactions;
}

public class CustomerTransactions implements Serializable {
    @ManyToMany
    private List<Item> listOfItemsBought;
}

public class Merchant implements Serializable {
    @OneToMany
    private List<Item> listOfSellingItems;
}
share|improve this question

migrated from dba.stackexchange.com Sep 22 '11 at 14:52

This question came from our site for database professionals who wish to improve their database skills and learn from others in the community.

    
This seems like an architectural question more than a database question. Is there a specific database problem you're encountering, or is this really more domain-design? –  jcolebrand Sep 22 '11 at 14:18
    
I'm sorry if I put this question at the wrong place. I don't have any database problem at the moment. It's more on the architecture side. –  JamesBoyZ Sep 22 '11 at 14:46
    
Ok, in that case I'm going to refer you to our sister site at Programmers where they are much better at architectural questions –  jcolebrand Sep 22 '11 at 14:52
    
OK, just to be clear. I assume you have a Customer that has Many CustomerTransactions. Each CustomerTransaction can have Many Lists of Items and a List of Items can belong to many CustomerTransactions. Are you sure this is how the domain problem should be modelled? –  Martijn Verburg Sep 22 '11 at 15:26
    
@Martijn: I have updated my post with more information. I don't think this "should" be the way because as I said, merchant can update Item's information or even delete Item. I'm not sure how I should do it. Can you give me some advice please? –  JamesBoyZ Sep 22 '11 at 16:04

3 Answers 3

javax.ejb.TransactionAttribute annotation (@TransactionAttribute) can be applied to a bean class or it's methods

public class Customer implements Serializable {
    @OneToMany
    private List<CustomerTransactions> listOfTransactions;
}

public class CustomerTransactions implements Serializable {
    @ManyToMany
    private List<Item> listOfItemsBought;
}

public class Merchant implements Serializable {
    @OneToMany
    private List<Item> listOfSellingItems;
}

Example:

@Transactional(readOnly = false, propagation=Propagation.SUPPORTS)
public long insertTrade(TradeData trade) throws Exception {
   //JDBC Code...
}

@Transactional(readOnly = false, propagation=Propagation.REQUIRED)
public long insertTrade(TradeData trade) throws Exception {
   //JDBC code...
}


@Transactional(readOnly = true)
public TradeData getTrade(long tradeId) throws Exception {
   return em.find(TradeData.class, tradeId);
}

@Transactional(propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public TradeData placeTrade(TradeData trade) throws Exception {
   try {
      insertTrade(trade);
      updateAcct(trade);
      return trade;
   } catch (Exception up) {
      //log the error
      throw up;
   }
}
share|improve this answer

The best thing to do is to grab a piece of paper and a pen (or whiteboard/pen) and draw the real world relationships between the customer and the merchant and the items. Think about what happens when you go to your local store and order a list of items. Also think about when the items actually physically change hands as part of a transaction and whether or not those items can be changed or not....

A couple of hints:

  • Once I've bought the icecream and started eating it, the merchant can't change that.
  • The 5 tennis balls that I bought (a transaction) cannot be used in another transaction with me, I've already bought them.

Hope that gets you on your way!

share|improve this answer
    
Your ice-cream example is actually very close to my current situation. To be precise, I am implementing a system for restaurants, which allows customers to make reservation and buy meals. In this system, each item sold doesn't have any kind of bar code that I can save to mark it as something like "5 tennis balls sold". I have just added some more fields to CustomerTransactions to record price, discount at the selling point of time. Besides, when a restaurant deletes an item, I will have a field "boolean deleted" to record that event without deleting the item. Is this a good idea? –  JamesBoyZ Sep 23 '11 at 4:32
    
=) I did try to think about how things work in real life. However, since I haven't implemented any systems that involve commercial transactions before, I really want to know how experts like you are doing it. Thanks for your hints ^^ –  JamesBoyZ Sep 23 '11 at 4:36

One solution would be to add another level of item classification here. Say, GenericItem.
e.g. GenericItem: Shoe
Related items: Running shoe, Walking Shoe, Discounted running shoe, etc.

You can have the merchant select the generic cateogory when adding new items.

You can add more levels depending on the granularity desired for the relation between the customer and the items purchased. You can have a SubItem.
e.g. GenericItem: Shoe
SubItems: Running shoe, Walking Shoe, etc.
Items (for running shoe): discounted, black, etc.

  • You can factor in the following while making the architectural decisions:
    • Complexity: how much complexity in terms of item classification do you want and that you want to expose to the users (merchants)
    • Performance: more complexity is going to add to performance
    • Domain: do you anticipate the number and type of items to go very high? in that case, you want a good item classification scheme.
    • Normalization: apply it to your databse design to see how best you can represent and what you want.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.