Search code examples
mysqllaraveldatabase-designmany-to-manypivot-table

Database schema many-to-many with defaults

I am hoping someone would be willing to take a look at this many-to-many relationship. This example is for a Laravel project, but the specifics shouldn't matter too much.

action

+----+------+--------+-------------+------+--------+------------+
| id | name | script | description | icon | custom | project_id |
+----+------+--------+-------------+------+--------+------------+

pipeline(action_server this is the pivot table)

+----+-----------+-----------+-------+
| id | action_id | server_id | order |
+----+-----------+-----------+-------+

server

+----+------+------------+------------+
| id | name | ip_address | project_id |
+----+------+------------+------------+

This many-to-many relationship is used for a deployment server an action is part of a deployment's pipeline.

  • An action can be executed on multiple servers.
  • A user can add an action with a custom script.
  • All the actions for a deployment pipeline can be fetched through a project_id

This concept works within Laravel and I could simply fetch my actions based on a given project_id. In turn I could fetch the server actions needed to run the deployment by using action->servers().

I need a way to add default actions though. Instead of actions always having a user supplied script, I want the ability to provide actions with pre-defined scripts for a user to select from and add to a deployment pipeline.

These pre-defined actions can't be stuffed in the action table because the actions defined there are tied into a project_id. These need to be generic.

I can't simply create another table for these pre-defined actions in my current setup because the action_id in my pipeline is already set up with a foreign key.

So far it feels like I am mixing 2 concepts, which are pre-defined actions and the user-defined actions which users have created themselves. They need to be in the same pipeline and eventually run in the right order though.

Any thoughts on how this might be achieved? I am open to all suggestions.

Edit

After drawing this out it seems a possible solution would be to add another pivot table in the form of action_project which allows me to decouple(remove) the project_id from the action table. I am wondering how to keep this clean in Laravel though.

action_project

+----+-----------+------------+
| id | action_id | project_id |
+----+-----------+------------+
Solution

  • Summarizing your problem in a conceptual way:

    1. applications ("projects") have associated custom actions,
    2. standard actions are not defined for a specific application
    3. servers have/host applications
    4. pipelines define which "actions" to perform on which server in which order

    I think what you need is simply a generalization of custom actions and standard actions, corresponding to a superclass "action" that subsumes both cases. This leads to the following tables:

    1. actions(id, type, name, description) with type being either custom or standard
    2. custom_actions(id, script, icon, custom, project_id)

    Alternatively, you could append the attributes of custom_actions to actions and have them all NULL for standard actions.