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.
...
203
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 ...
194
First up, you should be more free with read-only access rights than read-write. It might be possible that a hacker has access to your data but isn't able to edit it.
But, much more importantly, this is not about you. The fact that you might be screwed if someone has full access to your database is irrelevant. Much more important is your user's data.
If you ...
143
There are two fundamental advances with the structured approach that can't be emulated using text logs without (sometimes extreme levels of) additional effort.
Event Types
When you write two events with log4net like:
log.Debug("Disk quota {0} exceeded by user {1}", 100, "DTI-Matt");
log.Debug("Disk quota {0} exceeded by user {1}", 150, "nblumhardt");
...
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 ...
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 ...
118
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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.
64
If you get hacked you can restore the site from backups and fix it. But the hacker still has passwords for everyone's accounts! There are documented real world examples of this happening (Sony, Linked-in), where if the password tables had been properly hashed and salted, securing and restoring the sevice quickly would have been much easier.
It's ...
62
I think beyond the technical question, your boss may not have the time to keep up to date on current standards.
Since his stance is not completely out to lunch, just out-dated, respect his position when discussing this matter (and you need to remember to discuss, not argue), and try to work through concerns he has with regards to UTF-8. I suspect the ...
57
One thing that no one seems to have mentioned is indexing of records. Your approach is fine at the moment, and I assume that you have a very small data set and very few people accessing it.
As you get more complex, you're actually creating a database. Whatever you want to call it, a database is just a set of records stored to disk. Whether you're creating ...
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. "...
49
Which of us is right?
Once upon a time, your boss was. But as time goes by, things change. Nowadays, you are (but before running to your boss, be sure to read Nelson's answer too).
Old versions of MySQL, and old versions of mostly everything, dealt much better with the older Latin1/ISO-8859-1(5) than UTF8.
There is a reason why UTF8 has been created, ...
47
It's safe, if that's what you're asking. As long as you're as careful about your security as you are with your data's security.
But don't reinvent the wheel, Stored Procedures ARE bits of SQL stored in a table. And they support, nay encourage, parameterisation.
Also note, you can make your security simpler AND reduce the number of points of failure AND ...
39
My two cents: I do not think it is a good idea. GIT does something like "storing snapshots of a set of files at different points in time", so you can perfectly use GIT for something like that, but that doesn't mean you should. GIT is designed to store source code, so you would be missing most of its functionality, and you would be trading a lot of ...
38
Another concern: if it's a JOIN query and you're retrieving query results into an associative array (as could be the case in PHP), it's bug-prone.
The thing is that
if table foo has columns id and name
if table bar has columns id and address,
and in your code you are using SELECT * FROM foo
JOIN bar ON foo.id = bar.id
guess what happens when someone ...
36
Yes, it is a bad practice.
Performance impact on the DB:
if you do a SELECT with any BLOB column, you will always do a disk access, while without BLOBs you have a chance to get data straight from RAM (high throughput DB will be optimized to fit tables in RAM);
replication will be slow, replication delay high, as it will have to push BLOB to slaves. High ...
36
But I have other problems if someone gets in my database, i.e. deleting data.
It's not about the problems you have, it's about the problems it might cause for all your other users. It's about removing temptation (or even worse, potential liability) for people working on the site to abuse data that's stored there.
See, even though people should use ...
33
Advice: Don't be afraid of learning new things - you made a good First Step in acknowledging that you could do better and then made the effort to learn how you could do better. Yes, it takes more time up front, but the payoff is usually worth it in the long run. Now that you know CodeIgniter, you can use it for the next future project(s). You can put it on ...
answered Dec 28 '11 at 21:12
FrustratedWithFormsDesigner
43.3k6 gold badges116 silver badges169 bronze badges
32
If you want to save SQL statements in a database for later execution, there's a better option than putting them in a table: use the built-in functionality provided for this specific purpose. Put them in stored procedures.
30
I'm currently working on a mobile/desktop/distributed app with exactly the same requirements and issues.
First of all, these requirements are not inherent to mobile apps per se, but to any disconnected/distributed client-server transactions (parallel programming, multithreading, you get the point). As such they are, of course, typical issues to address in ...
29
MySQL timestamps:
Are stored in UTC
They are converted to UTC on storage and converted back to your time zone on retrieval. If you change time zone settings, the retrieved values also change.
Can be automatically initialised and updated
You can set their default value and / or auto update value to CURRENT_TIMESTAMP
Have a range of 1970-01-01 00:00:01 UTC ...
29
Never expose direct access to your database from the web layer. You can never lock that down; someone will send "DROP TABLE table" to your server, however much you lock down the JS source.
All they have to do is see what the browser sends to your server to detect that your server accepts arbitrary SQL commands.
Not that you can lock down the JS code; it is ...
Only top voted, non community-wiki answers of a minimum length are eligible
