Handle Databaserelations on serverside or in the program

after a few discussions with a collegue we still not have the same meaning about this topic.

In my opinion it makes more sense to create a properly designed Database with all including relations. Im not really experienced in this area, this is why im asking you.

Advantages in my opinion

- No "wrong" inserts because of the relation conflicts in the Database

- Database and Program is strictly seperated

- Several programms for the same Datasource requires less work to c开发者_Go百科ustomize

- Making the use of LINQ much easier

- and many more.... ?

Possible disadvantages of this way?

What are the advantages of not related Tables?

Transactional systems should "always" have the referential integrity enforced as close to the database as possible. Most people would agree that this is best done right inside the database itself. You have correctly recognized many of the advantages of letting the DBMS enforce referential integrity.

I said "always" above because I believe in common sense and deliberate decisions not rules of thumb.

One reason why someone may not want to enforce referential integrity within the database is that you have a cyclical relationship where the parent and the child need to point to each other and it is not possible to insert one record because the other isn't there yet. This leaves you with a so-called catch-22. In this case, you may need to enforce the referential integrity in program logic. Still, the best place for this is in the data layer, not in the application layer.

Another reason why some people don't worry about referential integrity is when the data is read-only. This can happen in a reporting database or data warehouse. Referential integrity in the database creates indexes which are used to enforce the relationships. This can sometimes be a space issue, but more often it is just a problem with making the data warehouse load harder because of the order of operations required.

One more reason why referential integrity is sometimes not used is that archiving old transactional data can get tricky because of complex interrelationships between master tables and transaction tables. You can easily find yourself in a position where it's impossible to delete any data, no matter how old it is, because it is somehow related to something that is related to another thing that is needed by something current.

Having said all of this you should definitely start from the position of using referential integrity features of your database and only back away from this if you have a really good, well considered reason.

Of course !!! You must enforce the referenctial integrity within your database model ! Safer, more efficient, guaranteed data integrity, and you do not rely on the programmer. No discussion here.
Not related tables are ONLY usable if you are just building a "reporting db" that downloads nightly data from various systems, for example.