Hot answers tagged

385

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 ...


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 ...


118

The database doesn't have to check for data integrity every time application modify data. This is a deeply misguided point. Databases were created for precisely this purpose. If you need data integrity checks (and if you think you don't need them, you're probably mistaken), then letting the database handle them is almost certainly more efficient and less ...


70

TL;DR: Relationship constraints should go in the database. Your application ain't big enough. You are correct, indeed, that enforcing relationships across databases may require enforcing them in the application. I would point out, however, that you should first check the documentation of the database software you are using, and check existing product ...


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

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 ...


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 ...


51

The constraints should lie within your database, as (with the best will in the world), your application will not be the only thing to ever access this database. At some point, there may need to be a scripted fix within the database, or you may need to migrate data from one table to another on deployment. Additionally you may get other requirements e.g. "...


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 ...


25

From Wikipedia: The ORDER BY clause identifies which columns are used to sort the resulting data, and in which direction they should be sorted (options are ascending or descending). Without an ORDER BY clause, the order of rows returned by an SQL query is undefined. So it's undefined. The SQL specification doesn't state the specific order that ...


19

What's interesting about this Q&A thread is that there are actually 3 questions. Everybody has answered a different one, and almost nobody has answered the first one: Why aren't some databases in the wild normalized? Why/when should a normalized database be denormalized? In what situations is it harmful or unnecessary to normalize in the first place? ...


19

Let t1 and t2 be distinct integers that represent two times written in YYYYMMDD formatting. Then t1 < t2 implies that t2 occurred after t1. You lose this ordering with DD and MM first formatting. ISO is, IMO, the only sensible format.


17

You should have relations in the database. As the other answer notes, performance of constraint checking will be far better inside that database than inside your application. Database constraint checks are one of the things that databases are good at. If you ever need additional flexibility - e.g. your noted cross database references - then you can remove ...


16

ORMs(Object-relational mapping) are not mutually exclusive with Stored Procedures. Most ORMs can utilize stored procedures. Most ORMs generate Stored Procedures if you so choose. So it the issue is not either or. ORMs may generate unacceptable SQL (in terms of performance) and you may sometimes want to override that SQL with hand-crafted SQL. One of the ...


16

The approach is, frankly, horrible. As I understand it, you want the column 'Price' to be interpreted as a foreign key to Product.Id, and then "redirect" the query for Pear prices to a query for Apple prices or for Orange prices. This requires complicated logic in your SQL code for what should be a straightforward table look-up, and since data base schema ...


15

No, they don't. Those keys are definitely good enough! They're unique, not rarely going to change, and meaningful, which is a step up over a surrogate key. That's pretty much the definition of a good PK. The restrictions about PKs being immutable and numeric-integer are not part of the Relational Model (Codd's) or any SQL standard (ANSI or other).


15

The assumption built into the question and in some of the answers is that normalization is synonymous good database design. This is in fact often not the case. Normalization is one way of achieving a particular set of design goals and a requirement if you are relying heavily on the database to enforce "business rules" about the relationships between data ...


13

ORMs often assume that the database exists to serve the ORM. But usually the database exists to serve the company, which might have hundreds and hundreds of apps written in multiple languages hitting it. But it's only a case of "ORM vs. Stored Procedures" if you're using an ORM that can't call a stored procedure. Otherwise, it's a case of deciding where to ...


13

Primary and Foreign Keys do not have to be readable. Their purpose is to maintain the internal relational structure of the database, not to be read by a human. Naturally, if there is an appropriate natural key that will never change (I claim these are as rare as hen's teeth or four-leaf clovers, but...), you can use that, and some customers will make that ...


13

We no longer live in one back-end <-> one front-end world. Most solutions involve a web front-end, a mobile front-end, a batch-front-end, and iPad front-end, etc. Database engines already have thousands of tested lines of code optimized to enforce referential integrity. Can you really afford writing and testing referential integrity enforcing code when ...


12

You could use a built in function to concatenate the records together. In MySQL you can use the GROUP_CONCAT() function and in Oracle you can use the LISTAGG() function. Here is a sample of what a query might look like in MySQL: SELECT user.*, (SELECT GROUP_CONCAT(DISTINCT emailAddy) FROM emails email WHERE email.user_id = user.id ) AS ...


12

SQL injection occurs when a query is built by concatenating text from an untrusted and unvalidated source with other portions of a query. While such a thing would most often occur with string literals, that would not be the only way it could occur. A query for numeric values might take a user-entered string (that's supposed to only contain digits) and ...


12

One point not mentioned is that, in interactive inputs, this format allows to control the input. The system cannot know if a month has 28, 29, 30 or 31 days without knowing the specific year and month. When the interactive input mandates that year and month come first it can check if the day (inserted last) is in the allowed range. Granted, the question ...


11

There are multiple reasons. Many developers are only experienced in relational data modeling. To use OO databases, they would need to learn completely different way to model and think about data. This is either really hard or quite time consuming. Relational DBs had lot of time to mature. Even free relational DBs have advanced optimization and indexing ...


11

A lot of developers don't know or care about normalization, or about data modeling or database. For some jobs it's really not important. Sometimes there's a really good reason to de-normalize, for example to make a particular difficult workload perform well. Relational Database concepts are recently less in fashion than they were in the 1990s and 2000s. ...


10

When databases first appeared, OOP still wasn't the way to program. Relational databases, on the other hand, gained a lot of traction. And SQL introduced in the 80's by IBM quickly became lingua franca of all databases. When OOP become popular there were some attempts, but there are some problems. First, true OODBMS is really hard to implement. In case of ...


10

The rules of thumb I use are: Try not to assume any max size. If you must assume a max size, look for any standard regarding the field. Email for example has a max size of 256. Phone numbers have standards that differ depending on scope. If you must assume max size, and you have no standard to go by, pick something ridiculously large and then assume ...


10

You need to be ready for any identifier that is exposed to users/customers needing to be changed, and changing the identity of a row in a database and propagating that change to all foreign keys is just asking to break data. If the data has no natural business key, you can add an additional field for a "business identifier". This should be optimized for the ...


10

The problem with this is that it's returning the user's name, DOB, favorite color, and all the other information stored The problem is that you are not being selective enough. You asked for everything when you said Select * from... ...and you got it (including DOB and favourite colours). You probably should been a little more (ahem) ...selective, and ...


Only top voted, non community-wiki answers of a minimum length are eligible