DB application design - which one, how to.
Hi,
I have a problem with DB application design. I have in my mind few ideas but I am not sure which could be better for further develop & maintain.
The first idea is to create connection and statements on every dialog, hard code sql commands into the code. It would looks like the easiest and the fastest way to get some working results, but I know from my experience it will become a huge "pain in the a.." later on, when code grows, and application becomes more and more complex, especially when DB structure changes.
The second idea is to have a one class representing one row in one table (or set of rows, maybe using rowset). Each class should have create, get, save, and remove record methods, and each class has a static connection object which it uses to work on a db. The problem is when a lot of tables exist, because I have to create a lot of those 'utility' classes. It looks like I should also create a class for each view or join on tables. It could be also a big "pain" if you work on 1000 tables. On the other hand who is working alone with such big DB's? :).
Having such solution I can work on DB just manipulating prepared objects, which looks quite easy.
On the other hand I wonder if I should keep in the DB only structure and data, or should I put there also business logic. If I put there some logic I will have more problems when migrating to newer version or different platform. If I will leave logic within my application it will take more time to accomplish some common simple tasks, which RDBMS provides.
Summarizing:
1.What design should be used to work with DB抯? I know it always depends on the problem, but maybe you could provide some figures or hints like for 50 tables consider using this and that, for more don抰 even try and use the other one.
2.Where the business logic should be implemented? Let the RDBMS to keep eye only on data integrity, execute some logic or only store the data and everything is in the developer hands, even the data integrity.
Kind regards
Piotr Kobik
