The Phalcon PHP Framework
-
|
In my screenshot is a table structure and relationships I've laid out within my database to make a virtual mailbox. This is necessary for my medical app so we can provide nearly the same functionality like GMail, but without data ever leaving the app to an external client. A brief scope of the tables and their purpose:
The relationship is as follows:mail_thread When a user opens their mailbox, I want the following to occur:The database is queried for any rows within mail_thread and only returns any results where mail_thread has a relational mail_thread_recipients record with their user ID. For each mail_thread record found, all related mail_message records will be gathered where mail_message has a relational mail_message_recipients record with their user ID. For each message found, all related mail_message_attachments records will be gathered. So with that scope out of the way, my questions are as follows:1.) Is this type of structure acceptable or is there a more efficient way of storing/fetching these records with the intended functionality provided? I've never stored data to this level of relational complexity with Phalcon, so I'm a bit lost on how to execute this properly. Any help would be greatly appreciated! |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 4 replies
-
|
My 2 cents The structure is very nicely done, normalized. More on improving this below. To get the data as efficiently as possible you will need to load the minimal amount of data that you want to display per action i.e. user clicks Inbox -> need to show all emails and who it was sent to. This way you can query only the mail thread and recipients and display that information. When a user clicks on a mail thread, you can further go to display each message and whether it has attachments or not. You get the point. You minimize queries based on the amount of information you need to display. Some minor reorganization might help you there also, for instance storing the number of attachments per message in the message table. This way you don't need to do a I would go with the query builder directly, to avoid n+1, unless this is the message display. Message display: You just query one message record. Using that, you can use Non message display (say inbox): Use the query builder to retrieve your data. If necessary, query first to get the IDs of the satellite tables that relate with your records, then use Finally, once this grows to the point that queries become slower, you might want to invest in a text search engine (Elasticsearch for instance) which will store all your data denormalized. The index of each row in Elasticsearch will be updated with events such as There was an app we had many moons ago, which displayed a list of records. We had 17 joins in that model. Type as you go search was 5 seconds per character. Using Elasticsearch it was reduced to 0.1 |
Beta Was this translation helpful? Give feedback.
-
|
Wow, thanks @niden ! That was way more valuable of an answer than just 2 cents! I know this reply will be long, but I want to clarify with you one final time if you have a few moments to make sure I'm on the right track. I really appreciate you taking the time to help! First, I'm glad that my database structure works out. This is not too dissimilar from how I've set up the rest of the 90+ tables in the app, so it's definitely reassuring that I'm on the right track. I have attached an updated structure based on your input as well as some realizations that I had. Here is my reasoning if you wouldn't mind taking a look and see if this looks good to you:
mail_thread
mail_message
mail_message_participants
As for the views themselves, this is what I'm thinking: Inbox ViewRun a single query that grabs the last 10 records in Based on this query needing to join three tables to the initial Thread ViewI would need to essentially do two queries on this page from what I'm seeing: the first would be to ensure that the user is in fact included in one of the records of Thanks again for your help and also thanks for recommending ElasticSearch! That's already been added on our list of to-dos once we start saturating the database with a high volume of records. |
Beta Was this translation helpful? Give feedback.
-
|
Comments below:
This looks just fine!
For admin stuff, it is not uncommon to duplicate the information contained in your main tables, so as not to burden such tables with admin operations. For instance, you will need to know how much space the user uses so that you can bill them if they go over. That number does not necessarily need to live only in the
This is a tough one. You will have degradation here no matter what you do. Scanning the relationship to see who read what is going to be costly. I would go with the approach of 2 additional tables. One will contain
The query should run once, store the data in a cache (say Redis) and keep it there until a new message arrives OR a change happens i.e. user deletes a message, reads a message etc. from that particular view. This way you are not hitting the DB every 10 seconds.
Yes multiple joins are possible and a lot easier when you use aliases. I would use the builder because it is very verbose in your code.
Check my idea above for the additional table. If you have not done so already, make sure that you utilize a cache mechanism as much as possible. This will be used at least for now until you get ES going later on. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks again @niden all solid advice! We're gonna incorporate the I'll start planning out those suggestions you made regarding the Thanks again for being such a huge help. We've been running on a shoestring budget and I'm the sole developer for what is way too big of an app for one person, so being able to get some guidance, especially from someone as busy as you, is something I'm eternally grateful for. |
Beta Was this translation helpful? Give feedback.
-
|
Happy to help. All of the above have been issues that I have encountered so it was easy to offer pointers. One thing that bit me in the past was cache invalidation so be careful on that. When your app pings the server (say for unread messages) it should do the db hit, store in the cache and send the numbers back. The next ajax call would be from the cache. On a separate process of receiving an email, when the totals are updated, that initial cache should be invalidated so that the next ajax call would hit the db. You could also just update the cache instead of clearing it to avoid that db hit every X seconds or so. One other thing that comes to mind is work with your payloads i.e. what is coming back to the front end from say ajax calls. What you could do is check payload interop (https://github.com/payload-interop/payload-interop). This is for the Action Domain Responder pattern or even Domain Driven Design. You could use it as your response vehicle so that your views (at least on the ajax/js side) receive a consistent object to work with. Another implementation could be a simple <?php
namespace App\Http;
use function is_array;
class Payload
{
public const CODE_SUCCESS = 2000;
public const CODE_ERROR = 3000;
public const CODE_INCORRECT_PAYLOAD = 3004;
public const CODE_NOT_FOUND = 3404;
public const STATUS_SUCCESS = 'success';
public const STATUS_ERROR = 'error';
private $message = '';
private $code = 2000;
private $status = 'success';
private $payload = [];
/**
* Gets the payload code
*
* @return int
*/
public function getCode(): int
{
return $this->code;
}
/**
* Gets the payload message
*
* @return string
*/
public function getMessage(): string
{
return $this->message;
}
/**
* Gets the payload
*
* @return array
*/
public function getPayload(): array
{
return $this->payload;
}
/**
* Gets the status
*
* @return string
*/
public function getStatus(): string
{
return $this->status;
}
/**
* Initializes the internal variables (for reusability)
*
* @return $this
*/
public function init(): Payload
{
$this->message = '';
$this->code = 2000;
$this->status = 'success';
$this->actions = [];
$this->payload = [];
return $this;
}
/**
* Returns the formatted payload
*
* @return array<string,integer|string|array>
*/
public function render()
{
return [
'code' => $this->getCode(),
'status' => $this->getStatus(),
'message' => $this->getMessage(),
'actions' => $this->getActions(),
'payload' => $this->getPayload(),
];
}
/**
* Sets the code
*
* @param int $code
*
* @return $this
*/
public function setCode(int $code): Payload
{
$this->code = $code;
return $this;
}
/**
* Sets an error message, status and code
*
* @param string $message
*
* @return $this
*/
public function setError(string $message): Payload
{
$this->code = self::CODE_ERROR;
$this->status = self::STATUS_ERROR;
$this->message = $message;
return $this;
}
/**
* Sets the message
*
* @param string $message
*
* @return $this
*/
public function setMessage(string $message): Payload
{
$this->message = $message;
return $this;
}
/**
* Sets the status
*
* @param string $status
*
* @return $this
*/
public function setStatus($status = self::STATUS_SUCCESS): Payload
{
$this->status = $status;
return $this;
}
/**
* Sets a success message, status and code
*
* @param string $message
*
* @return $this
*/
public function setSuccess(string $message): Payload
{
$this->code = self::CODE_SUCCESS;
$this->status = self::STATUS_SUCCESS;
$this->message = $message;
return $this;
}
/**
* Sets the payload
*
* @param array|mixed $payload
*
* @return $this
*/
public function setPayload($payload): Payload
{
$this->payload = (true === is_array($payload)) ?
$payload :
[(string) $payload];
return $this;
}
}Returning: return $this->payload->render();will give you the JSON array you need. (note you will have to set that in your base controller i.e. Then your JS will have everything it needs i.e. the status, payload etc. You can also map those same status codes in your global JS code and once your ajax call returns, a function will decide what to do with the data (if error for instance). The above class can easily be extended to whatever you need to send back and forth. |
Beta Was this translation helpful? Give feedback.
My 2 cents
The structure is very nicely done, normalized. More on improving this below.
To get the data as efficiently as possible you will need to load the minimal amount of data that you want to display per action i.e. user clicks Inbox -> need to show all emails and who it was sent to.
This way you can query only the mail thread and recipients and display that information. When a user clicks on a mail thread, you can further go to display each message and whether it has attachments or not. You get the point. You minimize queries based on the amount of information you need to display. Some minor reorganization might help you there also, for instance storing the number of attachments per…