396
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
This way, the dates can easily be sorted as strings using the default sorting rules (i.e. lexicographical sorting).
This is also why both month and day are specified using two digits (adding a ...
251
votes
Accepted
Is it ever okay to use lists in a relational database?
The key word and key concept you need to investigate is database normalization.
What you would do, is rather than adding info about the assignments to the person or tasks tables, is you add a new ...
170
votes
Accepted
Why not return dates as a string from the database?
Dates, DateTimes and really any other typed object, should generally be left in their properly typed format until the moment you need them to be made into some other type - especially when that type ...
151
votes
Accepted
Is it good practice to always have an autoincrement integer primary key?
It's never a bad idea to have a guaranteed unique row identifier. I guess I shouldn't say never – but let's go with the overwhelming majority of the time it's a good idea.
Theoretical ...
147
votes
Accepted
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
The problem is that #1 requires you effectively parse and interpret the entirety of the SQL variant you're working against so you know if it is doing something it shouldn't. And keep that code up to ...
136
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
Not mentioned yet, but you quickly gloss over the order inside YYYY. That's already millennia, centuries, decades, years. That is to say, YYYY is already ordered from longest period to shortest period....
133
votes
Accepted
Are database unique indexes a mask on bad scripting?
My suggestion was to make a UNIQUE index on whatever it is that defines the uniqueness of the table
You really ought to have one already.
Uniqueness of rows is a pretty fundamental property of a ...
116
votes
Is it considered an anti pattern to write SQL in the source code?
You excluded the crucial part for simplicity. The repository is the abstraction layer for persistence. We separate out persistence into its own layer so that we can change the persistence technology ...
109
votes
Is it good practice to always have an autoincrement integer primary key?
TL;DR: Use UUID's instead of auto-increment, if you don't already have a unique way of identifying each row.
I disagree with all the answers before. There are many reasons why it is a bad idea to add ...
80
votes
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
Because option 1 is not a solution. Screening and filtering means rejecting or removing invalid input. But any input might be valid. For example apostrophe is a valid character in the name "O'Malley". ...
74
votes
NoSQL within SQL Server
When he reviewed the database schema he stated that all foreign keys and other such constraints should be removed as this is business logic and should be applied within the business layer.
Then he's ...
69
votes
Are database unique indexes a mask on bad scripting?
UNIQUE constraints are supported by all relational databases for good reasons, one of them being able to prevent data inconsistencies.
Your colleague has a point in that the code should be written to ...
67
votes
Accepted
Why is "Select * from table" considered bad practice
Think about what you're getting back, and how you bind those to variables in your code.
Now think what happens when someone updates the table schema to add (or remove) a column, even one you're not ...
64
votes
Is it good practice to always have an autoincrement integer primary key?
Autoincemental keys have mostly advantages.
But some possible drawbacks could be:
If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules.
...
60
votes
Why did SQL injection prevention mechanism evolve into the direction of using parameterized queries?
If you're trying to do string processing, then you're not really generating an SQL query. You're generating a string that can produce an SQL query. There's a level of indirection that opens up a lot ...
57
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
Is there any reason at all?
Yes. Those pieces of software will be using ISO 8601.
ISO 8601 has a number of advantages over other date formats:
It's a standard with a spec document :)
It's ...
55
votes
Accepted
Why store flags/enums in a database as strings instead of integers?
Yes, storing strings instead of numbers can use more space. The reason that high-profile pltforms are doing it anyway is that they think the benefits of that solution are greater than the cost.
What ...
55
votes
Is it considered an anti pattern to write SQL in the source code?
Most standard business applications today use different layers with different responsibilities. However, which layers you use for your application, and which layer has which responsibility is up to ...
55
votes
Is there any technical reason why, in programming, the default date format is YYYYMMDD and not something else?
It's because all the other ways to do it are ambiguous.
01/02/2003 what does that mean? January second 2003? Or in Europe: February 1st 2003? It gets even worse if you use two digits for the year, ...
54
votes
Why do beginners still write SQL? Why do we not push them to use a higher level abstraction?
Simple.
Because there is no way you would really understand how to use a database if you're only interacting with it through the ORM.
This already happened in practice to many beginner programmers ...
53
votes
Why not return dates as a string from the database?
He is saying to use the web server to convert the data time to a string. I am saying do it on the database server and not the web server. Why do you think that is better? - M T Head
I want to know ...
48
votes
Accepted
Using a relational database vs JSON objects for event/activity data
I think your question really boils down to: When should I use a NoSQL approach vs. RDBMS? You settled on JSON early (a NoSQL-ish decision), perhaps because you've got Ajax consumers.
The answer of ...
45
votes
Why not just make non-parameterized queries return an error?
There are too many cases where using a literal is the right approach.
From a performance standpoint, there are times that you want literals in your queries. Imagine I have a bug tracker where once ...
42
votes
Accepted
Is the use of NoSQL Databases impractical for large datasets where you need to search by content?
While I agree with your premise that NoSQL is not a panacea for all database woes, I think you misunderstand one key point.
In NoSQL database you have only one criterion you can search for ...
41
votes
Why is most SQL written in YELLING?
It's using case as a form of syntax highlighting. It makes the logic distinct from the table names and field names.
It's a form that predates new-fangled things like having more than one colour on a ...
41
votes
Is the use of NoSQL Databases impractical for large datasets where you need to search by content?
Generally speaking, if your workflow is a perfect match for relational database queries, you'll find relational databases to be the most efficient approach. Its kind of tautological, but its true.
...
41
votes
Accepted
Is domain driven design an anti-SQL pattern?
These days, you are likely to see reads (queries) handled differently than writes (commands). In a system with a complicated query, the query itself is unlikely to pass through the domain model (...
39
votes
One of my team members committed SQL injection-vulnerable code; should I report it to the manager?
There are only two good reasons to report something like this to management: is if you believe that the coder who did this was malicious and attempting to sneak something through, or if you believe ...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
sql × 729database × 182
database-design × 122
c# × 82
sql-server × 78
relational-database × 54
design × 48
mysql × 47
nosql × 45
php × 35
java × 28
design-patterns × 26
performance × 25
architecture × 24
orm × 24
.net × 23
postgres × 17
query × 17
tsql × 17
object-oriented × 16
rest × 16
rdbms × 15
stored-procedures × 15
asp.net × 13
web-development × 12