Tag Info

Hot answers tagged

18

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


6

Option B is the worst choice: Why Because by keeping values in CSV format in a single column, you are violating first NF http://en.wikipedia.org/wiki/First_normal_form. Most critical drawback of violation of first NF is complex join conditions. You have to apply join using LIKE operators and worst wildcard options or using other string functions. ...


5

Correct. The implementation of a unique constraint or index in sql server allows one and only one NULL. Also correct that this technically doesn't fit with the definition of NULL but it's one of those things they did to make it more useful even though it isn't "technically" correct. Note a PRIMARY KEY (also a unique index) does not allow NULLs (of ...


4

Option B is a bad choice and to answer you question on performace. Perfomance wise option A is better choice. Option A will have simple query where as option B need to run complex sql query. Option A offers better indexing options which in turn offers better performance. Option A is neat and clean design.


4

The relationship between normalization and performance is complicated. Sometimes the pursuit of normalization and the pursuit of high performance lead in the same direction, sometimes in opposite directions. You have to look at each case carefully. More importantly, there is more than one measure of "goodness" in a database. In addition to performance, ...


3

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value. There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient ...


3

No, your case does not block on Oracle, nor should it. Concurrency is one the the major reasons to use an RDBMS instead of say, Excel. But just to make it interesting, if you insert the same value in both sessions, it will then block. Session #1 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY); INSERT INTO t1 VALUES (1); -- do not commit transaction Session #2 ...


3

Your colleague is correct that it is easier to simply not think about it and just store everything as a varchar. But this comes at a large cost in terms of space requirements, performance, flexibility in querying data, and most importantly, lack of data integrity. This is not just a one-time cost; it is paid repeatedly over the lifecycle of the ...


2

There are several situations in which it's better to represent numbers using some kind of numeric data-type. It's a little more efficient, but that's just the beginning. You get support for built-in arithmetic using SQL operators without performing type conversions at run time. Not only do type conversions slow things down, but they can result in numerous ...


2

Performance It depends A LOT on the actual data (are there many people having read a few books each, or a few people having read many books), skewing (are there some power-readers?) and the books which you query - like ypercube mentioned the bible. And this is before the optimizer really sets in and decides to completely rewrite your query because it ...


2

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics. https://class.stanford.edu/courses/Engineering/db/2014_1/info


1

Questions to ask could include: What fits well with your current skills? (That might make it 'easier' to use.) Is your focus on getting something working soon, or on developing new skills? What is the cost of your choices (money, time, effort, unfamiliarity, etc)? With your list you do emphasize cost as a factor and the database is not expected to be ...


1

First - stop using the phrase "Null value", it will just lead you astray. Instead, use the phrase "null marker" - a marker in a column indicating that the actual value in this column is either missing or inapplicable (but note that the marker does not say which of those options is actually the caseĀ¹). Now, imagine the following (where the database does not ...


1

I think if you look at your model and you consider the requirements that it doesn't handle (i.e. your questions) then you will find that you need to expand your model somewhat. Consider the following ERD: (Note I use the James Martin crows foot notation which is a little more compact than what you have used but should be pretty simple to understand. The ...


1

This may not be technically accurate, but philosophically it helps me sleep at night... Like several other have said or alluded to, if you think of NULL as unknown, then you cannot determine whether one NULL value is in fact equal to another NULL value. Thinking of it this way, the expression NULL == NULL should evaluate to NULL, meaning unknown. A Unique ...


1

I see one design possible with your reqirement as Book Table BookId PK WriterId -FK PublisherId -FK Title ..Other columns Editions Table EditionId - PK BookId - FK ..Other Columns Sets Table SetsId - PK EditionsId - FK VolumnName-- Like A, B(or 1,2) Now I see one problem with above design, Let say with new editions if publisher changed, then ...


1

I would say that an Edition is an entity that is related to a Book. You then have a one-to-many relationship between Books and Editions. (One book can have many editions.) and a many-to-one relationship between Editions and Publishers (many editions are published by one publisher). When I was building actual databases, back in the day, I always had ...



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