Tag Info

Hot answers tagged

44

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

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


38

C. J. Date goes into detail about this in Chapter 7 and Appendix B of SQL and Relational Theory. You're right, there's nothing in relational theory that prohibits an attribute's data type from being a relation itself, as long as it's the same relation type on every row. Your example would qualify. But Date says structures like this are "usually--but not ...


21

A) Too many things can fail with the database, and logging this failures is important too. B) Unless you have a database system allowing autonomous transactions (or no transactions at all), logging would require a seperate connection so a rollback or commit in logging doesn't interfere with rollback or commit in the application. C) Many things worth ...


18

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


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


14

Some of the earliest database systems were based upon the Hierarchical Database model. This represented data in a tree like structure with parent and children, much like you are suggesting here. HDMS were largely superseded by databases built upon the relational model. The major reasons for this were that RDBMS could model "many to many" relationships which ...


13

I've seen logs written to the DB before (and sometimes you get configurable options for logging, where trace goes to file, errors to DB, fatals to Windows Event log). The main reasons are speed and size, enabling some tracing can produce vast, vast qualtities of logging - I've trawled through log files gigabytes in size. The other main reason is that ...


13

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


11

ORMs 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 ways to accomplish this is by ...


11

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


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

I suppose that your question really is centered at the fact that while databases are based on a solid logic and set theroretic basis and they do a very good job storing, manipulating and retrieving data in (2-dimensional) sets while ensuring referential integrity, concurrency and many other things, they don't provide an (additional) feature of sending (and ...


10

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


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

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


10

They do work out of the box, for the most part, but you do have to know how to use them. They don't come pre-populated with your application's data model, because they don't know what that is, so they're a blank slate, in that respect. SQLite will support small to medium websites with nominal concurrency and relatively low traffic, which describes probably ...


9

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


9

Deep down, in the guts of a relational database, its all rows and columns. That is the structure that a relational database is optimized to work with. Cursors work on individual rows at a time. Some operations create temporary tables (again, it needs to be rows and columns). By working with only rows and returning only rows, the system is able to better ...


9

In large projects, and specially those in mainframes, this is not the case. In fact if you search job sites you will see several positions for data modelers. Also, having many columns on a single table does not go against normalization. Nevertheless, your observation is valid for some projects. Database design is one of the skills required to build quality ...


8

Speed is one reason; others are: Eliminating points of failure. A filesystem rarely fails under conditions where a DBMS wouldn't, but there are lots and lots of error conditions in databases that simple don't exist in filesystems. Low-tech accessibility. If things go really really bad, you can boot into a rescue shell, or mount the disk on a different ...


8

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


7

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


7

"All PKs are surrogates" is not a very sound strategy at all and certainly not one that you are ever likely to find an "authoritative" source for. Firstly think about what is meant by "primary key" in this context. In the relational model there are no "primary" keys - meaning no one key which is fundamentally different from any other key of the same table. ...


7

SELECT count(ID) FROM posts WHERE deleted = false If you want to put the results of these in the footer of your forum you would need to add a dummy parameter just to say false every time. Or the naive web programmer looks up how to disable that warning and then continues on. Now you can say you would add an exception for enums but that just opens the hole ...


6

When databases first appeared, OOP still wasn't the way to program. Relational databases on the other hand gained 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 ...


5

Actually Oracle supports what you want but you need to wrap the sub-query with "cursor" keyword. Results are fetched via open cursor. In Java, for example comments would show up as result sets. More on this see Oracle's documentation on "CURSOR Expression" SELECT id, content, cursor(SELECT * FROM comments WHERE post_id = 7) AS comments FROM posts WHERE id = ...



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