Database Relations OR Via Code

HI :wave:

I am working on a Accounts and Inventory Project for a Workgroup. Now, What I did is that I do not define any relation between the tables. I am managing it through code. For exmaple , In the Invoic Form, the user opens the form, it automatically to to the first record of mater, then I filter the detail table and display the reords in Grid having the same invoice number as master.

Similarly I handled all the cases of edit, delete, add, navigation, etc.

I want to know what are the disadvantages of WORKING WITH THE ABOVE APPROACH ? and Wha are the Advantages of defining tables relation in database ????

Thanks a million in Advance.
:thumb:

The overall goals is to package as much of the "business" logic as far away from the client as possible.

The fact that you manage the invoice and detail data in the client, to give a proper view of data to the user is fine - that's great.

But not having referential-integerity in the DB to force this is not good - that means you client code is "enforcing" more than it should.

If a weak user writes a query to update and doesn't know about the rules in your VB code, then a DB failure is what happens next. You want a poor QUERY to fail in the database, so the user is forced to make it proper...

Just my opinion...

Hi, thanks for the reply.


The thing is that the user can not enter QUERIES himself. He is limited to perform day to day data manipulations like ADD,DEL, Edit, Reports etc. As far as the reports are concerned, there are a lot of queries predifinied, so again the user has to SELECT which report he wants to see.

so , if this is the case , then is it necessary to implement Database Relation rulesss ?

Even though the users cannot enter queries....they still enter the data. Having the relations in the DB help to enforce data integrity will help to ensure bad data doesn't get in. It will also help the DB optimize queries for efficientcy.

TG

My opinion is this:

You design the db and you allow the users to enter data.
If you allow them access to the tables/queries or write their own queries, you should expect the db to fail.

As to referential integrity, if your code is working fine it won't matter (see above point on users n queries). So you won't have to worry about crap data getting in 'cause you've coded for it (or you'd better have ;) ).

Referential integrity is good in principle but overall to the coder its a pain in the ass :) (again my opinion). Just when you decide its acceptable to delete something, it errors and kills your process in the middle or some other such problem.


If you have the time, do both ways as it will give you experience in all options available to you.


Vince