How can I model this schema in MySQL?

Question

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., sb-1.0.0 for sandbox, st-1.0.0 for staging, and pr-1.0.0 for 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.0 and 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?

Answer 1

Have you thought about what happen with stale/old data??

• Each new version creates a ton of new entries.
• Management becomes a nightmare
• the DB becomes a SPOF(single point of failure).

---

Better Solution: Service discovery

The better solution would be to use a Service discovery System and annote each Service with their version and the environment as a Metadata tag. This keeps the System more manageble.

More Help

IMHO, sticking Linux Code into a Docker Image and run multiple copies of it, is the best way of getting a immutable infrastructure up and running.

Answer 2

I want to credit Max N for making me think about another solution.

In fact I already have the solution, right in front of me. We're a Gradle shop, and our build.gradle files contains our dependencies. So when I release a version, I know exactly what services+versions I need for this version. Since we have branches corresponding to our environments, I the services I need in each environment. So when I just need to

• I need to Git tag every time we do a build, including the tools that did the build
• When deploying, I then need to deploy above tag, which includes code + tools

Thanks again Max N.