Database choices for PHP applications

[NotionCommotion]

A while back, I started using Doctrine and feel it brings many benefits, however, I experience many issues when the database schema contains unique indexes.  I have since learned that deferrable constraints exist for some DB's and their use will eliminate my issues.  According to MySQL's documentation the SQL standard is to make them the default, however, MySQL does not do so (and apparently neither does MariaDB which I typically use).

I am considering changing databases.  Any recommendations?  I've always heard good things about postgresql.  Thoughts?  Much of a learning curve to change?

Thanks
 

Quote

 

Per https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html:

In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.

 

 

[requinix]

Back up. What issues with unique indexes?

[NotionCommotion]

13 hours ago, requinix said:

Back up. What issues with unique indexes?

I've got the following table and data and wish to swap the position value for two records, delete and reorder the positions, or insert and reorder the positions.  Currently, I perform the first task by reserving position zero as a temporary placeholder and perform the other two tasks by executing something like "UPDATE series SET position=position-1 WHERE chart_id=3 AND position > 2".  It would be simpler to update the entity values and save them, however, I often encounter an interim unique constraint error.

CREATE TABLE IF NOT EXISTS series (
  id INT(11) NOT NULL AUTO_INCREMENT,
  chart_id INT(11) NOT NULL,
  position INT(11) NOT NULL
  PRIMARY KEY (id),
  UNIQUE INDEX unique_position (chart_id ASC, position ASC) VISIBLE,
  UNIQUE INDEX unique_name (chart_id ASC, name ASC) VISIBLE,
  INDEX IDX_582B2D4DBEF83E0A (chart_id ASC) VISIBLE,
  CONSTRAINT FK_582B2D4DBEF83E0A
    FOREIGN KEY (chart_id)
    REFERENCES chart (id)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

|----|----------|----------|
| id | position | chart_id |
|----|----------|----------|
| .  | .        | .        |
| .  | .        | .        |
| 3  | 6        | 2        |
| 6  | 1        | 3        |
| 1  | 2        | 3        |
| 4  | 3        | 3        |
| 5  | 4        | 3        |
| 2  | 1        | 4        |
| .  | .        | .        |
| .  | .        | .        |
|----|----------|----------|

 

[kicken]

If it's a problem, I'd probably just drop the constraint.  If it's important, handle it in your code otherwise don't worry about it.

I'm not sure what a different database might provide as solutions to your issue.  Only other one I really use is SQL Server and haven't looked into anything like this.  The closest situation I have is with a DisplayOrder column which is typically set to a unique 1..n range value by the application.  Having a duplicate isn't really a problem though so I don't bother enforcing that with a constraint or code.

 

Edited February 23, 2020 by kicken

[NotionCommotion]

4 minutes ago, kicken said:

If it's a problem, I'd probably just drop the constraint.  If it's important, handle it in your code otherwise don't worry about it.

I'm not sure what a different database might provide as solutions to your issue.  Only other one I really use is SQL Server and haven't looked into anything like this.  The closest situation I have is with a DisplayOrder column which is typically set to a unique 1..n range value by the application.  Having a duplicate isn't really a problem though so I don't bother enforcing that with a constraint or code.

 

My "position" column is identical to your DisplayOrder column.  Agree it is not mission critical and certainly need not be enforced by the DB and not even the application, and will drop it before stressing over it.  I believe that any database which implements the standard for unique constraints which I believe is deferrable constraints will automatically eliminate my issue.

[NotionCommotion]

Regardless of whether deferrable constraints are used, does anyone have any feedback on using PostgreSQL with PHP and how it compares to MySQL and/or MariaDB?

[requinix]

+1 to dropping the unique on the ordering. It doesn't have to be unique. Order by it, then by something else (like the ID). It's okay.

I prefer PostgreSQL over MySQL and friends. More features. Perhaps better performance.

[Barand]

If you are going to have a column for sequencing, take a tip from someone who used to program on paper then have their lines of code transferred to punch cards

- sequence in increments of, say, 100 and not 1. That leaves lots of room to alter the sequence or add inserts.

Edited February 25, 2020 by Barand

[gizmola]

In my opinion, your issue is easily solved by allowing null on your sequence column.  Null is also theoretically correct, in that null indicates the absence of value.  MySQL will allow multiple columns to be null on a uniquely constrained column or index.

[chhorn]

19 hours ago, NotionCommotion said:

does anyone have any feedback on using PostgreSQL with PHP

coming from MySQL in the past, i'm working with PHP+Postgres nearly exclusive for the last 5 years or so. main reason is standards compatibility, so less surprises and less wasted time with debugging, but performance and built in features are also a cause, GIS capability is great.

[Barand]

As an aside, is there a good administrative front-end for PostgreSQL (MySQL Workbench equivalent - modelling, admin, editing etc)?

Edited February 26, 2020 by Barand