Hi varun077,

Most of your questions including your several performance and maintenance issues will simply get solved if you (re-)design a true relational datamodel based on correct relationships (cardinalities) and effective normalization, as for example:

1. Foreign keys: will appear automatically if relationships are correctly taken into account and implemented in database. They are a must for ensuring referential integrity. Why shall one painfully reinvent that at Java programming level or even simulate that by triggers if database has already the most effective referential-integrity feature? It is true myth that foreign keys would provoke serious performance issues. As for triggers: Simulating foreign keys by means of triggers is like breaking flies on wheels. In both cases you probably need indexed columns to gain reasonable performance depending on the size of tables. Additionally for triggers, interpreted program code must be executed on server, how to coordinate triggers? Did you ever worked with databases where some hundreds triggers were firing every which way?

2a. Never put different data/information described by similar attributes into one table what would lead to poor normalization what also would entail typical anomalies. Is there really a common primary key? And how to write effective-SQL select statements within such context?

2b. Never drop foreign keys, which implements one-to-many relationships, from a table and replace them by additional two-column tables containing two foreign keys from both associate tables to joining them together: You would then have many-to-many relationships only. For example, such constellation would allow to assign items of an invoice of a particular invoice, between them exists a real one-to-many relationship and nothing else, to every other existing invoice. To monitor and avoid this, you must invest additional plain programming efforts.

3. Surrogate keys: If a column or set of columns is able to identify each row uniquely and permanently over the course of time you should use this naturally given key. OK, if such natural degenerates into many columns sometimes it's cleverer to introduce a surrogate key what is then usual a numeric and auto-increment key. But attention: Surrogate keys never avoid duplicate data. Therefore, this must be done in Java program itself. (Don't forget to put indexes on all relevant columns you want to check for duplicates in consequence of beloved surrogate keys. You got it? Yes, you wouldn't have run into such mess, if you hadn't that surrogate thing.)

4. Splitting big table: There is no reason to split tables what contains tree-like data into various tables because trees can be effectively handled by recursive tables. You may google for Joe Celko trees.

Quintessence: All above enumerate problems would never arise, if database designers follow Edgar Frank Codd's idea of relational database design strictly.

Don't forget: In a well designed datamodel all selects should be implemented on SQL level rather then by plain programming code no matter how complex the query is. (It's really poor if one finds in a Java program two nested loops what implements fetching the rows from a one-to-many relationship (aka master-detail tables).

Best regards,
tesu