395
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 leading zero if needed).
In fact it is one of the date formats defined by ISO 8601. That standard also defines a date-and-time format, 2015-03-27T15:26:40Z, which ...
251
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 table with that assignment info, with relevant relationships.
Example, you have the following tables:
Persons:
+−−−−+−−−−−−−−−−−+
| ID | Name |
+====+======...
170
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 is a human readable form, and especially when it's a lossy/one-way sort of conversion.
Why? Because it is assumed that the type provides you with lots of handy ...
148
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 potential downsides include an extra index to maintain and extra storage space used. That's never been enough of a reason to me to not use one.
147
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 date as you update your database. Everywhere you accept input for your queries. And not screw it up.
So yes, that sort of thing would stop SQL injection attacks,...
136
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. The same goes for MM and DD, that's just how the number system works.
So to keep the order between fields consistent with the order within fields, the only ...
116
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 more easily when we need to. Therefore, having SQL outside of the persistence layer completely foils the effort of having a separate persistence layer.
As a ...
106
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 an auto increment field in all tables.
If you have a table where there are no obvious keys, an auto-increment field seems like a good idea. After all, you don't ...
80
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". It just have to be encoded correctly before being used in SQL, which is what prepared statements does.
After you added the note, it seems you are basically ...
74
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 an idiot, and some excerpt from your codebase is likely to end up on The Daily WTF someday. You're absolutely right that his approach doesn't make sense, and ...
67
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 directly using.
Using select * when you're typing queries by hand is fine, not when you're writing queries for code.
64
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.
When transfering data between two databases, especially when the data is in more than one table (i.e. master/detail), it's not straight-forward since sequences ...
60
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 of room for errors and bugs. It's somewhat surprising really, given that in most contexts we're happy to interact with something programmatically. For ...
57
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 unambiguous. mm/dd/yyyy and dd/mm/yyyy can be confusing unless it's past the 13th day.
It lexicographically sorts into ascending time order, so no special date-sorting ...
55
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 you and your team. Before you can make a decision about if it is right or wrong to place SQL directly in the function you have shown us you need to know
which ...
55
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, as 01/02/03.
That is why you use YYYYMMDD, it's the convention which enables us to communicate clearly about dates, 20030201 as a date is always clear. (and it ...
54
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 out there. They have never written a single line of SQL, because their favorite ORM does it for them, and, surprise, surprise, they could even write applications ...
53
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 are the benefits? You can easily read a database dump and understand what it's about without memorizing the enum tables, and even semi-official GUIs might ...
53
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 the type.
I really don't care if your database stores information in a string, some ints, or bytes, because, well in the end it's always bytes anyway. That ...
52
JOIN and INNER JOIN are the same, the inner keyword is optional as all joins are considered to be inner joins unless otherwise specified. The difference between JOIN and FULL OUTER JOIN is the same as the difference between INNER JOIN and FULL OUTER JOIN.
An INNER JOIN will only return matched rows if a row in table A matches many rows in table B the table ...
52
It's returning exactly what you asked for: a single record set containing the Cartesian product defined by the joins. There are plenty of valid scenarios where that's exactly what you would want, so saying that SQL is giving a bad result (and thus implying that it would be better if you changed it) would actually screw a lot of queries up.
What you're ...
47
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 course to when to use NoSQL approaches vs. RDBMS's is basically about what type of data you're working with and what consumers you anticipate having. If your ...
45
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 it gets big enough to worry about performance I expect that 70% of the bugs in the system will be "closed", 20% will be "open", 5% will be "active" and 5% will ...
42
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 effectively - the key.
This is clearly not true.
For example MongoDB supports indices. (from https://docs.mongodb.org/v3.0/core/indexes-introduction/)
Indexes ...
41
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 screen. We used to do it with UCSD pascal too.
41
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.
The claim that many NoSQL advocates would make is that many workflows were actually massaged into a relational form, and would have been more effective before ...
41
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 (which is primarily responsible for maintaining the consistency of writes).
You are absolutely right that we should render unto SQL that which is SQL. So we'll ...
39
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 that the coder who did this is incompetent, which can be just as harmful as a malicious coder.
From the way you describe him, it sounds like you believe he's ...
Only top voted, non community-wiki answers of a minimum length are eligible