1

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?

  • 1
    You cannot avoid select statements. Just make sure you have the tables appropriatelly indexed. – Tulains Córdova Aug 23 '16 at 13:00
  • @TulainsCórdova Oh, I wanted to say that I believe running SELECT on separate tables would be more time efficient than running SELECT WHERE on one table with lots of records. – Cube. Aug 23 '16 at 13:31
  • Shouldn't we change the tittle to "One table vs multiple tables for the same logical entity?" ? – Tulains Córdova Aug 23 '16 at 14:23
5

Creating separated tables for the same entity based on a domain value is generally a bad idea.

enter image description here

enter image description here

Talking about CONNSTRUCTION and CONSTRUCTION_SITE:

  • Make sure the tables are apropiatelly indexed
  • CONSTRUCTION has a PK
  • CONSTRUCTION_SITE has a PK
  • CONSTRUCTION has a FK pointing to CONSTRUCTION_SITE

There will be an index for every PK and every FK. The database engine will take care of, as fast as it can, filter out the rows you want. That's what database engines are for.

As they say: "Premature optimization, etc."

enter image description here

  • So turns out the original approach was much better. Thank you so much for the detailed answer and for simplifying my work :) – Cube. Aug 23 '16 at 13:33
0

The basis that I was taught was that if there is any enumeration that you will need then it should probably be defined as the primary key in a table and then used as a foreign key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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