I am trying to design a database and i am having some questions, hopefully you can guide me for the next steps.
My stack consists of Node.js in the backend and my test DB is in Postgresql but i would also consider MongoDB if needed.
I am having the following entities with these relationships (As i imagine them in practice):
- User (Customer)
- role ENUM
- PROJECTS[ ]
- CAMPAIGNS[ ]
- EQUIPMENT MEASUREMENTS[ ]
- Measurements (Raw Data 80000 - 200000 rows each table with 20-50 cols, ideally no ID usage, maybe an index)
- several different columns
The way i am thinking above is as follows.if a User is not an Admin, then the User should have some Projects that should be able to see via the API, of course if a User is an Admin as a Role then he/she should be able to pick whatever PROJECT-CAMPAIGN-MEASUREMENT.
The first idea i have but seems to be error prone in the backend API for several reasons would be the following. The Users should live in a fixed table under a database in the cluster, and then i would break the relationships into the following: Project --> Database || Campaign --> Schema || Measurements --> Table.
More specifically, i was thinking to create these types of relationships by adding strings in the arrays above as in names to reuse in the API, so eventually, i would dynamically use queries that change the PG adapter (by passing a different DB name) , the schemas and the tables accordingly.
The other idea, assuming i would use MongoDB, would be to pass references but i am not sure yet how i would do that in practice, since i can see that i can pass ref IDs but in case i want to reference whole collections it would be tricky.
Any ideas would be really helpful, especially to know if i am on the right path or on a completely wrong one.
If you need any more clarifications please feel free to ask me anything.
Thanks in advance.