382
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 ...
316
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 - ...
275
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.
...
246
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 |
+====+======...
242
I'm going to come out and say it: It's not really a bad practice (and even if it is, its not that bad).
You could make the argument (as Chad pointed out) that it can mask errors like in the following query:
SELECT *
FROM cars car
JOIN manufacturer mfg
ON mfg.Id = car.ManufacturerId
JOIN models mod
ON mod.Id = car.ModelId
...
198
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 ...
169
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 ...
159
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 ...
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
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.
133
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 ...
122
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 ...
121
Because when you have a table with a foreign key you can't name that foreign key "Id". You have table name it TableId
And then your join looks like
SELECT * FROM cars c JOIN manufacturer m ON m.Id = c.ManufacturerId
And ideally, your condition should have the same field name on each sides
SELECT * FROM cars c JOIN manufacturer m ON m.ManufacturerId = c....
117
Why don't you simply handle it like adults: sit down, non-confrontationally, and come up with a list of pros and cons for a naming scheme , agree on one and make it official by writing a short document describing it. Elicit genuine interest in her input so she feels (and is) involved.
If it's mostly a matter of taste and if she's the kind of person who ...
112
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 ...
90
Absolutely! SQL is still the lingua franca of databases and although you may do a lot with ORMs you have to understand SQL to understand the decisions ORMs make and the SQL they generate. Also, there are still lots of things that you have to do with custom sql and stored procedures as well. Sorry, no free lunch.
90
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 ...
81
Not really - that's an incredibly disrespectful thing to do.
You've talked to her and we haven't, but it seems like you would be within your rights to restore the previous naming conventions from a backup or revert them if they are in a source control. DO notify your boss and coworker if you do this, and provide your reason (You can't maintain your own work)...
79
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 ...
72
Makes perfect sense to me. It's just very normalized, which imparts a lot of flexibility that you wouldn't have otherwise. De-normalized data is a pain in the butt.
72
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 ...
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.
66
Ruby's ActiveRecord library and Groovy's GORM use "id" for the surrogate key by default. I like this practice. Duplicating the table name in each column name is redundant, tedious to write, and more tedious to read.
63
This is hard to explain to a lot of programmers, because if you only know basic SQL then it really doesn't give you much of an advantage over the crutch of an ORM. The more advanced SQL concepts, however, are a crucial part of the difference between applications that just work vs. applications that are high quality (in particular, fast and reliable).
I'm ...
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 ...
59
I find that the second form is better. That may be because that is how I learned it, I'll admit, but I do have one concrete reason - separation of concerns. Putting the fields you are using to join the tables in the where clause can lead to difficulties in understand queries.
For example, take the following query:
select *
from table1, table2, table3, ...
Only top voted, non community-wiki answers of a minimum length are eligible
