249

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


119

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


95

I can't tell you why it's a bad idea. I can tell you a bunch of reasons why a relational database is a good idea though. Remember that not everyone consults a dictionary for a definition. More times than not, a dictionary is used to find the correct spelling. This means you're not just finding a needle in a haystack, you are searching the haystack for ...


70

The performance argument is usually the one which is most intuitive. You especially want to point out how it will be difficult to add good indexes in an incorrectly normalized database (note: there are edge-cases where denormalization can in fact improve performance, but when you are both inexperienced with relational databases you will likely not easily see ...


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


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


40

Generally speaking, if your workflow is a perfect match for relational database queries, you'll find relational databases to be the most efficient approach. Its kind of tautological, but its true. The claim that many NoSQL advocates would make is that many workflows were actually massaged into a relational form, and would have been more effective before ...


40

While I agree with your premise that NoSQL is not a panacea for all database woes, I think you misunderstand one key point. In NoSQL database you have only one criterion you can search for effectively - the key. This is clearly not true. For example MongoDB supports indices. (from https://docs.mongodb.org/v3.0/core/indexes-introduction/) Indexes ...


35

You're asking two questions here. First, you ask if its ok to store lists serialized in a column. Yes, its fine. If your project calls for it. An example might be product ingredients for a catalog page, where you have no desire to try to track each ingredient individually. Unfortunately your second question describes a scenario where you should opt for ...


30

Database indexes are modeled after textbook indexes, then made more efficient: The non-indented parts are the primary part you're searching on, and the indented part underneath some of them further identifies specific topics. Each indentation level is similar to another column on the index. Taking advantage of indexes is (I think) partially implementation-...


30

Yes, there are tons of reasons why this may be the better design. You may have an inheritence/extension relationship, e.g. you might have a User table and then an Administrator table which has more fields. Both tables may have a primary key of User ID (and therefore have a 1:1 relationship) but not all users will have a record in the Administrator table. ...


28

It is important to distinguish between different use cases for databases. The traditional business database is accessed by multiple independent applications and services and perhaps directly by authorized users. It is critical to have a well-thought out schema and constraints at the database level, so a bug or oversight in a single application does not ...


27

If you go with the key-value store (which offers you a more impoverished programming model) and it turns out you need more structure (in your case, say, adding a third language), or you need to do more complex queries involving joins, you'll spend a bunch of time reorganizing your keys, denormalizing your data, and/or looping over all the data to find what ...


26

Enumerated types should be a separate table in your database that have an id number and a string name and any other columns you might find useful. Then each type exists as a row in this table. Then in your table you are recording the transactions the "trans_Type" field should be a foreign key to the key of that reference table. This is a standard practice in ...


24

I'll be having to implement a database with my boss ... Using dedicated Database Management software might be considerably easier (sorry; couldn't resist). lngStoreID | vrStoreName | lngCompanyID | vrCompanyName | lngProductID | vrProductName If this database only cares about "logging" which product was sold where, when and by whom, then you might be ...


24

First: plain text is binary (it's not even the UTF8 or ASCII characters "0" and "1" but actual on/off bits) That said, some of the reasons are: Business/design constraints: allowing the number 7626355112 in the HEIGHT column of the PERSON table would be wrong. Allowing "Howya" in the DATE column of an INVOICE would be wrong. Less error prone code: you don'...


22

It maintains referential integrity (yes but can be maintained without it too) You are technically correct that if you're able to maintain referential integrity yourself, you don't need the constraint to exist. But by that same logic, you don't need fire insurance as long as your house doesn't burn down, and you don't need health insurance as long as you ...


22

What you're describing is known as a "many to many" relationship, in your case between Person and Task. It's typically implemented using a third table, sometimes called a "link" or "cross-reference" table. For example: create table person ( person_id integer primary key, ... ); create table task ( task_id integer primary key, ... ); create ...


20

Just because your NoSql database doesn't have a schema in a traditional sense doesn't mean there isn't a logical schema you need to deal with as it changes. In the case of a typical app using MongoDb, most likely your code expects certain fields of the json object to behave in certain ways. If you change the behavior, it follows you might want to update the ...


19

But is that really a big problem when doing upgrades? It can be. Some organizations are -- well -- disorganized, and do a very bad job of schema migration. "Migration Weekend". Stop the servers. Back up and export all the data. Build the new schema (often by modifying the existing schema). Reload data or attempt to restructure in place. "Continuous ...


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


18

Very often, when you have a behavior where you cannot decide which of two objects should have it, that is because either the two objects should actually be one object or there is a third object missing. A great example is the classic "Bank account" that is so often used as an introduction to OO. In the typical example, balance is data and deposit is an ...


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

As a matter of general principle, I summarily add an "autonumber" field to every table I create in a relational database, give it a sensible name, and mark it as the Primary Key. Doing this eliminates all sorts of intricacies, like the "candidate, "secondary" and "tertiary" concepts. Primary keys are well-understood to have certain characteristics: they ...


16

NoSQL is a rather vague term, since it basically covers all database systems which are not relational. What you describe is a key-value store, which is a kind of database where a blob of data is stored under a key, and can be quickly looked up if you know the key. These databases are blazingly fast if you know the exact key, but as you say yourself, if you ...


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


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