#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0

    PostgreSQL Environment for Small Business


    Hi All,

    We are looking to setup a database in a small business. The principle aim being to achieve better information sharing and access by having a central data source that is updated in a single location so we don't end up with conflicting information in different locations. It will mainly be storing customer and supplier details, and relating those to orders and services provided. We are limited on money and expertise in this area. I've spent some nights researching and decided to pursue a PostgreSQL database for this purpose and I'm hoping you can give me some advice on its hosting and setup. The database won't see heavy use (tops 20 simultaneous users, approximately 5 simultaneous users initially), but it does need to be reliable.

    Below are my questions, but; in answering please consider that our lack of technical knowledge (we can't afford to higher a DBA or an IT guru to help at the moment, going it alone is the only choice). As such, user friendliness, up to date and complete software documentation, and a large pool of experienced users that can be accessed for specific questions will be critical to us. We are looking for reliability and ease of implementation/use/maintenance over performance (heresy, I know )

    Questions:

    Can you suggest a stack construction to support the database, both in terms of its development and then user access to it (i.e. the front end)?

    Which OS would you suggest running in a virtual server to host the PostgreSQL database? (Linux or FreeBSD, which particular distro/version?)

    Which web server? (Apache?)

    Which programming language (PHP?), and can we avoid this entirely/mostly? (see next question.)

    What tools might you suggest to aide someone like myself who has little technical expertise in getting this thing going?
    I'm thinking here of some tools (or complementary sets of them) such as:
    DB Design:
    DbSchema / DbVisualizer / SQL Power Architect
    DB Admin:
    PhpPgAdmin / PGAdmin
    Front End:
    DaDaBIK / MS Access (everyone already has it anyway)
    General Tool Suites:
    Aqua Data Studio / PostgreSQL Maestro / Navicat

    I know the basics of SQL, Javascript and some other unrelated things like VBA, but that's about it. As much as I would love to learn the detail myself like learning PHP properly and SQL in depth, the timeframes and constraints on our time do not allow it. I'll consider paying a few hundred for commercial software if it would significantly streamline the process of doing the DB design, implementation, management and front end development when compared to the time required to setup and learn the intricacies/nuances of free alternatives (conversely, perhaps the free alternatives are just as good as the commercial options in this respect?)

    It should also be clear that coding a front end from scratch, even a simple, one using JavaScript and PHP would be out of my reach at this stage. I would value any insights people might have about this process, and thanks for spending your time in reading this and replying!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by Simple_One
    DB Design:
    DbSchema / DbVisualizer / SQL Power Architect
    Development on Power Architect seems to have stopped as far as I can tell. From the list above I can recommend DbSchema. It is really a nice tool and the money is well spent.

    You should also look into a schema management tool such as Liquibase or Flyway to roll out schema changes.

    You have to put those scripts under revision control just like your application code. Popular choices for that are either Subversion or Git.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Thanks, I'll look into those a bit further.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    We are looking for reliability and ease of implementation/use/maintenance over performance (heresy, I know )
    Reliability is always more important than performance. MySQL can store dat very quickly but there is no guarantee that the data is actially stored, let alone stored correctly. PostgreSQL may sometimes take a little longer, but it's very difficult to get it to mess up.

    Which OS would you suggest running in a virtual server to host the PostgreSQL database? (Linux or FreeBSD, which particular distro/version?)
    Personally I think running a database on a virtual server is a mistake to begin with, but it your workload is low enough you can probably get away with it.

    FreeBSD is probably technically the better choice, but Ubuntu/Xubuntu is much more userfriendly which is good if you're new to it.

    Which web server? (Apache?)
    Apache of Nginx, both have a parge usergroup and once you've set it up you won't have to deal with it anymore, so look into both and see how you feel.

    Which programming language (PHP?), and can we avoid this entirely/mostly? (see next question.)
    How much of it you can avoid depends on what exactly you want to do.

    As for tools:

    design: dbschema
    maintenance: pgadminIII/Navicat/SQLManager (EMS)
    frontend: whatever floats your boat. I'd never even consider MS-Access but if that can do what you need, go for it.

    (*NEVER* use an PHP-based management tool except if you absolutly must and you don't have to use transactions)


    I'm thinking here of some tools (or complementary sets of them) such as:
    DB Design:
    DbSchema
    DB Admin:
    PhpPgAdmin / PGAdmin
    Front End:
    DaDaBIK / MS Access (everyone already has it anyway)
    General Tool Suites:
    Aqua Data Studio / PostgreSQL Maestro / Navicat


    conversely, perhaps the free alternatives are just as good as the commercial options in this respect?
    It differs greatly from tool to tool, and of course it depends very much on your personal preferences.

    It should also be clear that coding a front end from scratch, even a simple, one using JavaScript and PHP would be out of my reach at this stage. I would value any insights people might have about this process, and thanks for spending your time in reading this and replying!
    I think you need to explain a bit more about what you actually need, how do you plan to distribute this data, as a raw PgSQl connection, or XML or JSON or what?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by Vinny42
    I think you need to explain a bit more about what you actually need, how do you plan to distribute this data, as a raw PgSQl connection, or XML or JSON or what?
    Okay, you have lost me a bit with that question, I don't follow what you mean by distributing the data, distributing what from where to who?

    This is probably not the clarification you're seeking, but the data will be hosted centrally, that is, on a server that is remote from all users, who will be accessing it via private IP WAN links. Users can connect to the server either via RDP, or as a mapped network drive.

    My originally scheme was to distribute a MS Access file to users that held the front end information (i.e. a bunch of premade forms, queries and reports), and they could then just connect that to the DB using the ODBC driver to create linked tables.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    This is probably not the clarification you're seeking,
    Probably, but the more you explain about your situation, the better the answers are going to be.

    You want central data storage, so that all your remote sites work with the same uptodate data. This means that the remote sites must run some applications (even if it's just an access form) and that application must get it's data from the central server somehow.

    A database service can only be accessesed by a either a TCP/IP socket or ODBC, so a network drive can't access the data, and an RDP would have to have the actual application running on the central server.


    My originally scheme was to distribute a MS Access file to users that held the front end information (i.e. a bunch of premade forms, queries and reports), and they could then just connect that to the DB using the ODBC driver to create linked tables.
    That sounds like a feasable setup; let the remote sites run the application and make them talk to a central database server. That way, if one user locks a record the other users will be refused access untill the user has finished the work, which is exactly what you want.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Okay, no problem. I wasn't sure if I was just wasting your time with that information, good to here it was relevant

    On with yet more questions unfortunately, is it generally advisable to run the front end locally, or would I better off (since we have the choice of) getting users to connect to a front end that runs on the server itself (i.e. using the front end within their RDP session)?

    I can see pro's and con's to both approaches, I'm curious if there is one factor that might swing it one way or the other for experienced DBA's and dev's such as yourselves?

    Obviously poorly written queries that are asked to return massive joined tables are bad at the best of times, and would be horrible down WAN links, but equally, I find working within an RDP session to be tedious due to continuous latency issues compared to working on your own desktop. So, is there something more fundamental that would make that decision for you (and hence, probably for me)?
    For instance, maybe record locking works much better with the front end hosted locally, or perhaps you use less network traffic by just sending well written queries over the network than an RDP session uses, so if the user is working over a poor link or IP tunnel, perhaps a local front end would be a much better option...etc?

    The other issue is how to actually make the most of Access as a front end, with a good RDBMS like PostgreSQL as the backend. What coding practices would I need to employ to make the most of such a configuration?

    To elaborate, what techniques would enable me to use the database engine to manage as much of the user access control, security and complex query calculation as possible; whilst still being able to employ Access' simple interface design tools and use its strong integration with the rest of the MS office suite?
    I imagine that it would boil down to:
    1. Use pass-through queries
    2. Use strong criteria on those queries to minimise the size of returned results
    3. Should I use stored procedures within the DB backend and call them from the front end instead, what difference is there really?
    4. Do I need the backend to create materialised views, or conventional views, and do I even have the option of using both types of view if I use an Access front end? (can I make the option user selectable in some way?)
    5. Can you use 'bound' items (forms, controls etc) with a PostgreSQL backend, or should I focus on learning to implement these things by 'unbound' means?
    6. Do I need to do more complex things to make Access work properly, such as copy tables locally to Access (I hope not) in order to make it's integrated tools work correctly, or does it still function largely as advertised with a different RDBMS as it's backend instead of Jet?


    Any thoughts you have on how to go about producing the best outcomes with an Access/PostgreSQL setup would be much appreciated.

    Thanks for the help so far as well
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    is it generally advisable to run the front end locally, or would I better off (since we have the choice of) getting users to connect to a front end that runs on the server itself (i.e. using the front end within their RDP session)?
    It depends entirely on how you are going to access the data. For the past few years I've been developing webbased backoffice applications, which basically means that each user has a very simple application running in the browser and all the actual work is done by the server. Only the result of the work is sent to the user and the most data that could be sent was one pagefull of records.
    If you can get MS-Access to do the same, you're ok.


    Obviously poorly written queries that are asked to return massive joined tables are bad at the best of times, and would be horrible down WAN links
    My primary concern is that the application shouldn't download all the data to do work at the user's end, that would simply take too long and cause race conditions.

    but equally, I find working within an RDP session to be tedious due to continuous latency issues compared to working on your own desktop. So, is there something more fundamental that would make that decision for you (and hence, probably for me)?
    I think your biggest problem would be finding a server that can handle the number of desktops that you need. Effectively that single server will do the work of a lot of desktops, it's going to have to a e pretty beefy server.

    For instance, maybe record locking works much better with the front end hosted locally
    The only important thing here is that all running applications always access the central data storage, and not some cache they have stored locally. Where the application is running doesn't matter for locking.

    1. Use pass-through queries
    2. Use strong criteria on those queries to minimise the size of returned results
    3. Should I use stored procedures within the DB backend and call them from the front end instead, what difference is there really?
    4. Do I need the backend to create materialised views, or conventional views, and do I even have the option of using both types of view if I use an Access front end? (can I make the option user selectable in some way?)
    5. Can you use 'bound' items (forms, controls etc) with a PostgreSQL backend, or should I focus on learning to implement these things by 'unbound' means?
    6. Do I need to do more complex things to make Access work properly, such as copy tables locally to Access (I hope not) in order to make it's integrated tools work correctly, or does it still function largely as advertised with a different RDBMS as it's backend instead of Jet?
    1. Yes.
    2. Yes. Only fetch what the user absolutely needs, and nothing else. Over the past years I've spent quite a lot of time talking to endusers about what they need to do their jobs. A manager will tell you that his employees need access to all the data, and the user will tell you that 90% of that data is never used.
    3. Yes. Well, you don't absolutely have to, but you'd be a fool not to. :-) In your case, a stored procedure can loop through the kinds of large resultsets that you want to avoid sending over the network. But much more importantly; stored procedures are defined in the database so they are the same for every user, even if the user is running a sligntly outdated version of the MS-Access application. If you find a bug in the access stuff you have to notify all users to upgrade immediately. If you find a bug in the stored procedures, you just fix it and the users will never know.
    An aditional bonus of stored functions is that they can be defined by a user who has more rights than the user who calls the procedure, so you can deny access to certain tables and only let users use the procedures to enter/modify it's data.
    4. Again, you don't have to but... view control what the users can see, so you can hide data that they don't need or shouldn't be able to see. If the view is slow you might consider a materialized view, but then you have the added problem of keeping that uptodate. I'd start with regular views and see if that is acceptable.
    5. Tables rarely (read: never) represent complete business objects so I have never had a need for a control that accesses just one table (let alone directly) Of course it depends on your application but personally I always have a layer of logic that transforms the table data to a form and back again, allowing for stuff like validation on a much more usefull level.
    6. Honestly, I have no idea. I try to stay far away from all MS products, especially Access :-)
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    Excellent information, that's more than enough to get me started down the correct path hopefully, thanks

    I've got a table structure setup with the keys and relationships established, I'll create a few fields for extra attributes and populate all of it with dummy data and start working with it via Access. It'll tell me quickly enough what sort of things happen and if/how that affects the ability of Access to integrate with other things like Excel and Outlook.

    With luck I can avoid, or at least delay, long nights of learning PHP and Javascript/RoR/Python to make a web front end.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    11
    Good luck!

    Oh, and make sure your dummydata is sensible, ie: like the real thing. If you fill your table with series of numbers; 0,1,2,3,4 the database will come up with very different queryplans and performance will be very different from what your actual data will be like.
    And make sure you add much, much more data than you except to get in the next year or so. Small tables are fast no matter how you use them, large tables (which you will get in time) show you exactly where the problems are.

IMN logo majestic logo threadwatch logo seochat tools logo