Unanswered: Would PostgresSQL be ideal for this scenario?
Okay, I'm trying to upgrade my current database (Excel; I know, boo) and I was wondering if PostgresSQL might be ideal for my situation.
Okay, I have a Excel sheet with two tabs, one for demographic information for parents and one for children. I would like to be able to import that information into the PostgresSQL database and create fields for each of the columns that currently exist, thus allowing new entries to be added to their respective column in the background (I know I didn't put this very well, but I can clarify; maybe it's because I'm still thinking in Excel terms).
Aesthetically,I want the database that have a front end that is identical to a paper form that the staff has already been utilizing. Reason being, that, the staff will need to print out hard copies of the electronic form (containing the data for a particular client) for client files. Also, said form has already been approved by our grantor and has all the necessary logos, etc... attached. So, I will need to be able to do the same on the front end of the database.
Lastly, the only other "biggie" for me would be to be able to recall a client and all of their demographic information and have it populate into the fields of the front end, using a search function or query.
I've already downloaded the manual for PostgresSQL and am ready to go full steam ahead, but I don't want to hit the roadblocks that I encountered while trying to do the same with Access, which led to my simply regressing to Excel for the time being.
Without knowing the type of data that you're talking about, We can only reply in generalities, but, keep in mind that ANY database works differently than Excel. What is a good idea in Excel may turn out to be an awful approach with a database.
First things first, take a look at the design of the database.
Databases don't work as well when you have 'wide' structures, with much repeating data, in a single table. They can 'do' it, but, it doesn't play into a database's strengths. By properly analyzing the requirements of the data, you can then design a data storage solution that fits the requirements, and has the ability to 'grow' as needed. By arranging the data in related tables, where the data is more vertical, in columns, your design will be more flexible, and more responsive to future changes in requirements.
As an example, suppose you have a paper form which has a column for each month, and you've entered summary data into the form in these positions. If you just copy this structure into a spreadsheet, and enter the data into the spreadsheet at these positions, what do you do when next year rolls around? The obvious solution is to add another spreadsheet file, copying the format from the previous file.
Well, in a database you DON'T want to add another table to hold another years' worth of data. It makes querying and reporting a nightmare. Instead, you add a table with a date field, and a value field. And, you enter the date and the value into the table. To view a year at a time, you just retrieve ONLY the data which matches the date range in question.
To view a different year, you just change the query which returns the data to the view - (using a different date range, but, with the same table.) Databases work very well when the data is 'vertical', in columns. SQL has many 'aggegrate' functions (average, min, max, counts, sums, etc.) which work great in this case. When the data is spread horizontally, though, it's a pain to get at in aggregate.
"Aesthetically,I want the database that have a front end that is identical to a paper form that the staff has already been utilizing." I have to tell you, from an application/database design mode, this requirement would ring warning bells in my mind. Paper forms are not well suited for, and are often not designed with, relational database storage in mind. It is often better to break a form down into its data requirements, and treat each section separately in the front end. The sections can be related, and 'tied together,' (I often use a tabbed interface for related data.) Reports can be printed out just about any way you would like to see them, and can look almostt exactly like the old paper forms. But, how you enter the data is efficient, and how you store the data is efficient.
I think the key thing to remember here is that for efficiency's sake, data presentation/editing and data reporting should be treated as two separate (but, related) roles. With a spreadsheet, they are forced to be closely tied together, and often, identical.
Take a look at database tutorials on promary and foreign key relationships, as well as database normalization, before you lock yourself into an application that ties BOTH hands behind your back.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
I unfortunately had my first experience with a database using Microsoft Access 2003, which was very lackluster in it's capabilities and functioning. I was able to generate a database, but I eventually just deserted it, retrograding to Excel for the mean time.
I totally understand that using a database front end as a form for printing may not be the most idyllic thing to do, but, I really want to prevent having to do the double work of setting up a form in Excel, using that to collect the information and then importing that into my database.
But, as it is, that may be the only way, currently. But, as you say, maybe I can resort to leaving the "form" design to the the final report that is generated. But then, I'm a little hesitant about that because that would involve having my co-workers enter the information and then learn how to print a report/form for said client. Not "impossible", but, a task to reckon with.
I guess, really, I'm going to have to create a database in the long run, no matter what, so my main concern will be which option will be most affordable (free is wonderful), most malleable and has the least possible learning curve. I know I'm asking for a lot, but I'm afraid of getting knee deep into Postgres and then feeling unsatisfied like I did with Access.
And, yeah, I learned quite a bit about relational database storage and topics of that ilk from my dreaded period of utilizing Access. I know that SQL in and of itself is another beast, but the tutorial in the Postgres manual looked pretty helpful.
As loquin has already said: you need to understand that the database is something different than the front end. Access lets you easily forget that.
First try to create a proper (relational) data model to meet the requirements of your business. Rip out the essentials that you need to store, find the ways how you read and write the data and optimize the model against that.
Once that is done think about how you can present that to the user in a meaningful way.
I partially disagree with loquin's comment
Paper forms are not well suited for, and are often not designed with, relational database storage in mind. It is often better to break a form down into its data requirements, and treat each section separately in the front end
It is true that paper forms are not designed with a relation model in mind, but they reflect the business process and therefor the front end should try to support this process as much as possible.
Sometimes the requirements for the front end contradict to the requirements for the business data (note "process" vs. "data") and that's where the tricky part begins and that's where the experience of an front-end developer and the experience of a data-modeller come into play. Some minor trade-offs here, a bit of streamlining there... The problem is to decide where to make trade-offs and what that means.
For the back-end part, PostgreSQL is most likely the right choice although it might not look to you like that at the beginning because switching from Access (which is a front-end not a database) or Excel to a full-blown relational database requires some changes in the mind-set and you need to be prepared for that.
The reason why the back-end data model is so important is that the database and the data in there will live a lot longer (if well designed) than your application. And it's very likely that at some point (if the data is valuable) there will be more than one application access the data.
Note that I'm saying "there will be" not "there might" - out of experience!