Relational database normalization

Normalizing allows you to change the design easier to potentially point more than the 4 directions (maybe up, down, portals, etc). Could make for some interesting design possibilities depending on your project goals, but it complicates the queries.

If you know for certain you'll never want to change from storing the information for the 4 possible directions, then all 4 in the screens table would be fine. Simple to query, and you could add more columns easily. Not that it would be super difficult to port over to a normalized version, but it's yet another thing you'd have to do if you made that decision.

Have you considered sqlite instead of full MySQL?

I didn't know about SQLite until you brought it up.

Basically, I need to store and retrieve very large BLOBs, consisting of PNG files. Surely SQLite does this too.

Thank you.

Holy cow, SQLite is awesome. I got the BLOB insertion working, and it’s not very complicated. This is exactly what I wanted! Thank you so much khawk!

I was having difficulty finding a distilled example of how to select a blob, so I thought that I would give ChatGPT another try. I chose to ask: ‘SQLite select blob C++’ and it returned a distilled example.

#include <sqlite3.h>
#include <iostream>
#include <vector>

int main() {
    sqlite3 *db;
    sqlite3_stmt *stmt;
    const char *sql = "SELECT your_blob_column FROM your_table WHERE your_condition_column = ?";
    int rc = sqlite3_open("your_database.db", &db);

    if (rc) {
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        return rc;
    }

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

    if (rc != SQLITE_OK) {
        std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_close(db);
        return rc;
    }

    int param_value = 123; // Example value
    sqlite3_bind_int(stmt, 1, param_value);

    rc = sqlite3_step(stmt);

    if (rc == SQLITE_ROW) {
        const void *blob = sqlite3_column_blob(stmt, 0);
        int blob_size = sqlite3_column_bytes(stmt, 0);

        std::vector<unsigned char> blobData((unsigned char*)blob, (unsigned char*)blob + blob_size);

        // Do something with blobData...
    } else if (rc == SQLITE_DONE) {
        std::cout << "No rows found." << std::endl;
    } else {
        std::cerr << "Failed to step statement: " << sqlite3_errmsg(db) << std::endl;
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return 0;
}

The whole code is:

// https://stackoverflow.com/questions/18092240/sqlite-blob-insertion-c
int InsertFile(const string& db_name, const string &file_name)
{
	ifstream file(file_name.c_str(), ios::in | ios::binary);
	if (!file) {
		cerr << "An error occurred opening the file\n";
		return 12345;
	}
	file.seekg(0, ifstream::end);
	streampos size = file.tellg();
	file.seekg(0);

	char* buffer = new char[size];
	file.read(buffer, size);

	sqlite3* db = NULL;
	int rc = sqlite3_open_v2(db_name.c_str(), &db, SQLITE_OPEN_READWRITE, NULL);
	if (rc != SQLITE_OK) {
		cerr << "db open failed: " << sqlite3_errmsg(db) << endl;
	}
	else {
		sqlite3_stmt* stmt = NULL;
		rc = sqlite3_prepare_v2(db,
			"INSERT INTO DEMO_TABLE(DEMO_FILE)"
			" VALUES(?)",
			-1, &stmt, NULL);
		if (rc != SQLITE_OK) {
			cerr << "prepare failed: " << sqlite3_errmsg(db) << endl;
		}
		else {
			// SQLITE_STATIC because the statement is finalized
			// before the buffer is freed:
			rc = sqlite3_bind_blob(stmt, 1, buffer, size, SQLITE_STATIC);
			if (rc != SQLITE_OK) {
				cerr << "bind failed: " << sqlite3_errmsg(db) << endl;
			}
			else {
				rc = sqlite3_step(stmt);
				if (rc != SQLITE_DONE)
					cerr << "execution failed: " << sqlite3_errmsg(db) << endl;
			}
		}
		sqlite3_finalize(stmt);
	}
	sqlite3_close(db);

	delete[] buffer;
}


int retrieve_file(const string& db_name, const string& file_name)
{
	sqlite3* db;
	sqlite3_stmt* stmt;
	const char* sql = "SELECT demo_file FROM demo_table WHERE ID = ?";
	int rc = sqlite3_open(db_name.c_str(), &db);

	if (rc) 
	{
		std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
		return rc;
	}

	rc = sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr);

	if (rc != SQLITE_OK) {
		std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
		sqlite3_close(db);
		return rc;
	}

	int param_value = 1; // Example value
	sqlite3_bind_int(stmt, 1, param_value);

	rc = sqlite3_step(stmt);

	if (rc == SQLITE_ROW) {
		const void* blob = sqlite3_column_blob(stmt, 0);
		int blob_size = sqlite3_column_bytes(stmt, 0);

		std::vector<unsigned char> blobData((unsigned char*)blob, (unsigned char*)blob + blob_size);

		ofstream f(file_name.c_str(), ios_base::binary);
		f.write(reinterpret_cast<char*>(&blobData[0]), blobData.size() * sizeof(unsigned char));
		f.close();
	}
	else if (rc == SQLITE_DONE) {
		std::cout << "No rows found." << std::endl;
	}
	else {
		std::cerr << "Failed to step statement: " << sqlite3_errmsg(db) << std::endl;
	}

	sqlite3_finalize(stmt);
	sqlite3_close(db);

	return 0;
}



int main(int argc, char** argv)
{
	//string filename = "";

	//if(openFileDialog(filename))
	//	cout << filename << endl;	

	//InsertFile("test.db", "C:/temp/1.png");
	int x = retrieve_file("test.db", "C:/temp/1_out.png");

	cout << x << endl;

	return 0;
}

Out of curiosity, why do you want to store the blobs in the database? Vs. store the paths/filenames to the PNG's in the filesystem. Just curious the goal.

I guess maybe using the database might be cool, I wouldn't do it myself, but I guess u get all the features, but it would slow u down I think. Doesn't a game need max perf?

MagnusWootton said:
I guess maybe using the database might be cool, I wouldn't do it myself, but I guess u get all the features, but it would slow u down I think. Doesn't a game need max perf?

Games use relational databases for all kinds of things. Yes, performance absolutely matters but mostly in the right places at the right times.

What you're looking up, when, and where, make a huge difference. A flat data table loaded in memory can be best measured in microseconds and potentially nanoseconds, a simple local relational database is best measured in milliseconds for something simple, a remote query is best measured in fractional seconds. Each is suitable or unsuitable in different situations.

Data like quest details or a lot of bulk information during long loads is just fine in databases. Lots of persistent information also works well in databases. Do the bulk processing and pull it into faster query results that are then used moment-to-moment. For writing, similarly do the slow work of pushing out database stuff when it makes sense versus the moment-to-moment updates kept in faster memory. Games with more complex shared worlds have tiers around what gets persisted and when. Some are worth the time for atomic consistency reasons like auction trades, most are transitory data that never hits a database like your moment-to-moment ammunition count.

khawk said:

Out of curiosity, why do you want to store the blobs in the database? Vs. store the paths/filenames to the PNG's in the filesystem. Just curious the goal.

Data integrity, thanks to foreign key constraints. I’m only using SQLite for the development. I will eventually have an Export button, which spits out a directory full of files, to be used in production.

MagnusWootton said:

I guess maybe using the database might be cool, I wouldn't do it myself, but I guess u get all the features, but it would slow u down I think. Doesn't a game need max perf?

See my reply to khawk above. I just replied 2 seconds ago, so it’s not your fault.

So basically, if I need to encode one-to-many or many-to-many relationships, then normalization is important. For one-to-one relationships, normalization is not required. Right?

taby said:

So basically, if I need to encode one-to-many or many-to-many relationships, then normalization is important. For one-to-one relationships, normalization is not required. Right?

Generally, yes. For one-to-many and many-to-many, normalization is important to avoid data duplication and maintain integrity.

For one-to-one, normalization could be helpful, but it depends on the use case. You might normalize, for instance, if you want to separate entities into different tables based on data types, or if data isn't needed.

For example, GameDev.net's database is designed with one-to-one normalization of user data, meaning user accounts from user profiles, user settings, and more even though those are all 1-1 mapping. Doing so keeps optional user data out of the main user account table, so it's only stored when needed. It speeds up queries and creates a separation of concerns in the schema. Also makes it easier to extend in the future, if needed, without affecting the core user table.

Any suggestions for GUI-based schema designers that basically spit out a bunch of create table statements?

Few recommendations:

• MySQL Workbench: https://www.mysql.com/products/workbench/
• DB Browser for SQLite: https://sqlitebrowser.org/ (not sure if it has visual designer)
• DBeaver CE: https://dbeaver.io/

Oooooooh, I'm in love with DB Browser for SQLite! It even previews the blobs!

I think that I see. The point of normalization is to take advantage of the low entropy of the data.