Need help with proposition argument

[1+1=2or11]

Hi guys,

Can someone help me with the correct way to respond to the proposition below?
Database checks and constraints have become good to have, and should not be enforced at the database level. Rather the checks should be implemented at the application level so any violations are detected as early as possible.

It sounds correct?

Thanks for any help.

 

[davidktw]

Hi guys,

Can someone help me with the correct way to respond to the proposition below?
Database checks and constraints have become good to have, and should not be enforced at the database level. Rather the checks should be implemented at the application level so any violations are detected as early as possible.

It sounds correct?

Thanks for any help.

Not correct from my perspective. Databases are common facilities across multiple applications. This point alone would introduce inconsistencies when it comes to checking and constraint because not all applications are created the same and when you have multiple applications with different constraints and checking upon the same tables and databases, it would corrupt the database.

Besides database require those constraints and checks to ensure ACID. ACID is the assurances that RDMBS offers, and that is normally the largest benefits from a RDBMS, so without the single management which is offered by the RDBMS, it cannot offer ACID.

Your case where applications provides these constraints and checking is for narrow use case where normally one application is the sole user to the database, but in general use cases, database is like a mainframe used across multiple parties, and centralise checking and constraint is the way to go.

Under the design of software architecture, each component should take care of its own well being in the manner of responsibilities segregation. It is not in the interest nor the responsibilities of an application to ensure consistency and reliability to the database. The same applies to embedded database, just a different level.

 

[1+1=2or11]

Thanks! It's not my case, I was given this proposition to respond to. So the key points will be ACID RDBMS, single application and centralized management? Do I have your permission to quote your comments for my report? I will rephrase.

 

[davidktw]

Thanks! It's not my case, I was given this proposition to respond to. So the key points will be ACID RDBMS, single application and centralized management? Do I have your permission to quote your comments for my report? I will rephrase.

You can quote and rephrase, it's alright.

TBH, I don't know who came out with that preposition, but it doesn't sound like one whom design software and does operations. (jokingly)

PRIMARY KEYS, UNIQUENESS, FOREIGN KEYS or REFERENCES are basic constraints. It doesn't need to resort to complex constraints that some applications are performing. So I don't see how DATABASE CONSTRAINTS are GOOD TO HAVE. Some of them are in fact essential to ensure the proper database inner workings on tuples, which conform to DATABASE CALCULUS.

Imagine if TABLE A references TABLE B ON A.REF = B.KEY, and such constraints are performed by the applications. If application ONE enforce them, but application TWO doesn't. When application ONE tries to read something that application TWO wrote, application ONE will choke. One can argue both applications should be designed to ensure consistency, but then in real world cases, the 2 applications are most likely not done by the same vendor, nor do they have same set of requirements, and even if it is same vendor, it is not the same developers. There is too much chances of corruption at too many levels, which makes the proposition a weak one.

Databases generically are not limited to RDBMS, which could be just a series of files or NoSQL. The same issues applies across all, except RDBMS have the necessary features to keep things in order, which the rest are weak at. That is the main reason why RDBMS are still very alive today.

 

[1+1=2or11]

This is one of those open ended discussion questions that have no right or wrong answers. Someone else is tasked with another question:
Is data redundancy bad?

 

[davidktw]

This is one of those open ended discussion questions that have no right or wrong answers. Someone else is tasked with another question:
Is data redundancy bad?

Well one can say anything has open ended discussions. But coming from an operational perspective, there is too much disadvantages that supersede any so called advantages one could foresee. I am quite confident most database administrators will give it a miss. But there again, if it is just an argumentative discussion, one can assume just about anything.

Well it's your assignment, you can cook up your own side of the story. Those comments I have provided above are in my opinion real industrial issues that need to be addressed. So unless one could tackle them reliably and robustly in an alternative industrial manner, they are better left the way they are designed today.

 

[1+1=2or11]

I think real life implementations and operations are important as opposed to theory only. I appreciate your comments and they have been valuable to my learning.

Many times it is due to legacy thinking and systems that inefficient designs are used. There was another discussion:
Given a constant list of unique names (eg countries), codes are often created and used to in place of the full name (eg SGP, SG for Singapore). Is this still a good practice given that storage and processing speed have been improving by leaps and bounds?

 

[benedium]

Hi guys,

Can someone help me with the correct way to respond to the proposition below?
Database checks and constraints have become good to have, and should not be enforced at the database level. Rather the checks should be implemented at the application level so any violations are detected as early as possible.

It sounds correct?

Thanks for any help.

I not an IT guy but let me try rewrite a bit k? See if I understood what you said correctly.

Database level checks and constraints are now only a good-to-have but no longer an absolute necessity. Rather, checks should be implemented at application level so that violations can be detected as early as possible.

 

[1+1=2or11]

I not an IT guy but let me try rewrite a bit k? See if I understood what you said correctly.

Database level checks and constraints are now only a good-to-have but no longer an absolute necessity. Rather, checks should be implemented at application level so that violations can be detected as early as possible.

It's an open-ended question posed for discussion purposes, so you can rewrite based on your experience and understanding

 

[davidktw]

I think real life implementations and operations are important as opposed to theory only. I appreciate your comments and they have been valuable to my learning.

Many times it is due to legacy thinking and systems that inefficient designs are used. There was another discussion:
Given a constant list of unique names (eg countries), codes are often created and used to in place of the full name (eg SGP, SG for Singapore). Is this still a good practice given that storage and processing speed have been improving by leaps and bounds?

I agree there are legacy and inefficient design set in the past due to constraints that were but are no longer valid now. So for the database constraints, you find that there is room for discussions, which is totally fine. Hence I have laid out the concerns for you to tackle.

1) How to achieve consistency across multiple applications sharing the same schema.
2) How to ensure proper database schema consistency, when it is not the responsibility of the database performing the checks, and is the application.
3) Tacking the lost in performance due to network roundtrips between the application and database to enforce constraints and checking. Eg: App wanted to insert a row with a unique ID constraint. App query from db to find there is no such ID, hence decided it is alright to insert unique ID. App insert new row with the ID. Conflict exists because another app, or in fact another thread in the same app did the same process and also attempt to insert the same ID with another new row. This is called a race condition. The race condition can be resolved, but serialisation across the network is slower compared to constraint checking performed in-process within the database. It's only 2 threads/app. Consider 10,000 threads across 10 servers, the problem is no longer trivial. You need a cluster at the app, just to do this, even if the insertion is just a daily process instead of occurring 1000 times per second, for correctness.

As for the another question you have posted in your post above. Well it depends how you use the codes. Do the codes get shared across system ? Are the codes used for references ? Do the codes get used in IoT devices which are memory and network bandwidth constrained ? These are some areas to tackle.

Debatable perhaps...

 

[cwchong]

I not an IT guy but let me try rewrite a bit k? See if I understood what you said correctly.

Database level checks and constraints are now only a good-to-have but no longer an absolute necessity. Rather, checks should be implemented at application level so that violations can be detected as early as possible.

They are not mutually exclusive;

If your system is going to be open/integrated with other systems, you cannot guarantee that input from those sources are going to be valid.

Both have their uses, and appropriate use of them will keep your application/database robust.

Eg. if your database field is going to only support a certain enum value, then an application-level check will easily save a roundtrip query to the database.

On the other hand, if insert keys such as NRIC is only enforced at application level, you will have to first query the database, before using another query to insert.
Whereas if the database has the unique constraint written, you can just optimistically insert and monitor the return code from the database.

Given a constant list of unique names (eg countries), codes are often created and used to in place of the full name (eg SGP, SG for Singapore). Is this still a good practice given that storage and processing speed have been improving by leaps and bounds?

I'm guessing this is the argument for nosql. But even if processing speed/storage is improving, there is no good reason not to improve it via such lookup keys. Moreover, such codes (usually ISO) have been widely adopted as standards and will vastly make your codes compatible with other systems.
For eg., if your system uses 3rd party logins (maybe google/facebook?) which shares your country, it is likely stored as ISO codes; which you can then directly integrate with your existing system, rather than depend on a middleware to convert those codes to your system's version.

 

[Azzizz81]

This is one of those open ended discussion questions that have no right or wrong answers. Someone else is tasked with another question:
Is data redundancy bad?

Normally (pun intended) you want your tables to be normalized to some degree but there are some circumstances where you would intentionally denormalize it say for performance, typically for reporting.

Another example is where the links between commonly queried tables are tenuous, it may be simpler cognitively to introduce redundancy.

Let's say u need to join 10 tables to get the result of a customer's order from the last table, it may be good idea to introduce the customer's ID into the last table and query on that instead, if that is a common query. Of course alternatively, you can just write a view to hide this complexity~

 

[Azzizz81]

I think real life implementations and operations are important as opposed to theory only. I appreciate your comments and they have been valuable to my learning.

Many times it is due to legacy thinking and systems that inefficient designs are used. There was another discussion:
Given a constant list of unique names (eg countries), codes are often created and used to in place of the full name (eg SGP, SG for Singapore). Is this still a good practice given that storage and processing speed have been improving by leaps and bounds?

The extra level of indirection does give a little more flexibility for some kinds of changes ~

 

[1+1=2or11]

Normally (pun intended) you want your tables to be normalized to some degree but there are some circumstances where you would intentionally denormalize it say for performance, typically for reporting.

Another example is where the links between commonly queried tables are tenuous, it may be simpler cognitively to introduce redundancy.

Let's say u need to join 10 tables to get the result of a customer's order from the last table, it may be good idea to introduce the customer's ID into the last table and query on that instead, if that is a common query. Of course alternatively, you can just write a view to hide this complexity~

Seems like this is the "model answer" for having data redundancy, have not heard any other interesting arguments.

 

[chenaz]

Another benefits of havin constraints keys in database is indexing of the data. So that query run and data will be fetch to query faster if you are dealing with huge data.

 

[davidktw]

Another benefits of havin constraints keys in database is indexing of the data. So that query run and data will be fetch to query faster if you are dealing with huge data.

I think you need to separate between indexing and constraints. You can have indexing without constraints. So it would be not an advantage of having keys or constraints to get indexing, because the latter does not require the former.