I want to create a schema that reflects the following relationships, and I'm trying to do this in MySQL Workbench. (I'm no DB expert)
- I have 3 environments - sandbox, staging, and production
- I release a product version in each environment, e.g.,
st-1.0.0for staging, and
pr-1.0.0for production. I can have multiple releases per environment, but there can only be ONE version that's currently released in each environment.
- Each product version in each environment contains a set of services, same set of services in all the environments, but differing in their version numbers. So
sb-1.0.0 = [svc1-2.0.0, svc2-2.0.0], st-1.0.0 = [svc1-1.0.0, svc2-2.0.0], and pr-1.0.0 = [svc1-1.0.0, svc2-1.0.0]. As you can see, I have the same 2 services in all environments, but the service versions can be different (or the same) in each environment.
I want to accomplish multiple things.
- I want to be able to create new releases in each environment, with each release containing the set of services and corresponding versions. So I want to be able to create a
pr-2.0.0 = [svc1-2.0.0, svc2-1.0.0]version.
- I want to be able to query my DB for version
pr-1.0.0and know all the services and their respective versions in this particular release.
- I want to have the notion of a "current version" and a "desired version" for each service in each environment.
- I want to be be able to then run a script (Kubernetes?) to release each service and their respective version based on the query.
- I want to be able to extend it to more environments, e.g., add a UAT environment.
So how do I model my DB?