PHP / Laravel - Unique Results for shared relationship

Question

I am trying to create a web application, where users can:

1. Upload documents
2. Upload emails (inbound emails)

The documents/emails will be uploaded to what I call a "Stream". So a stream can have many documents and many emails stored.

However, I am furthermore trying to do so my users can parse the content of the documents and emails, according to a set of parsing rules. The users can create fields that can have many parsing rules.

Example: User A uploads a new document to a stream called "My Documents and Emails". The user have defined below two document fields for this stream:

1. Order Reference
2. Tracking Number

Further, there are some email fields that has been defined as well, but will not be used in this case, as User A is currently uploading a new document:

1. From Name
2. Email Subject

All fields above will also have some parsing rules, that will parse the content (this is not shown in this example, as it is not relevant).

As you can see, the stream "My Documents and Emails" both have DocumentFields and EmailFields.

This is my current setup:

Stream.php

//A stream can have many document fields
public function documentfields()
{
    return $this->hasMany(DocumentField::class);
}
//A stream can have many email fields
public function emailfields()
{
    return $this->hasMany(EmailField::class);
}

Document.php (Same relationship are avialable in Email.php)

//A document belongs to a Stream.
public function stream()
{
   return $this->belongsTo(Stream::class);
}

//Get the document fields available for the specific stream.
public function fields()
{
   return $this->stream->documentfields();
}

Now my question is, how can I do so the fields are "bound" to the stream, but contain a unique value for each Document or Email that is uploaded?

Something like:

document_fields:

id | stream_id          | name             
1  | 1                  | Order Reference 
2  | 1                  | Tracking Number 

document_field_results

id | document_field_id  | document_id  | content
1  | 1                  | 10           | Lorem Ipsum Dolar Amet for document #10    
2  | 2                  | 10           | Some other Lorem Ipsum Dolar Amet for document #10
3  | 1                  | 55           | Lorem Ipsum Dolar Amet for document #55       
4  | 2                  | 55           | Some other Lorem Ipsum Dolar Amet for document #55      

(Same logic when uploading an email / inbound email)

So whenever a new document is uploaded to the stream (id.1), it will "inherit" the document fields above, but the content will be unique for each field (as each uploaded document/email will ultimately have different content).

What would the correct relationship setup look like in this case? And is this the correct way to go?

Answer 1

IMHO.

streams: id, name

file_categories: id, name. Values: document & email. This gives you the flexibility to add new kind of file uploads

uploads: id, file_category_id, stream_id ,name

upload_fields: id, stream_id, name

upload_upload_fields: id, upload_id, upload_field_id, content.

With this setup, you don't need to make models for Email and Document. Moreover, you can easily add new upload file category.

Btw, I've been programming for a while now, but just started to answering SO questions. I don't know how to make the database structure looks nice like in your questions. Could you give me a link to that format?

Implementation

I am not sure how the front end looks like. So I just go with my interpretation

Stream.php:

public function uploadFields(){
    return $this->hasMany(UploadField::class);
}

New file uploads

//First, get the uploadFields based on the stream_id
$uploadFields = Stream::with('uploadFields')->get();

//Second, insert the upload
$upload = new Upload(bla bla bla...); 
$upload->save();
//https://laravel.com/docs/5.8/eloquent#inserts
//Third, use the upload id to fill the upload_upload_fields, or if you prefer the result terms, that's okay
foreach($uploadFields as $field){
    UploadUploadField::insert(['upload_id' => $upload->id, 'upload_file_id' => $field->id, 'content' => 'The Lorem Ipsum, I guess this is from the request']);
}

I think that's how I am gonna do it. It will be a little bit different especially for the content since it highly depends on the request structure.