Database Structure

I don't have enough experience on this topic to say anything on best practices, however I know that you can query your keywords relationship in the following way:

// Retrieve all projects containing 'bar' or 'baz' keywords:

$projects = App\Project::with('keywords')->whereHas('keywords', function ($query) {
    $query->where('body', 'like', '%bar%')->orWhere('body', 'like', '%baz%');
})->get();

In this case 'body' is a column of your keywords table containing the keyword string.

You can chain the ->whereHas() clause with another whereHas() to further built up the eloquent query and filter down the results. You can decide for yourself if you'd like to eager load the 'keywords' relationship using the with() method like I did above.

Hi John,

Thanks for your reply thats great and good to know.

I suppose from a search perspective I have a keywords relationship table but from the example above if each area ie Service/Sector/Areas where each project could have more than one of each would you also create a table to house each of these?

Or would you run this similar to how Wordpress uses Terms/Taxonomies so you can hold multiple category/values in 1-2 tables. I suppose this is where I am lacking the guidance.

Thanks again for your reply much appreciated.

Cheers J

@JAMIEPS - I am not sure what the best way would be to go about this.

Maybe @snapey or @bobbybouwmann have something to say on this?

Well the answer is always it depends. The question you must ask yourself is if the keywords should be reusable or not.

If they should be shared between projects or you maybe want the user to be able to select from the already existing lists of keywords. In this case a many-to-many relationship would be the best approach. That means 3 tables: projects, keywords, keyword_project

If it's not shared a one-to-many relationship would do fine as well. However you might even use a many-to-many relationship here. This way you're free to make it shareable in the future as well ;)

Does this answer your question?

Hi

One piece of advice - don't get too wedded to your design - it can always be changed later.

I'll refer you to one of my Laravel projects https://speakernet.co.uk - the source directory for this 'simple' site has all the current code in a folder called v6. This reflects the major re-builds I did BEFORE releasing the first site. So, the site underwent a lot of change before settling on an architecture.

You can see there that I tackled a lot of the issues you are mentioning here. If there is anything you want to ask, about the way it's structured I'm only too pleased to help.

For your keywords, I would implement a tagging package. This will take care a lot of this for you quite easily. I used https://github.com/rtconner/laravel-tagging

Morning Guys,

Thanks ever so much for your feedback it is extremely helpful.

@bobbybouwmann Yes the Keywords side I have a one to many as it is just strings so all projects could have any number of different words applicable to that project only.

I suppose it was the next areas I mention Service/Sector/Areas these would be pre populated checkboxes almost like they are there own categories. Similar to how Wordpress would work with Taxonomies Services/Sector/Areas would be there own taxonomy and would hold 10 terms each so in the backend the user could check which terms apply to each project. In this case as you mention a many to many. I just wasn't sure if creating a table with a pivot table on a many to many for each taxonomy was the right way to go. Seems like a lot of tables to create just to run these. Basically am I working smart enough or is this the right thing to do, this is where experience is probably lacking.

@snapey Ah this is great I will take a look I think this is what I need to see real world builds using complex functionality. From a tagging perspective I have this running as I would like with a one to many it was more the additional areas as mentioned above. As looking at it I would need 3 tables with additional pivot tables so I can then use a search to look through these to gather all related project IDs on the frontend. I am unsure if I am overthinking or not working smart enough.

The best example as mentioned above is similar to the Wordpress setup I have used in the past numerous Taxonomies which hold numerous Terms where the terms are related to the projects but each term could belong to numerous projects if that makes sense. I am aware there is a WP taxonomies package for this but also am I looking at it like this because of my past developments with Wordpress and limiting myself to a better way of approaching this with Laravel.

Again appreciate all your guys help I am just interested in how other PHP users would approach this and the smartest way to tackle this.

It sounds to me that you need to setup multiple many-to-many relationships to make this work for you. If you have any doubts just ask ;)

Hi Guys,

Thanks for your feedback as mentioned it is appreciated more than you can realise.

I think polymorphic maybe the way to go so the main category would be the ID and Type and I can morph in server models ie Sector/Service/Area and the polymorphic table would hold the term names.

I think I would achieve what I need and also only require 1 table per instance.

I am sure I will be back with more questions in other areas but great to have a group of experienced developers to help me plan moving forward.

Many Thanks Jamie

@jamieps I do have one advice about this though! If you have a huge volume of data the polymorphic relationship might not be the best way to go. The reason for this is that you can't index the table because you don't know to what table the id will be pointing. If you split it up in multiple tables you can index them correctly. However you need to write more logic for that.

You won't feel much difference with 100k records or so, but over 1m you might notice this more!

@bobbybouwmann Thanks for this much appreciated.

I am going to run with the manytomany but to a terms table which will be assigned a taxonomy group label so I can group them as collections through the model.

I only anticipate this to grow to 500 terms in total so this will work fine. As you kindly pointed out and thanks for doing so if it were a huge table with lots of rows this would need a refactor and split into other tables should I come up against bigger tasks so thanks for this pointer it will certainly make me look at all these avenues moving forward.

Thanks for your help guys.