Jump to content

Database choices for PHP applications


NotionCommotion
 Share

Recommended Posts

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.

 

 

Link to comment
Share on other sites

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        |
| .  | .        | .        |
| .  | .        | .        |
|----|----------|----------|

 

Link to comment
Share on other sites

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 by kicken
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

punchcard.jpg.cb49dcf1d18964d96ebd41431f4ef66e.jpg

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

Edited by Barand
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.