Timeline for Should I define the relations between tables in the database or just in code?
Current License: CC BY-SA 3.0
40 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:45 | history | edited | CommunityBot |
replaced http://softwareengineering.stackexchange.com/ with https://softwareengineering.stackexchange.com/
|
|
| Oct 31, 2016 at 8:36 | history | tweeted | twitter.com/StackSoftEng/status/793008770339246084 | ||
| Oct 29, 2016 at 22:12 | comment | added | peterG | "many of the projects I have read in the past didn't have relationship definitions in the database," - How far back are you going? Years ago RDBMS didn't have DRI - maintaining RI in the application was the only option. | |
| Oct 29, 2016 at 17:28 | answer | added | Tony Ennis | timeline score: 0 | |
| Oct 29, 2016 at 9:08 | comment | added | user2338816 | Analyzed a bad app years ago that enforced constraints fairly well. Forced the user to make record corrections until all errors were gone, then only after displaying the finally correct data would the app allow the 'Enter' key to signal writing the record to the DB. The astonishing bug? That "finally correct" record image wasn't rechecked after the final 'Enter' key was pressed. I.e., once the no_Errors condition was set, the input could be changed in any way and would be written to the DB. Massive integrity checks; then 'anything goes'. | |
| Oct 29, 2016 at 8:53 | comment | added | user2338816 | Verbosity of error messages is irrelevant. Degree of verbosity is always under control of the app (which may choose to let it pass), not the DB... except when the DB is accessed outside of the app, at which points all constraints must be in the DB. | |
| Oct 28, 2016 at 21:58 | answer | added | jmoreno | timeline score: 0 | |
| Oct 28, 2016 at 21:53 | comment | added | Wayne Conrad | I'm writing an ETL for a large database where the creators did not ask this question. They created no FK constraints. The data quality is just awful--there are gobs of FK's that point to nowhere. If I had a time machine, I'd use it to go back in time and offer them money to add FK constraints. | |
| S Oct 28, 2016 at 21:16 | history | suggested | psmears | CC BY-SA 3.0 |
Improve grammar and wording
|
| Oct 28, 2016 at 15:47 | review | Suggested edits | |||
| S Oct 28, 2016 at 21:16 | |||||
| Oct 28, 2016 at 14:23 | answer | added | paparazzo | timeline score: 0 | |
| Oct 28, 2016 at 10:57 | comment | added | sampathsris | The database doesn't have to check every time application modify data. No, but any respectable database implementation for integrity handling is probably decades ahead of what you would write yourself. | |
| Oct 28, 2016 at 10:16 | comment | added | James_pic | In some cases, adding integrity checks to the database can increase performance. Some modern RDBMSs are able to use constraint information to optimise queries, by choosing plans that are faster, but would be incorrect if the integrity constraints did not hold. | |
| Oct 28, 2016 at 10:06 | comment | added | Christian Strempfer | Note: You tagged this with mysql and relational-database, although you don't mention it in the question itself. The answers would be completely different, when we're talking about other database types, e.g. document database MongoDB. | |
| Oct 27, 2016 at 23:06 | comment | added | Kyslik | I am just curious if you are self thought programmer, or you have some academia background. I am (currently at uni) and I would never think of making my own RDBMS - so many things can go wrong, + reinventing the wheel. | |
| Oct 27, 2016 at 22:01 | comment | added | jpmc26 | Read this. Then go put basic constraints to your DB. (Some of the more fine grained ones may be left out, if the database is protected by an application of some kind.) | |
| Oct 27, 2016 at 17:31 | vote | accept | Yoshi | ||
| Oct 27, 2016 at 13:21 | comment | added | dcorking | Confusingly, in database terminology, relation is a synonym for table. In your question title, I think you mean association, (aka foreign key constraint aka _foreign key relationship). It may help to rename your question. | |
| Oct 27, 2016 at 7:56 | history | protected | gnat | ||
| Oct 27, 2016 at 7:20 | comment | added | Radu Murzea | Related question: Business logic: Database vs code | |
| Oct 27, 2016 at 6:13 | answer | added | parker.sikand | timeline score: 1 | |
| Oct 26, 2016 at 20:55 | comment | added | mercurial | I once asked a very talented programmer a similar question he told me "Its like brakes on a car. The point isn't to make the car go slower, but to allow it to go faster safer." Sure its possible to run without constraints but if bad data somehow gets in, it can cause a serious crash | |
| Oct 26, 2016 at 18:22 | history | edited | Yoshi | CC BY-SA 3.0 |
added 376 characters in body
|
| Oct 26, 2016 at 15:32 | comment | added | user20416 | You are quite correct -- the user shouldn't be seeing SQLException error messages; data changes that cause such errors should be checked in the application code before trying to update the database, or at least these errors should be handled and presented with a better UI. But that doesn't mean you don't need to define relations in your database. | |
| Oct 26, 2016 at 14:45 | comment | added | Blrfl | In source code: You eventually end up writing most of a database. | |
| Oct 26, 2016 at 14:40 | answer | added | Bradley Thomas | timeline score: 2 | |
| Oct 26, 2016 at 13:30 | comment | added | user190064 | Something worth mentioning... once you introduce integrity problems to your database it is a kin to opening Pandora's box. It is a nightmare to reintroduce integrity to a anomaly-ridden database. Keeping tight controls on your database may be a hassle but it will save you a lot of pain in the long run. | |
| Oct 26, 2016 at 13:27 | answer | added | Tulains Córdova | timeline score: 13 | |
| Oct 26, 2016 at 11:44 | answer | added | Matthieu M. | timeline score: 71 | |
| Oct 26, 2016 at 10:46 | answer | added | Paddy | timeline score: 52 | |
| Oct 26, 2016 at 9:30 | answer | added | DHN | timeline score: 0 | |
| Oct 26, 2016 at 9:09 | review | Close votes | |||
| Oct 31, 2016 at 3:01 | |||||
| Oct 26, 2016 at 8:43 | comment | added | Dabu | There's one thing you never should forget: Unless everybody involved writes perfect software, if the checks are in the software, one of these checks will fail and lead to constraints not being enforced. It's not a question of if, but of when. This leads to hard to reproduce errors and long hours of massaging the data to fit the software enforced constraints again. | |
| Oct 26, 2016 at 7:24 | comment | added | Yoshi | Sometimes I feel it is need for application to handle data integrity for more verbose error message. Please check the updated question for details. | |
| Oct 26, 2016 at 7:23 | history | edited | Yoshi | CC BY-SA 3.0 |
added 909 characters in body
|
| Oct 26, 2016 at 7:02 | answer | added | Kirk Broadhurst | timeline score: 17 | |
| Oct 26, 2016 at 6:53 | answer | added | Kilian Foth | timeline score: 119 | |
| Oct 26, 2016 at 6:53 | comment | added | user82096 | "as application has to make more query(s) to check data integrity." Not necessarily. If the database is fully under the control of your application, extra checks of the data integrity may be overly defensive programming. You don't necessarily need them; just test your application appropriately to ensure it makes only valid changes to the database. | |
| Oct 26, 2016 at 6:51 | review | First posts | |||
| Nov 12, 2016 at 18:43 | |||||
| Oct 26, 2016 at 6:47 | history | asked | Yoshi | CC BY-SA 3.0 |