211
votes
Benefits of Structured Logging vs basic logging
There are two fundamental advances with the structured approach that can't be emulated using text logs without (sometimes extreme levels of) additional effort.
Event Types
When you write two events ...
- 2,286
119
votes
Should I define the relations between tables in the database or just in code?
The database doesn't have to check for data integrity every time application modify data.
This is a deeply misguided point. Databases were created for precisely this purpose. If you need data ...
- 108k
94
votes
Accepted
Why is using MySQL for a dictionary website a bad idea?
I can't tell you why it's a bad idea. I can tell you a bunch of reasons why a relational database is a good idea though.
Remember that not everyone consults a dictionary for a definition. More times ...
- 34.8k
71
votes
Accepted
Should I define the relations between tables in the database or just in code?
TL;DR: Relationship constraints should go in the database.
Your application ain't big enough.
You are correct, indeed, that enforcing relationships across databases may require enforcing them in the ...
- 14.7k
68
votes
Accepted
Can it be acceptable to construct SQL queries dynamically?
On one hand, I don't like such braindead rules, since they are clearly an oversimplification. A better wording would be IMHO
You shouldn't construct SQL queries dynamically using unsanitized input ...
- 203k
62
votes
Should I use DATE or VARCHAR in storing dates in MySQL?
I don't understand as to why the DATE data type exists
Always store Date values in Date Fields.
What PHP gives you back when you retrieve those values is a Character Representation of the Date value ...
- 12.1k
60
votes
Accepted
How to store a fixed length array in a database
"adding 24 columns to the draft table seems more efficient to me"
show me all the drafts which include player 2.
select * from draft where p1=2 or p2=2 or p3=2....
vs
select * from draft ...
- 72.4k
52
votes
Should I define the relations between tables in the database or just in code?
The constraints should lie within your database, as (with the best will in the world), your application will not be the only thing to ever access this database.
At some point, there may need to be a ...
- 2,623
46
votes
Accepted
Is it possible/advisable to combine unit testing and integration testing?
Is it possible/advisable to combine unit testing and integration testing?
Doing both unit testing and integration testing? Overwhelmingly yes.
Mashing them together in a single test suite? Not ...
- 47.7k
27
votes
Why is using MySQL for a dictionary website a bad idea?
If you go with the key-value store (which offers you a more impoverished programming model) and it turns out you need more structure (in your case, say, adding a third language), or you need to do ...
- 33.6k
24
votes
Should I use DATE or VARCHAR in storing dates in MySQL?
In programming, we are always working with abstractions and representations:
The text string 2021-02-09 12:47:14 UTC is a representation of a particular point in time.
The integer 1612874834 is a ...
- 5,752
23
votes
How to store a fixed length array in a database
Unmeasured efficiency concerns are poor arguments for design decisions. If you really believe there is an efficiency issue develop a performance requirement based on your actual needs. Create a test ...
- 105k
20
votes
Can it be acceptable to construct SQL queries dynamically?
There's a whole tonne load of things that can go wrong with dynamically constructed queries. Hence the rule.
sql injection attacks
non-performant queries
non cached queries
server specific settings, ...
- 72.4k
17
votes
Benefits of Structured Logging vs basic logging
When you are collecting logs for processing, be it for parsing into some database and/or searching through the processed logs later, using structured logging makes some of the processing easier/more ...
- 277
17
votes
Should I define the relations between tables in the database or just in code?
You should have relations in the database.
As the other answer notes, performance of constraint checking will be far better inside that database than inside your application. Database constraint ...
- 4,199
13
votes
Should I define the relations between tables in the database or just in code?
We no longer live in one back-end <-> one front-end world.
Most solutions involve a web front-end, a mobile front-end, a batch-front-end, and iPad front-end, etc.
Database engines already have ...
- 39.4k
13
votes
Should I use DATE or VARCHAR in storing dates in MySQL?
But everytime I echo the date and time in PHP it just gives me a string, not a date object.
That's basically just the quirk of your DB access library. When it comes down to it, all data are reducible ...
- 12.3k
12
votes
Accepted
Is indexing foreign keys a good practice?
Most, if not all, RDBMS automatically create an index on a FK because a FK means you will be doing joins using those columns.
It's standard RDBMS behavior based on the fact that you will be using ...
- 39.4k
12
votes
Is it safe to put SQL statements in my C# application?
The "we don't have the resources" line is a red flag to me.
You're either going to pay to do it right, or you'll have to pay for doing it wrong. I know which I'd pick.
While your use case is ...
- 3,287
11
votes
Accepted
How to test data based on SQL queries?
Been there, done that.
First let's make something clear:
That's not unit testing. Unit testing is about code. You are not runnning the tests after a code change to test if code alterations ...
- 39.4k
11
votes
Should I use DATE or VARCHAR in storing dates in MySQL?
Size
A MySQL DATE is 3 bytes. Each char is a VARCHAR is 1 byte. Which means your VARCHAR date is at least 2.67 times the size of a DATE. 3.33 if you include separators. Or much larger if your ...
- 219
11
votes
Can it be acceptable to construct SQL queries dynamically?
It can be acceptable to concatenate SQL, but only under a very narrow and specific set of circumstances. Before reading this answer, you should read Doc Brown's and Ewan's answers for a good warning.
...
- 34.8k
10
votes
Benefits of Structured Logging vs basic logging
You won't find much benefit from structured logging when your app creates a few hundred log messages per day. You definitely will when you have a few hundred log messages per second coming from many ...
- 221
10
votes
Why is using MySQL for a dictionary website a bad idea?
For a database this small, it's probably not going to make much difference for performance. A standard RDBMS isn't a terrible idea here because presumably, there should be far more reads than writes ...
- 25.9k
10
votes
Application/Database design where two tables have both 1-to-many and 1-to-1 relationships
Think longer term - eventually... there will be a MegaCar 2.0 Or there will be another new car company with only the one model. Or one model companies all go out of business.
Keep it the way you ...
- 317
9
votes
Accepted
Is it safe to only use intval to sanitize user input for a database select?
intval is guaranteed to produce an integer (it returns 0 if it "fails"), and an integer is a safe thing to use in a MySQL query. This answer https://stackoverflow.com/a/3208089/458614 on SO agrees:
...
- 206
9
votes
Should MySQL database tables only have a single unit of information per row?
Your coworker is actually right about this. If you're taking measurements from all your sensors at the same interval, and the set of sensors you're recording from remains constant or mostly constant, ...
- 82.4k
9
votes
Transaction handling in DAO or Service layer in pure JDBC without frameworks
Sorry for the rather long answer.
I think, for a modern application approach, you should consider the connection to be injected into the dao, not taken from a factory.
Your problem is, as I ...
- 216
9
votes
Do we have 2 logical query processings, one with indexes and one without indexes?
tl;dr: Query planners are a big deal.
In general, before an RDBMS backend executes a query,
it first plans the query.
There's more than one way to access the data,
and the planner evaluates some of ...
- 4,799
Only top scored, non community-wiki answers of a minimum length are eligible