387
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 ...
325
In layman's words:
These are things that SQL is made to do and, believe it or not, I've seen done in code:
joins - codewise it'd require complex array manipulation
filtering data (where) - codewise it'd require heavy inserting and deleting of items in lists
selecting columns - codewise it'd require heavy list or array manipulation
aggregate functions - ...
283
You can query data in a database (ask it questions).
You can look up data from a database relatively rapidly.
You can relate data from two different tables together using JOINs.
You can create meaningful reports from data in a database.
Your data has a built-in structure to it.
Information of a given type is always stored only once.
Databases are ACID.
...
249
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 |
+====+======...
204
Whilst I agree with everything Robert said, he didn't tell you when you should use a database as opposed to just saving the data to disk.
So take this in addition to what Robert said about scalability, reliability, fault tolerance, etc.
For when to use a RDBMS, here are some points to consider:
You have relational data, i.e. you have a customer who ...
184
It's not about NoSQL vs SQL, it's about BASE vs ACID.
Scalable has to be broken down into its constituents:
Read scaling = handle higher volumes of read operations
Write scaling = handle higher volumes of write operations
ACID-compliant databases (like traditional RDBMS's) can scale reads. They are not inherently less efficient than NoSQL databases ...
168
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 ...
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,...
138
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.
135
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 ...
123
I would rephrase that to "Never do in code what SQL Server can do for you well".
Things like string manipulation, regex work and such I would not do in SQL Server (barring SQL CLR).
The above tends to talk about things like - joins, set operations and queries. The intention behind it is to delegate much of the heavy lifting to SQL Server (at things it is ...
111
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 ...
95
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 want to select * from blog where body = '[10000 character string]'. You'd rather select * from blog where ...
86
Originally SQL language was called SEQUEL standing for
Structured English Query Language
with the emphasize on English, assuming it to be close in spelling to natural language.
Now, spell these two statements as you'd spell English sentences:
"From Employee table e Select column e.Name"
"Select column e.Name From Employee table e"
Second sounds closer to ...
85
noSQL databases give up a massive amount of functionality that a SQL database gives you by it's very nature.
Things like automatic enforcement of referential integrity, transactions, etc. These are all things that are very handy to have for some problems, and which require some interesting techniques to scale outside of a single server (think about what ...
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.
60
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
One thing that no one seems to have mentioned is indexing of records. Your approach is fine at the moment, and I assume that you have a very small data set and very few people accessing it.
As you get more complex, you're actually creating a database. Whatever you want to call it, a database is just a set of records stored to disk. Whether you're creating ...
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 ...
56
I think you misunderstood what indexing does for database performance.
An index helps the database find rows. Indexes are specialized data-structures that, in exchange for extra disk-space and some performance when inserting and updating, help the database engine home in on matching rows.
Because they take extra space and cost (a modicum of) performance to ...
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
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 ...
51
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
