I want to ask for any suggestions for an architecture I can implement for a Java app I need to create (initial thoughts below).
It is supposed to be a local Swing application for tracking financial aspects of multiple construction sites. Excel-like but with a few additional features like mailing data etc.
After some thinking, I came up with an idea of using a Derby database with a separate table for clients, another one for categories of products used and multiple "ConstructionX" tables, each containing records for a different construction site, created dynamically via JDBC.
That last part is what I'm most concerned about as it doesn't really feel like a good solution programatically but it does so pragmatically.
I don't really have much experience building applications like this so here comes my question: Is there any better way I could design this database? I'm open for suggestions (please don't post it on TheDailyWTF).
(I originally asked this question on StackOverflow and was redirected here)
As a side note (as someone on SO suggested something like this too): I originally planned to create just one "Construction" table which would hold records for all construction sites where each record would be tagged with a construction site number it refers to (possibly forming a relation to a table holding all sites). However, I was worried about times of running "SELECT" statements (when displaying all records just for one specific site). Can anyone confirm it'd be a better solution here?