282

You can query data in a database (ask it questions). You can look up data from a database relatively rapidly. You can relate data from two different tables together using JOINs. You can create meaningful reports from data in a database. Your data has a built-in structure to it. Information of a given type is always stored only once. Databases are ACID. ...


248

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


204

Whilst I agree with everything Robert said, he didn't tell you when you should use a database as opposed to just saving the data to disk. So take this in addition to what Robert said about scalability, reliability, fault tolerance, etc. For when to use a RDBMS, here are some points to consider: You have relational data, i.e. you have a customer who ...


168

Dates, DateTimes and really any other typed object, should generally be left in their properly typed format until the moment you need them to be made into some other type - especially when that type is a human readable form, and especially when it's a lossy/one-way sort of conversion. Why? Because it is assumed that the type provides you with lots of handy ...


157

Because "multiple concurrent writes" is much, much harder to accomplish in the core database engine than single-writer, multiple-reader. It's beyond SQLite's design parameters, and including it would likely subvert SQLite's delightfully small size and simplicity. Supporting high degrees of write concurrency is a hallmark of large database engines such as ...


149

Store all dates in normal DATE field in the database and have additional accuracy field how accurate DATE field actually is. date_created DATE, date_created_accuracy INTEGER, date_created_accuracy: 1 = exact date, 2 = month, 3 = year. If your date is fuzzy (e.g May 1980) store it at start of period (e.g. May 1st. 1980). Or if your date is accurate to ...


142

Reasons in favor of storing files in the database: ACID consistency including a rollback of an update which is complicated when the files are stored outside the database. This isn't to be glossed over lightly. Having the files and database in sync and able to participate in transactions can be very useful. Files go with the database and cannot be orphaned ...


138

It's never a bad idea to have a guaranteed unique row identifier. I guess I shouldn't say never – but let's go with the overwhelming majority of the time it's a good idea. Theoretical potential downsides include an extra index to maintain and extra storage space used. That's never been enough of a reason to me to not use one.


134

Plaintext is binary. When you write an H to a hard drive, the write head doesn't carve two vertical lines and a horizontal line into the platter, it magnetically encodes the bits 010010001 into the platter. From there, it should be obvious that storing plain text data takes up exactly the same amount of space as storing binary data. But plaintext is just ...


132

Premature optimization is "optimizing" something because of a vague, intuitive sense that, y'know, this will probably be slow, especially to the detriment of code readability and maintainability. It doesn't mean willfully not following well-established good practices regarding performance. Sometimes that's a difficult line to draw, but I'd definitely say ...


131

If you create one per query / transaction, it is much easier to manage "closing" the connections. I can see why common sense dictates that you should open one and use it throughout, but you will run into problems with dropped connections and multithreading. So your next step will be to open a pool, say of 50, connections and keep them all open, doling them ...


131

Unicode is certainly difficult, and the UTF-8 encoding has a couple of inconvenient properties. However, UTF-8 has become the de-facto standard encoding on the web, surpassing ASCII, Latin-1, UCS-2 and UTF-16. Just use UTF-8 everywhere. The most important reason why you should support Unicode is that you shouldn't make unnecessary assumptions about user ...


125

On the surface the two technologies may seem directly comparable, however if you spend some time with them you'll soon realize they are not. They were designed to achieve a similar goal, client side storage, but they approach the task at hand from significantly different perspectives and work best with different amounts of data. localStorage, or more ...


103

It doesn't cause database problems. It causes problems in applications written by developers that don't understand databases. At the root of the problem is that much database-related software displays a NULL record as the string NULL. When an application then relies on the string form of a NULL record (likely also using case-insensitive comparison operations)...


101

Before you lose any data, let me try to introduce a sysadmin perspective to this question. There is only one reason we create backups: to make it possible to restore when something goes wrong, as it invariably will. As such, a proper backup system has requirements that go far beyond what git can reasonably handle. Here are some of the issues I can foresee ...


101

Youtube can't use sequentional IDs for two reasons: Its databases are almost certainly distributed, making sequential numbering complicated. It has a privacy option "Unlisted videos": those that don't show up in the search results, but are available if you know the ID. Therefore, the video IDs should be reasonably random and unpredictable. Whether the ID ...


100

Not only do you not want a copy of the production database, it may actually be illegal. For example, in the US, you cannot move production data out of the production environment if it contains regulated information like personal health data, financial data, or even data that could be used in identity theft. If you do, you could be fined, lose your compliance ...


97

MariaDB is a backward compatible, binary drop-in replacement of MySQL. What this means is: Data and table definition files (.frm) files are binary compatible. All client APIs, protocols and structs are identical. All filenames, binaries, paths, ports, sockets, and etc... should be the same. All MySQL connectors work unchanged with MariaDB. The ...


94

I disagree with all the answers before. There are many reasons why it is a bad idea to add an auto increment field in all tables. If you have a table where there are no obvious keys, an auto-increment field seems like a good idea. After all, you don't want to select * from blog where body = '[10000 character string]'. You'd rather select * from blog where ...


91

In many cases, this is a bad idea. It will bloat the database files and cause several performance issues. If you stick the blobs in a table with a large number of columns it's even worse. However! Some databases, like SQL Server have a FILESTREAM column type. In this case, your data is actually stored in a separate file on the database server and only ...


85

Business logic doesn't go into the database If we're talking about multi-tier applications, it seems pretty clear that business logic, the kind of intelligence that runs a particular enterprise, belongs in the Business Logic Layer, not in the Data Access Layer. Databases do a few things really well: They store and retrieve data They establish and enforce ...


75

On the form of the IDs: They're using Base64 (using the characters a-z, A-Z, 0-9, -, and _). This allows them to have 6 bits of information per character. YouTube uses 11-character video IDs, which means they can generate 26*11, or more than 7*1019 IDs. As Tom Scott put it, that's "enough for every single human on planet Earth to upload a video every minute ...


72

Assumed you have some services which can use the same kind of DB system and version, if you use different database or db instances is a decision you should not need to make at design time. Instead, you should be able to make the decision at deployment time, something you can simply configure. Design your services to be agnostic of the place where other ...


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


68

See How much business logic should the database implement? for previous discussion. In general, everyone wants things done in the layer they control. Because then they control it. Every database vendor wants people to put as much logic into the database as possible. Because that locks you into the database. The reasoning is that if multiple ...


67

Think about what you're getting back, and how you bind those to variables in your code. Now think what happens when someone updates the table schema to add (or remove) a column, even one you're not directly using. Using select * when you're typing queries by hand is fine, not when you're writing queries for code.


66

I've been engineering Healthcare solutions for many years. I won't go into all the different reasons that your father shouldn't be doing this; most of the reasons being academic: meaning, if you've been in the industry long enough you know how these things snowball and develop a life of their own. Instead your father, as a physician, needs to understand the ...


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