0

I am trying to design a database and i am having some questions, hopefully you can guide me for the next steps.

Setup

My stack consists of Node.js in the backend and my test DB is in Postgresql but i would also consider MongoDB if needed.


Concept

I am having the following entities with these relationships (As i imagine them in practice):

  • User (Customer)
    • username
    • email
    • password
    • role ENUM
    • PROJECTS[ ]
  • Project
    • description
    • CAMPAIGNS[ ]
  • Campaign
    • description
    • 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.


Ideas

  1. 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.

  2. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.