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 ...
- 221k
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 ...
- 13.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 ...
- 84.6k
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 ...
- 59.5k
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,957
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 ...
- 120k
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, ...
- 84.6k
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.5k
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,347
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.
...
- 46.6k
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
10
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 ...
- 7,997
8
votes
Database vs Flat files for rarely accessed data
Database engines are in principle designed to cope with huge amounts of data much faster than with raw data files, when you have to access data in a non-sequential manner.
You say that you have all ...
- 82.3k
8
votes
Is indexing foreign keys a good practice?
First, there is no automatic indexing of FOREIGN KEY in RDBMS except MySQL, which is a stupid behavior.
Second, in some cases indexing a FK create an included redundant index, especially when :
the ...
- 189
8
votes
Accepted
Database agnostic DAO (NoSQL + SQL)
The best way to guarantee that you stay reasonably decoupled from the database, but at the same time remain free to use any feature of it, is to not create an abstraction layer for the database. (Well,...
- 12.4k
8
votes
Is deterministic SELECT possible without specifying an ORDER BY?
No
You cannot and should not rely on an SQL database returning rows in any deterministic fashion without proper ORDER BY clauses. In practice, rows may come back in a particular order each time you ...
- 39.4k
7
votes
Accepted
Designing database: split into different tables or group them together?
A good rule of thumb is to design the database schema without too much regard to optimizing performance (especially if you're new) and then change it, if necessary, for performance reasons. As ...
- 414
7
votes
Is it reliable to compress database backups with git?
In short
No, this is not a real backup, and it might bring lots of trouble. There are better alternatives to protect you against accidental errors that worry you most.
More details
A real backup must ...
- 82.3k
6
votes
Is it reliable to compress database backups with git?
Git is not a backup solution.
There are countless programs available which are specialized for creating regular incremental backups of important files, both paid and free software. There are even some ...
- 23.5k
5
votes
Best way to query immutable MySQL Data
Yes, there is a cleaner way, it is called temporal databases, in particular "transaction-time".
The idea is to use an interval value [trans_start, trans_end[to denote, when a row was stored or ...
- 244
5
votes
Accepted
Application/Database design where two tables have both 1-to-many and 1-to-1 relationships
My problem is that one of the manufacturers is to be treated as a car
model. There will be only one car model under this manufacturer.
Ok.
When you click on the MegaCar
manufacturer, it should ...
- 15k
5
votes
Accepted
Should transaction id's skip?
Database keys should not be conflated with business identifiers like invoice numbers or transaction numbers, unless their semantics are identical.
The reason you have an ID field in a database is to ...
- 201k
5
votes
Accepted
Using the primary key as a foreign key
Foreign key mapping is a proven technique for implementing a one-to-many relationship (not mandatory, i.e. 1 - 0..*). So having a foreign key in shipment_data that refers to shipment's primary key ...
- 82.3k
5
votes
Variable WHERE clause while avoiding sql injection
And then the SQL Engine was updated to include Select ... XYZ
In short don't allow the user to provide any SQL.
In long. The capability of database engines and sql has steadily expanded over the years....
- 16.6k
5
votes
Accepted
Is it reliable to process millions of records with INSERT INTO SELECT directly in the database?
Should this necessarily be done at application level, or can the database (MySQL InnoDB) also do this reliably with INSERT INTO SELECT?
It is reliable, until it is not.
Honestly, this all depends on ...
- 221k
5
votes
Should I break a large user table into smaller tables for specific roles and information?
You should break the table up based one what is 1-to-many relationships. For example a user might have more than one degree, so education should be a separate table from user, with a foreign key to ...
- 62.4k
5
votes
Best Practices for Implementing a Heartbeat Feature in a Laravel App to Track Offline Status
You should start by assessing the risks. What exactly you need to check? What “offline” exactly means? Why do you need a heartbeat? What would happen if the heartbeat don't do its job? Depending on ...
- 139k
4
votes
How to store large amount of session data
Redis is known as a cache server; and while it is great for that, Redis' real strength is as a session store. Redis is the clear choice for large and intensive session stores.
However, it is not ...
- 5,311
4
votes
Accepted
How to Define Service Area – with Zip Codes?
You need to define what your service providers mean by service area. There are several ways to do so, depending on how specific you want to get.
Here are some options ranging from simple to more ...
- 46.5k
Only top scored, non community-wiki answers of a minimum length are eligible