I am designing a database system + application that deals with pipe objects who are part of a larger grid network. There are about 1000-5000 pipes for each network. Let's assume each pipe can have 2 states (clean, dirty).
The application is able to change the pipe status after a maintenance job has been done at that pipe. Each maintenance job has an ID and several other information behind it. The same is true for the pipes.
My design for this problem is as follows:
table: pipe_status id maintenance_id pipe_id status 1 1 1 clean 2 1 2 clean 3 1 3 dirty 4 1 4 dirty ... 1000 1 1000 dirty 1001 2 1 clean 1002 2 2 clean 1003 2 3 dirty 1004 2 4 clean .... 2000 2 2000 dirty
So for every maintenance job, each individual pipe should have a status attribute, depending on whether they have been affected by this particular maintenance job or not. This means that for every maintenance job there are as much status entries as there are pipes, resulting in a quickly growing amount of data.
Example: 20+ grids with 1000-5000 pipes. 500+ maintenance jobs per grid and growing, results in 10-50 million entries in this table at the moment.
Is there better way to implement this problem in a database? It is important to note that the status of each pipe is visualized in the application, so even if a maintenance job only affects 5 pipes, the other 995 are still shown in the app. In this application the user can select a specific maintenance job and see the corresponding pipes and their status.