I am attempting to design a database for any small project I am focusing on. Eventually, Let me turn it into a web-application, but at this time I do not mind just experimentation with data offline. However, I am stuck inside a crossroads.
The fundamental concept will be a user inputs values for 10 fields, to become in comparison against what is incorporated in the database, with every item getting a weighted value. I understand when I had been to code it, which i can use a glance-up tables for every area, accumulate the values, and display the end result towards the finish-user.
Another example could be needing to obtain the distance between two points, each point saved consecutively, using the X value getting its very own column along with the Y value.
Now, basically store data inside a database, must i attempt to fit everything in within queries (that we think would involve temporary tables amongst other things), or simply use simple queries, and manipulate the rows came back inside the application code?
At this time, I am thinking to choose the second (manipulate data inside the application) and merely use queries to lessen the quantity of data which i would need to go examine. An amount everyone suggest?
EDIT: At this time I am using Microsoft Access to obtain the fundamentals lower pat and then try to obtain a good design going. IIRC with my knowledge about Oracle and MySQL you are able to run instructions together inside a batch process and return only one result. Although not confident that it can be done with Access.
If you are utilizing a database I'd highly recommend using SQL to complete all of your manipulation. SQL is much more capable and effective with this type of job as in comparison to imperative programming languages.
Obviously it will signify you are comfortable in considering data as "sets" and programming inside a declarative style. But investing time how to get really confident with SQL and adjusting data using SQL pays off in a major way over time. Not just with this project however for projects later on. I'd also suggest using saved methods over queries in code because saved procedure give a beautiful abstraction layer permitting your table design to alter with time without affecting the relaxation from the system.
A really large a part of using and dealing with databases is knowing Data modeling, normalization and so on. Like anything else it will likely be a effort but over time it'll repay.
May I request why you are using Access if you have a much better database open to you for example MSSQL Express? The migration path from MSSQL Express to MSSQL or SQL Azure even is very seamless and all you do and experience today (within this project) completely means MSSQL Server/SQL Azure for future projects in addition to if the project develops away from anticipation.
I do not understand your last statement about managing a batch process and becoming only one result, but when it can be done in Oracle and MySQL then it can be done in MSSQL Express too.
What Shiv stated, as well as...
A great DBMS has a great deal of solid engineering inside it. You will find two components which are especially carefully designed, namely the query optimizer and also the transaction controller. Should you adopt the vista of utilizing the DBMS as only a stupid table retrieval tool, you will in all probability finish up inventing your personal optimizer and transaction controller within the application. You will not require the transaction controller before you proceed to an atmosphere that supports multiple concurrent customers.
Unless of course your engineering talents are remarkable, you'll most likely finish track of a house brew data management system that's not just like the main one inside a good DBMS.
The training curve for SQL is steep. You have to learn to phrase queries that join, project, and restrict data from multiple tables. You have to learn to handle updates poor a transaction.
You have to learn easy and seem table design and index design. Including, however is not restricted to, data normalization and data modeling. And you'll need a DBMS with a decent optimizer and good transaction control.
The training curve is steep. However the view in the top may be worth the climb.