1

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 |
+----+-----------+------------+
Stephan-v
  • 19,255
  • 31
  • 115
  • 201
  • 2
    Can't you make `action.project_id` nullable? – Jonas Staudenmeir Sep 10 '18 at 23:30
  • 1
    I don't really understand why you have `project_id` in both tables, it feels counterintuitive. Does a pipeline-entry for an action_id and a server_id that belong to two different project_ids make sense? If not, this might be the underlying root of your problem. Maybe the project_id belongs to pipelines (e.g. a project consists of several pipeline-rows or something like that), and not to the action? – Solarflare Sep 10 '18 at 23:48
  • @Solarflare the `project_id` belongs to the `server` table as well because I don't always work with a `pipeline` and `action` sometimes I just want to grab my servers directly from a `project_id`. A deployment pipeline may not even be defined yet when a `server` is created. Therefore I need to add it there. It's true that it might be removed from the `action` table. – Stephan-v Sep 11 '18 at 07:44
  • @JonasStaudenmeir If I were to make the `project_id` nullable I would still need a way to fetch an action and then fetch the servers assigned to that action. If I were to remove it my only point of reference from a `project_id` would be 1 or more servers pointing to a single `action`. – Stephan-v Sep 11 '18 at 07:46
  • Can you please clarify the ***concepts*** (not the tables!) that you are using. I understand that a *deployment pipeline* defines a sequence of *deployment actions* (either custom or default). How is a *deployment server* associated with the pipelines it has to execute? What is the meaning of "project" and "project ID"? – Gerd Wagner Sep 11 '18 at 10:13
  • Well, if `project_id` belongs to a server, that is fine. My point was that it looks fishy that you have it at both places and you should doublecheck if it is correct (especially since you seem to have a problem specifically with this column). We do not really know your full requirements, but assuming the 3 bullet points are those, you only reference to a project_id in context of a pipeline, so "because I don't always work with a pipeline" seems like trouble (and if you want/need that, you have to include it in the requirements/design) – Solarflare Sep 11 '18 at 10:17
  • @GerdWagner A deployment server is not neccesarily associated with a pipeline but rather an action. When a deployment runs certain actions may be ran on multiple servers for redundancy. For example if you were to have a load balancer between 2 servers you would want these servers in sync. A project would be a certain `application`. A project would currently always only have one deployment `pipeline` but it may have multiple servers associated. A user would then be able to add actions to a deployment pipeline and specify(checkboxes) on which servers the actions needs to be executed. – Stephan-v Sep 11 '18 at 11:11

1 Answers1

1

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.

Gerd Wagner
  • 5,481
  • 1
  • 22
  • 41
  • A standard action is user-selectable though. This just has a pre-defined script and a user might add to his/her `pipeline` as well in a certain order. It can be mixed into the pipeline with a custom `action` but I makes it easier for a user to get started. It does need an order though and needs the ability to get attached to a projects pipeline. – Stephan-v Sep 13 '18 at 07:05
  • Sure, when a user defines a pipeline, (s)he can choose either custom or standard actions. What I propose is a case of a simple class/table hierarchy, which can often be solved with the *single-table inheritance* strategy, see https://stackoverflow.com/questions/26691577/how-can-i-implement-single-table-inheritance-using-laravels-eloquent – Gerd Wagner Sep 13 '18 at 09:30
  • I have been thinking about a similar approach. In my case I guess I would have to replace `action_id` for a `pipelinable_type`. This still leaves me with the question if I have a `project_id` as my starting point, how do I couple `actions` or `custom_actions` to a `project_id`? Do I add the `project_id` to the polymorphic pivot table? – Stephan-v Sep 13 '18 at 12:34
  • I still do not understand your use case of a "project_id as the starting point" of what? Of defining a pipeline? My understanding was that users define their pipeline for executing certain actions on certain servers, such that custom actions are defined for specific "projects"/apps, and subsequently, this pipeline is run. – Gerd Wagner Sep 13 '18 at 14:25
  • Let's say I hit a route like this `/projects/1/pipeline` in this case the only reference I would have is a `project` with a `project_id` of 1. I can fetch data with either a `project_id` or a `user_id`. I need some way to fetch a user's `pipeline` though. Each user can create their own projects, with every project having a single pipeline. – Stephan-v Sep 14 '18 at 11:30