04-04 Database Design

The database schema is available here. The repository for the backend is here. I initialized a new Laravel / Lumen project and created migration files for the most basic tables.

Note that measurements is basically the middle table for the belongs to many relationship of servers and clients. --> Cool 😃

Problem

circularity of database schema is worrisome, maybe in future sever connection between environments and positions. Relationship would be nice to have, otherwise it would be hard to plot the data on a map of an env.

Solution

So far it seems okay to me that there are two "paths" between positions and their environments. One goes over the client -> measurement -> server -> environment route, the other goes directly to environments. This shouldnt be a problem though due to the nature of the query that the long path takes. The client has many measurements which have a server associated with it. But the dependency of measurements on clients dictates that one client has many measurements. This makes it impossible to go to the specific server that is associated with the environment. After all, there are many measurements with different servers associated with the client. Thus there isnt really a second path to the environment via this longer route.

If this does indeed pose a problem in the future, I can remove the positions table completely. For now it is not important at all and if it ends up making things too complicated, I can easily remove it. It only has downstream dependencies on other tables, there are no foreign key references to it. I consider this table nonessential for the time being.

Further actions

maybe users table in case this is more like a plug and play SaaS platform. I think people would like to have their own server though.

maybe a pivot table with measurements. measurements right now is the many to many between client and server, so maybe it will be easier for lumen if I directly say that servers belong to many clients and vice versa via a measurements pivot table.

Position easy to remove for now, so I should keep it that way.

Will create seeder files for testing and controllers for CRUD.