Tag Info

Hot answers tagged

39

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


33

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


17

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


17

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


11

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


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

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


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

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

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


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


9

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


9

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


9

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


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

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

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


6

I am not able to answer with a proper, argumented answer, so feel free to downvote me into oblivion if I am wrong (but please correct me so we can learn something new). I think that the reason is that relational databases are centered on the relational model, which in turn is based on something I know nothing about called "first order logic". What you may ...


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


6

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


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


5

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


4

I am facing a very similar debate in my own office: we have a large legacy system that uses flat files, and are attempting to persuade some of the long-time users to switch to a database. This is not a simple decision, and there are many, many issues that you will need to carefully consider. Here's a few: BENEFITS of migrating to DB: There are, of course, ...


4

Domains which have entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. An example of such a domain would be a medical practice, where there are a vast number of possible symptoms, but the number of symptoms that ...


4

Queries always produce a rectangular (un-jagged) tabular set of data. There are no nested sub-sets within a set. In the world of sets everything is a pure un-nested rectangle. You can think of a join as putting 2 sets side-by-side. The "on" condition is how the records in each set are matched up. If a user has 3 phone numbers, then you'll see a 3-time ...


3

The use of transactions should pay off here. If any issues arise, don't commit the changes being made, simply roll it back to either the beginning or at a another point in time (break it up into multiple transactions with tables for each step). If you use additional tables for each step in your process, you can go through the data changes made at each point ...



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