On Fri, 2011-09-16 at 01:10 +0800, Emmanuel Noobadmin wrote: > On 9/15/11, Always Learning <centos at u61.u22.net> wrote: > > >> Next you'll be saying you don't use triggers and constraints either. > > > > Not consciously. Never heard of them. > You should take a look at constraints, they are good for ensuring > certain types of data integrity. For example, it would make the > database to stop situations like somebody trying to insert a record > referring customer #9865 but in fact #9865 doesn't exist, whether it > was an unintentional user error or a bug in the application. Before anyone can add data for customer 9865, the existing customer record is displayed on the screen. This helps the user to be sure he/she has got the correct customer. A customer not found message means the record does not exist. Consequently it is impossible to add data to a non-existent customer record. In most circumstances, instead of entering anonymous un-meaningful digits to identify customers, look-ups are done with postal code or partial address match or partial organisation name match or partial telephone number match etc. I love easy-to-use user-friendly systems. > No, in many situation, it's a more secure method. Databases can have > privileges set. I use the SQL privileges for tables and enable only the SQL verbs required by a user. I certainly do not want a user being able to 'drop' a table. Only I can do that. > You could have triggers and stored procedures that > update certain records that cannot otherwise be altered by the > application which can be written by a third party. I do have some fields as zero-filled, auto increment. > For example, a stored procedure would require both a debit and credit > account for transferring funds and/or checks that the actual amount is > present before doing it. Without this, a bugged application or rogue > user/dev who run the app with privleged access would be able to > transfer funds that don't exist. In my systems such actions could not happen. No user gets permissions they do not genuinely require. If the programme specification says no 'overdraft' then funds can not be transferred out of an account if that account balance would go negative. > > Simplicity and good design makes applications fast. > > For some apps, fast is king. For some, data security and integrity is > ultimate. Would you want your banking transactions to run faster by > stripping out security and validation checks, at the risk that some > dude can transfer all your money into somebody else account? If so, > please let me know your bank account details and access credentials, I > have a program to speed up your banking transactions... ;) Let us be serious. Fast, efficient applications are no good if they malfunction. Proper functioning is the first requirement and if the systems, the database and the programmes, are designed and coded efficiently, the applications will run fast and be secured. The banks in Holland used to meet monthly to divide-up all the money that got lost in the system. Obviously they were using 'experts' to design and code their systems :-) > > The integrity of the data can be divided into two aspects: ensuring the > > data remains constant (unaltered) while stored, which is the > > responsibility of the operation system and the database software, and > > the data's integrity from an application perspective. Junk-in always > > causes Junk-out even when using 'non-dumb' databases :-) > > And if the database can further ensure that the application cannot put > junk in, whether due to a bug, user error or deliberate fraud, why > not? Especially when it's likely to be faster because it's native > code. Database intervention to validate data is too late, in my opinion. You do not want junk getting pass the application's data input stage. If you want an amount of money and someone specifies the currency as GBQ instead of GBP, then that input error should be identified and rejected at the data input stage not actually sent to the database to be stored. That is why I always try to wreck my programmes by entering invalid data. If I fail to wreck my programmes there is a reasonable certainty others will fail too. Paul.