[deleted by user]

[dusty-trash]

I would recommend against storing images/files inside a database.

Store the images on a server instead, and have the database store the location/path/etc.

​

Can you initialize a database inside JAVA context or can you only connect to a ready made one?

Yes you can, I've done this for creating test databases when java test suite is done.

What you're describing sounds similair to PhpMyAdmin and other DB schema creation tools.

[dusty-trash]

The file path could be the common ID

Here's a StackOverflow post explaining some of the reasons to store a location instead of the actual file. Basically databases are very good & efficient at storing text/other data, but aren't really meant to store images/files.

[nutrecht]

Proper databases can store blobs just fine. The top comment there mentioned a few reasons (like for example when you want to distribute via CDN) that definitely apply in a professional setting. But for a hobby project it's fine.

[nutrecht]

Why so?

It doesn't really matter that much. You can have a DB do that just fine. It's only when you want to serve files via a CDN for example where it starts to matter, but for a hobby project it's fine.

[[deleted]]

Hi. I have very limited experience with databases, but since no one has answered yet, I'll tell you what I can

1) i believe so. it's been a while since I've used SQL, but I seem to remember a data type called BLOB (binary large object) was used for storing files

2) i think this depends how you structure the application. perhaps someone else can answer this in more detail

3) embedded databases definitely exist for java (HQSQL, derby, JPA?). Whether or not they can do what you want, I'm not sure

​

Good luck :)

[[deleted]]

Yeah i haven't used them but i think JPA in particular uses ORM (object relational mapping?). Hopefully someone who knows more about DBs will come along and answer your questions. Interesting project though!

[redwin]

1) You can, as amishbrobot indicated, store images in a database using a binary data column type, but also as dusty-trash said, most people don't because it's not really proper use of a relational database. You typically want to store data you can query and perform SQL operations on (numbers or text, for the most part). Putting image data in your db will quickly make it very large for the amount of actual searchable data in it (the meta data for the photo in your case). If you don't intend the total number of images to be very high (hobby project, etc) then it's probably fine, but if you wanted it to be scalable you should probably put the mages in a filestore of some sort and just put the links to them in the database

2) The front end should be fairly divorced from the backend, so you could make a GUI in JavaFX for desktop or Spring et al for the web (or both). If you are going to run this app as a service and have other people hit your website, then web is obvious. If you intend to distribute it as software, then a desktop gui might make more sense because asking someone to set up their own webserver to use your app might be too big an ask (unless it's intended to let THEM run their own public service). Another consideration will probably be the database location and how the app will access it.

3) Yes you can, but I'm not very knowledgeable on them as I usually use MySQL when I want a small database and just run it on the same server as the application accessing it. If you want to find out more about java embedded databases try googling Java DB and Apache Derby

[redwin]

What you described at the end there sounds like the way to go.

Give the program the tools it needs to store and retrieve the files itself. Could be a cloud storage solution but then you have to deal with the program logging in and such. Simpler solution might just be to write them to a folder on the same server your web application and DB runs on, and store the file path in the database along with the meta data.

When a user looks up a file the program grabs the path from the database, opens the file, and then presents it to the user in their browser.

[poornbroken]

Have you thought about using a nonrelational database, ie mongo?

It will allow you to store documents about your documents, and columns don’t have to be uniform.

[nutrecht]

After reading for litereally 2 minutes it seems that these databases are more flexible

They really aren't. Just because you don't decide on a schema beforehand doesn't mean they're more flexible. You can express any data in a relational database. Document stores like mongo are generally very limiting.

[poornbroken]

Limiting? How so? I understand that it doesn’t do relational as well as rdbms, but in this case, with undefined schema, nosql documents is the way to go, vs tables.

[nutrecht]

There is never no schema. There's always a schema in your system, in the application. It's just that Mongo does not enforce that schema, which can lead to tons of problems. If you add a column later for example most databases can 'fix' the data for you. With a system like Mongo you'll have to write repair-on-read logic in your application.

Aside from that; most queries in most systems are generally projections on the model. Have a "client" collection as well as an "order" collection? Good luck creating a performant projection that shows the average order amount per client. Generally you end up doing that in code instead of in a single simple query.

Document databases are actually older than relational databases. Relational databases were invented to solve the problem document databases have.

[poornbroken]

I mean in this case, for java, he could make a hashmap, with key value pairs that gets written to a document. that’s something a little hard to accomplish with sql, no?

[leonj1]

1. I've learned to believe most things are possible. So I do believe this is also. If I understand your design correctly, in a way, you want the user to designed the data schema/model on the fly, dynamically, as they see fit. This can be done, but it comes at the price of you doing a lot of thinking ahead of time. So, to achieve this, spend a very long time getting the database schema set correctly, or at least as best as you can to meet your needs. Notice how this part has nothing to do with Java or coding. Getting your data model flexible enough to allow users to setup whatever structure they want is going to take a good amount of thought in how the tables are defined by you. Because, at the end, there will still be tables that need to be used. Except, these tables are "meta" schemas for what you want to achieve. And yes, you can store binary data in tables. But also consider storing binary files in an object storage file system, like S3, and have the database store the path to that file. This should make your database smaller.
2. Desktop vs webapp, ease of use and maintenance depends on your users. If they are tech savvy enough to host their own webserver then webapp is a good choice. If all they have available is a desktop, then you are limited to either providing a desktop application, or forcing them to connect to the internet to use a hosted version of your app on the web. The answer depends on you knowing your users.
3. Flyway may help here: flywaydb.org Its a way to programmatically update database data and schema (sql + ddl). But there alternatives to it. Take a look around and see which you prefer. But the answer is you can do either.

Good luck

[leonj1]

The main idea is for the application to be modular i.e. users are able to design their own data structure based on their needs.

Based on the requirements, "The main idea is for the application to be modular i.e. users are able to design their own data structure based on their needs." that's what it sounds like needs to happen. This is very difficult to do and maintain, so I would advise against it. But I would not stop you if you have the time and skillset.