4

I have an app that needs to attach objects of different types between them. For example: I have to add Lead type object to a Contact type object , a Contact to an Event and so on.

Such relationship looks to me to be an undirected graph (If Car A is attached to Contact B has same meaning with Contact B is considered to be attached to Car A).

Car A edge Contact B means two things: - Car A is attached to Contact B - Contact B is attached to Car A

Those two meanings are not independent, they are the same "business", only a different point of view

Question: What is the best way of storing such relationships in a SQL database (MySQL - my case)

Sidenote: I use Eloquent ORM (Laravel/PHP) and using a pivot table (for many to many relation types) for each type of relationship partially works, as it does not work when you try to make relation between same objects: Contact <-> Contacts

1

It is possible to have a relationship between objects in the same table.

For example, I have a services table and services can have a BelongsToMany relationship with one or more other services. Like a Database service is a dependency for the CRM Service and the Finance Service but also requires the Storage and Network services.

Use a pivot table, such as the following:

Schema::create('service_dependencies', function (Blueprint $table) {
        $table->engine = 'InnoDB';

        $table->integer('service_id')->unsigned();
        $table->integer('dependency_id')->unsigned();
        $table->foreign('service_id')->references('id')->on('services');
        $table->foreign('dependency_id')->references('id')->on('services');
        $table->string('type');
        $table->primary(array('service_id', 'dependency_id', 'type'));
        $table->timestamps();
    });

You can see that both the service_id and dependency_id are actually the id on the services table.

The Service model looks like this:

public function get_supported_services() {
    return $this
        ->belongsToMany('App\Service', 'dependency_service', 'service_id', 'dependency_id')
        ->withPivot('type')
        ->wherePivot('type','supports')
        ->orderBy('service_short_name');
}

public function get_required_services ()
{
    return $this
        ->belongsToMany('App\Service', 'dependency_service', 'service_id', 'dependency_id')
        ->withPivot('type')
        ->wherePivot('type','requires')
        ->orderBy('service_short_name');
}
  • Looking at your solutions , it seems to me that you have an oriented graph : when you add a Service A to a Service B, there are two edges between these two nodes: get_supported_services, get_required_services – user237329 Sep 30 '16 at 14:32
  • That is because I need to manage the direction of the relationship. I'm not familiar with oriented graph, but is Service A is added as 'required' for Service B, it can't also have a second relationship that is Service B 'supports' Service A. So not sure if you would say that there are two edges between the two nodes. If direction wasn't important, it would only need a get_services. – kerrin Oct 1 '16 at 0:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.