best practice - linking UI and database

Databases
1

Dear Community
I’m planning a new Project and would like some input on how to set up an efficient database concept. What choices need to be made before starting?
So far, I have found ActiveRecords that BKeeney has tools for. Then there is Valentina’s Studio that helps build SQL commands. I read of optional use of an API layer. I’m sure there more approaches to make life easy and efficient and surely they all have pros and cons. I’d like to get an overview on how things could be done.
The Project:
Eyewear shop, measuring Eyes. So track clients needs, prescription measurements, Glasses consisting of frames and lenses with their characteristics, contact lens fittings. These sum up in solutions with articles and services with their selling prices. These solutions follow a process. The main goal: swift gathering of the data, see where we are in the process (with alerts). Sum up statistics of sold product categories.
Desktop, Windows, Network with approx. 5 clients.
Criteria:

  • Swift to link up UI with DB
  • Swift building the query part
  • Ease of future development
  • Reliability & stability, stuff not crashing or data getting muddled up. It will not be running 24/24. But it shouldn’t need restarts or such
    I intend to define the data-model and setting up the UI, then finding a developer to link up the two for a first version containing the essential. After this, it is important to me, that I can take it from there to put in further features and functions. So the setup should be traceable for a non professional power-user.
    So, I’ll be curious for opinions and suggestions?
    Chris
2

I tried several databases and would strongly recommend PostgreSQL. Valentina Studio is my preferred tool to manage databases. You can create SQL statements and check the results in VS. Once the query returns the expected results simply paste the code into your app.

Several pros to PostgreSQL

  1. Powerful, scale-able database server. Can easily handle many users.
  2. Very easy to install and maintain. (I can install and setup PostgreSQL in 5 minutes)
  3. Triggers are very useful for notifications. Example: I can set up a trigger to notify me if a record is inserted updated and the UserName fields = MyUser.
  4. Free with no strings attached.

Cons:

  1. ??? I don’t know of any.
3

I use and love like PostgreSQL and there are even more Pro’s, but

Cons:
• field names are case sensitive
• text searches are case-sensitive
• PgAdmin is not a friendly as MySQL products, but it’s getting better
• Searching large databases with millions of records can take a long time (tens of minutes) or never end
• if a record gets corrupted there is no way to repair the database
• installation differs from Ubuntu to CentOS to …

4

Valentina is a good alternative:

Pros:

  • clear license
  • easy to install
  • good price
  • no case sensitive names or text searches (yuck!!!)
  • with a bit of tuning the SQL is fast
  • the journal file helps with most situations of corruption
  • the support is fast and good

Cons:

  • room for improvement with documentation and examples
  • no full text search
  • throwing GBs of data at the database at once easily leads to varchar corruption or segment faults.
5

Hi Chris.

Check out Xanadu Web App. The version we demoed at XDC 2016 is downloadable with open source for free. Since then we’ve made a bunch of updates which you can get for a fee.

Xanadu works a bit like FileMaker where you can simply add subclassed controls to the page once the table has been defined without writing any extra SQL. Xanadu used introspection to look at the controls for the table to automatically define the SQL statement.

6

I don’t want to turn this thread into a pro-con PG discussion, but…

You probably got this impression because you used PGAdmin to create the fields. If you create a field in PGAdmin and type MyCoolIntegerField in the “new field” dialog, PGAdmin creates this SQL for you: Alter table myTable add column “MyCoolIntegerField” integer (which you can see if you click on the SQL tab). The quotes around MyCoolIntegerField basically just tell the server to please honor your capitalization - which may or may not want you want. I think Paul recently wrote a blog post about it, but imho the way PG handles that is the best way possible.

? SELECT subject FROM topics WHERE subject ~* ‘eclipse’ is one way of doing it; Need an index on lower(myField)? No problem.

Well, it has its limits, but the chances of the OP who is obviously new to all of this hitting those is not overly high.

Yeah, but only if you don’t know how to create indexes and use EXPLAIN ANALYZE.

In my 16 years of heavy PG usage I have not managed to corrupt a record yet, but that’s what backups are for. Modern versions of PG have no known bugs in that area, so bad hardware is usually the culprit. Use good hardware.

Well, yeah, your data directory is a different one, but that’s no big deal just look at your postgresql.conf file or issue SHOW ALL.

7

Maximilian, please, could you briefly explain me how to use “EXPLAIN ANALYZE” and what it does ?

Thanks

8

Gladly, the first step of becoming familiar with all aspects of PG is being able to track down stuff in the excellent PG documentation. https://www.postgresql.org/docs/9.6/static/using-explain.html

9

[quote=329073:@David Cox]
• text searches are case-sensitive[/quote]
That’s the SQL standard. Use UPPER() or LOWER() if you want all results irrespective of case.

10

Thanks allot to all. This takes me a step further.

Thanks Hal. I’ll be looking into xanadu.

Chris