current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote -1 down vote favorite
1

I've given this so much research but still couldn't come to my own conclusion, before continuing this question I would like to give you a rough idea of my current situation. I'm 18 years old and have recently been selected and offered by my school to design and develop a database system for my Psychology department. The system itself is very easy, especially on paper, but after reading so much I'm not sure how to tackle this situation.

I've never built a whole system before, I've worked with multiple APIs, game clients, personal programs and small mini projects but never have I done it for someone else. I kindly as for your help to answer my question.

I have been coding for a while and Java is my strongest point, I still have alot to learn in regards to other C-Style languages, my school trusts me and I'd feel ashamed to let them down. What constitutes a good Database system?

When building a database system what are the first key factors that one ought to consider? I presume it's directly related to the actual skeleton of the system and the layout. That's relatively easy to do or better, not as complicated as the rest to do. This would include a normalized database which is secure and easy to use.

This is where my question comes in with Java and SQL joined together. I can obviously(And already have) written a database system entirely in Java, literally no SQL in it, wrote the queries myself and did it all with Java however I don't think that's very satisfactory for most people (I'm not sure why, if someone could clarify would be great). I've read into JDBC and I still have to go a bit more into it as self-teaching it does get a tad tricky every now and then.

I however searched more into it and have seen various Java Database Engines, such as:

HypersonicSQL - http://java-source.net/open-source/database-engines - Open source Database Engine

How good would it be to work with such engines, is there any security risks run with it or should I just scrap off the idea?

I know a lot of you here on StackOverflow are VERY knowledgeable in this field and know alot of you have built your own database systems (offline) so I kindly ask to pass on some knowledge that could prove valuable.

share|improve this question

closed as too broad by MichaelT, gbjbaanb, Wayne M, GlenH7, gnat Jul 27 '14 at 19:35

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs. If this question can be reworded to fit the rules in the help center, please edit the question.
1  
If you limit your question to one question in the post, it would likely be a better question (and better focused) - there are several in there as it is. Asking for advice on a broad topic is also, well, too broad. You really need to focus it to the problem you are encountering. There are books and courses on database design and trying to compress that into a text area doesn't make for a good answer. –  MichaelT Jul 24 '14 at 14:22
    
I understand very well Michael and I kind of got that impressions whilst re-reading it aswell. The issue is I've been reading too many books regarding this situation where one says A and other says B. If I can simplify it as much as possible to a fraction of what I've written it would be. "How should I go about designing and developing a good database system". Even pointers to good books would be very helpful! –  Juxhin Jul 24 '14 at 14:24
1  
Probably relevant - Where to Start –  Dan Pichelman Jul 24 '14 at 14:58
    
No idea what the Psychology Dept. is doing with these data, but make sure there aren't security issues due to patient/subject information. This is not a good project for a first time developer (no offense to your skills) and could literally be messing with people's lives. –  JeffO Jul 24 '14 at 15:40
1  
But ask permission ASAP. –  Basile Starynkevitch Jul 24 '14 at 16:48

3 Answers 3

up vote 2 down vote accepted

I read through your question and it sounds like you are unsure about using a database management system (DBMS). Your school has asked you to create a database. To me all that really means is they want a place to store and retrieve data. It doesn't necessarily mean that you need to create a DBMS. With that said, there are a lot of advantages to working with a DBMS. Some of these advantages are:

  • Optimized retrieval
  • Enforces data consistency & redundancy
  • Enforces concurrency rules

You can read about these advantages and more here: http://navdeep19.blogspot.com/2012/04/advantages-and-disadvantages-of.html

What this boils down to is a DBMS provides a lot of functionality that is needed in a robust database. You want to make sure that constraints aren't violated, data isn't accidentally overwritten and your queries are a fast as they can be. A DBMS will take care of these problems for you because that's its job.

Back to my point about you don't necessarily need a DBMS. It's best to take into account what the client plans to do with the database now and in the future. It might make sense to simply use flat files or maybe a relational DBMS like Hypersonic will work. Which method of data storage you choose will depend on your clients needs. I'll provide a short overview of the three storage methods I've mentioned here.

Flat Files

  • Great for data that already has a structure. You might store a JSON objects in a flat file. You could also create your own structure and parse the file for the data.
  • Doesn't enforce constraints, so you can have redundancy in your data.
  • Your relying on the Windows Operating System to optimize your data retrieval.
  • Your relying on the Windows Operating System for your security.

Relational Database

  • Enforces data integrity by preventing redundancy.
  • Enforces constraints on your data by using private and foreign key rules.
  • Heavyweight solution to data that needs to have a defined structure.

The security risks of the product you choose depends heavily on how the database is going to be deployed. For example, if the database is going to be public facing, then your going to have security holes related to things DOS attacks. More background in this area of your problem would help narrow down some answers to your question.

share|improve this answer
    
First and foremost I'd like to say thanks for taking the time to write all that, it honestly means alot the amount of help I'm getting here. Just reading this makes me realise alot, from what I can see a Relational Database fits my client's needs better than a flat-file system(Which is what they're using) in the comment below I will give you a basic idea of what the Database is to hold. Also from what it seems going for a Relational Database using a DBMS would help alot with the development for the reason inside that article you posted so I think I'll go ahead with that. –  Juxhin Jul 24 '14 at 14:50
    
The database is for the Psychology Department in my school, they need a very simple system, it is to hold all the students inside a particular 'club' and hold various information(that the client specified) inside each student and that's basically it. All in all I just need a table for students and one for teachers(And possibly link that with the login frame but that's extra). Also this isn't for public use, it's entirely private and I think they even want it offline, I was thinking of proposing the idea of server backups but I didn't mention it yet –  Juxhin Jul 24 '14 at 14:51
    
Your database is pretty simple, so you could explore XML as a storage method. If your looking for advice on writing a relational database schema, then I suggest creating another question :) That's a whole other can of worms! –  Cameron McKay Jul 24 '14 at 15:01
    
Alright thanks alot pal, you were of great help :) –  Juxhin Jul 24 '14 at 15:02
up vote 0 down vote

It sounds to me like you're having trouble connecting your database to your code. It's really not that hard. You've already read about using JDBC to connect to a database and you can use it to run any query or DML statement you want. The tricky part is in keeping this code neat and tidy. There are many sorts of Data Access design patterns you can research and implement to make this easier. You can also make use of existing frameworks such as Hibernate or MyBatis (just two of many) to handle the connection between your code and database if you don't want to write your own simple system.

share|improve this answer
    
Thanks alot! Will go ahead and check those two frameworks out. Also, where would you normally leave the database itself saved? Inside the folder with the actual code itself I presume? So that said, if I have my SQL database inside my project folder and connect to it using JDBC I could simply run queries and so on with it? I've tested something similar last time locally on my system using MySQL workbench but wasn't sure if the same concept is applicable to my situation –  Juxhin Jul 24 '14 at 14:36
1  
@Juxhin: You don't normally connect to a database file, normally you have a database server runnning (such as MySQL, Oracle, MS SQL,...) and connect to that (via the correct URL) and let the server worry about the file location. Of course there are some databases such as SQLite which are file based because they don't run as separate servers, they are accessed via a library and driver. –  FrustratedWithFormsDesigner Jul 24 '14 at 15:24
    
Hey thanks for answering again, your help is greatly appreciated. I kindly ask you to check the comment a left above in the answer which gave a basic overview of what my client would like. If the client desires an offline system should I opt for SQLLite? –  Juxhin Jul 24 '14 at 15:36
    
@Juxhin: SQLite is usually used as an embedded database for a single instance of an application. If you don't need a single and centralized database where all applications connect to, SQLite might be a good choice. It is not as feature-rich as many heavier RDBMSs, although that usually gives it much better performance. –  FrustratedWithFormsDesigner Jul 24 '14 at 15:43
    
I doubt I'll need all too many features for such a simple system really and maybe sacrificing certain features for better performance may end up with a better overall result –  Juxhin Jul 24 '14 at 15:44
up vote -2 down vote

You need to read the wikipages on database, database schema, database design, relational model, SQL, NoSQL.

I hope you are familiar to Linux (or are willing to learn it). Consider using sqlite, mariadb, postgresql or mongodb ...

Perhaps try to get some help from a tutor, etc.

If you go the SQL way, I would believe that you should not have very wide tables. Having several dozens of columns in a single table is IMHO a symptom of a bad design. Likewise, having hundreds of tables is suspicious. But YMMV.

Don't forget to backup your database.

share|improve this answer
1  
Hey Basil thanks for the answer! I have basic knowledge into database designs and relational models aswell as basic SQL. Haven't given Linux much thought actually, might be something worth looking into –  Juxhin Jul 24 '14 at 15:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.