Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA - entity mapped to different tables by parameter

 
daniele licitra
Ranch Hand
Posts: 81
Java Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi to all.

I'm developing a "multi-enterprise" / "multi-year" application and i want to use JPA api and JPQL when it's possible.

The users must manage invoices from different firm and for different year.

In case of invoices, i want to have a table per year and firm: firm1_invoice_2013, firm1_invoice_2014, firm2_invoice_2013....

There is a way to map the entity "Invoice" to the correct table, supposing i have variables "selected_year" and "selected_firm" at runtime?
An employee, for example, must select an order from 2013 and create an invoice for it in 2014
A lot of software use databases with prefix and suffix for differenciating tables for different environment (it's time-consuming filtering each time record for a firm).
 
Dieter Quickfend
Bartender
Posts: 543
4
Java Netbeans IDE Redhat
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are two things you have to do:

1) Normalize your database. If you see a lot of duplication, it's because your database is not normalized. If you split your tables logically, you can make entities for your logical domain. Talk to your DBA if you have one. I guess this doesn't directly answer your question and it may be that your table is already normalized, but it doesn't hurt to check. This should be check #1 on your list.

2) Partition your tables, don't split them into rowsets. I'm assuming your database contains this feature. Talk to your DBA for this.

Overall, it's a good idea to speak to system administrators when you have an important performance issue on their side. They can always help. Perhaps a few commands executed on the DB server can save you a lot of trouble with your application.
 
daniele licitra
Ranch Hand
Posts: 81
Java Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dieter Quickfend wrote:2) Partition your tables, don't split them into rowsets. I'm assuming your database contains this feature. Talk to your DBA for this.


This is what i'll do.
Postgresql support partitioning, so the entity must be mapped to the master-table and then the RDBMS split the query along the partition, is that right?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic