Optimal Postgres Development Process, Software - Mailing list pgsql-novice
From | Roger Rasmussen |
---|---|
Subject | Optimal Postgres Development Process, Software |
Date | |
Msg-id | 20060815104017.7CCC31CE304@ws1-6.us4.outblaze.com Whole thread Raw |
Responses |
Re: Optimal Postgres Development Process, Software
(<operationsengineer1@yahoo.com>)
Re: Optimal Postgres Development Process, Software (Andreas <maps.on@gmx.net>) |
List | pgsql-novice |
Hi, First a little background: I am an electrical engineer and have had a decent amount of experience programming - but mainly languages like matlab, C++, pascal, VB. Over the last few years I have developed andmaintained an ms access database for a business. It had up to 3 or 4 users at any one time, usually two. It was arranged in a split fashion, with a front end and a back end. Although the backend reached between 10-20 MB in size, it was extremely slow at times because of the involved business logic in some of the queries. Some reports were based on > 30 queries, including VB procedures. Unfortunately, I had a cheapskate boss and was not permitted to get a proper database. This was also 5 years ago and I was learning databases from scratch. As a result of that experience, I am in the position where I am going to have to craft a new database from scratch to do the job of the old. After several weeks/months of searching, I have decided on Postgres as a backaend after starting to be skeptical of the MySQL hype machine - it seems like a toy database for what I want. I also have a pretty good idea what I want from Postgres, although anything I haven't taken into consideration I'd like to know. I have also spent a while searching the mailing list archives and also just browsing the last 6 months of postings to try and find an appropriate answer. Please note: I will NOT be using my old Access frontend - I will create a new frontend from scratch. So, here are my requirements: 1. I don't want to be stuck with a product that is a pain in the ass to update with a developer that could leave me stranded, as was the case with MS Access 97. I want something that will only require me to do work when the business needs changes, not as a result of another company's need to design in obsolescence. I.e. Access as a frontend will be a last resort. 2. I don't want to pay money to a company for no good reason (i.e. M$) just because they want a regular income stream from me. 3. I want something that I can look at several years from now and understand it with a minimum of hassle. Something self documenting would be preferable, or at least something that is easy to document or is easy to get an overview of. (Since there is a possibility that one day I might inherit the business, I have the opposite need of most employees: rather than make myself indispensible I want to make the database I create something that any competent person can understand/extend in the future, be it me or someone else. 4. I want something secure; I don't want just anybody accessing this information. 5. I want something robust; I want something that I can get backups made of regularly and will only die if the IT department does a poor job of making sure the supply of power and quality of components is not up to par. 6. I want something scalable; I don't want to be switching software halfway down the track because I have 50 concurrent users instead of 3, or the database grows in the number of rows it has, and somehow a query does not want to ever finish executing because of this. (As a part of this, I will attempt to do as much work as possible on the server end rather than client end, e.g. using views to get the underlying queries the front end relies on.) 7. I want it to be easy to idiot proof it; I want to make it hard for the end user to break it. E.g. Referential integrity, cascading deletes, stuff the MS Access relationships window was able to do well. Even the lookup tables, where adding a category involved the user having to create a new category with a new autonumber instead of just typing a word (leading to multiple instances of the same thing). 8. I want to be able to create reports and forms quickly and easily, and be able to grok them easily in the future so that I can change them or at least identify where they are. 9. I want to be able to create the queries (or SQL) the reports are based on quickly and easily. I don't want to compromise on easy querying at the expense of complex queries (which I suspect that MS QBE was, though I could be wrong). 10. I want it to be easy to debug; I want to be able to logically identify where something is going wrong so that I can fix it when it breaks. I figure that half the answer is using Postgres as the database engine, and pushing as much business logic as possible onto it, as it should be much faster to compare different chunks of RAM than to send stuff back and forth on a network. I will continue with good normalization procedure as per Codd, in the same way as I did with ms access and table design. If I am wrong with any of this, please suggest an alternative, I doubt I will get anyone telling me to use something besides Postgres, this is after all a PG mailing list. :) Now for the other half... how to do the development work as per my requirements? What software would best satisfy my list of ten features? I'd also like to know the process involved... if there is a tradeoff between learning curve and approaching my ideal development platform as stated in items 1-10, I'd like to know it. From the polls I have seen at the postgres site, it seems that Jasper Reports are most popular for the reporting needs part of my equation. However, as with Postgres vs MySQL, judging things by number of votes needs to be done with qualifications. Nosense in blindly following the herd over a cliff. http://www.postgresql.org/community/survey.45 I would also like to know how best to map my process for development from ms access to Postgres + whatever you recommend.To assist you, my process for development in Access was roughly (from memory): 1. Figure out what tables I need and which columns are needed in the tables. Do this on paper. 2. Create the said tables on the access backend. 3. Open up the relationships window, view the tables I had just created and drag links across, then decide one to many etc, cascading deletes/updates etc. 4. Open up the tables again and do things like add indexes, constrain user input as appropriate in particular columns, etc, set up lookup tables etc. 5. Create some input forms, enter some test data, see if they work ok. If necessary, spend ages on the internet/ ms access help/ computer books looking for how to implement exactly what I want to do with VB. 6. Create queries needed by reports. See if test data works out ok and if it is my query or underlying table structure that is causing problems if it doesn't (usually it is the query, as I was pretty good at designing the tables). Debug the queries. If necessary, spend ages on the internet/ ms access help/ computer books looking for how to implement exactly what I want to do with VB. 7. Create the reports that go on top of the final query just debugged. 8. When a report breaks down, find the underlying query and keep going down until the previous query doesn't give an error, then fix the error. This was all done within MS Access (except the first step, which was on paper). I'd be curious how people do much the same thing while using Postgres and either Access or other aftermarket software. Even with the technical documentation of Postgres, the tutorial etc. I don't get a good feel for what the analogues of the various processes I did before are. I think it would help not only me but anyone else who is deciding to upgrade, as there are lots of Access holdovers in the same boat. Lastly, any books to recommend? I have printed out the latest online PG documentation plus ordering in "Beginning Databases with PostgresSQL: From Novice to Professional 2E", as that seems to get the best reviews on Amazon. So, in summary: 1. What software/languages to use for everything? (Even text editors, if necessary.) 2. What development process to use? How do each of my steps (and any additional ones you might think of) map from Accessto your solution? 3. If there is a learning curve/development speed (or something else) trade-off decision to be made depending on software/language choice, what is it? 4. Recommended books/resources? Thanks in advance, pgsqln00b p.s. Hope I haven't left anything out... ;) -- ___________________________________________________ Play 100s of games for FREE! http://games.mail.com/
pgsql-novice by date: