0

I'm currently working on a farming IoT system and confused about the architecture so I wanted to get your views.

I'm using a relational DB (PosgreSQL).

I have multiple customers, each customer has one or more farmhouse and each farmhouse has one or more field. I have IoT devices on each field, from a few to 200 hundred which communicates with backend each second.

I have multiple options how to architect this system.

  1. Subdomain and server per customer which will have their own database (logical db's on single instance), schema per farmhouse and a table for keeping fields. So when I want to get devices on certain field, I only need to do a inner join with fields and devices and filter.

  2. No subdomains, schema per customer and table for farmhouses and fields. Then I need to create association between farmhouse and field, and use this composite key to select the field and then make inner join on field_id.

  3. Single schema, I need to have a table for customers, farmhouses and fields. I need to associate customers with farmhouses, farmhouses with fields and select devices on fields with composite key of customer, farmhouse and field.

I'm quite junior on architectural design so I can't tell which one of these makes more sense.

4
  • 2
    What do you want to optimize for? Convenience in programming? Speed of execution? Highest possible security between customers? Dec 14 '20 at 18:27
  • @RobertHarvey thanks for your comment. I tend to optimize for convenience since my data isn't so big and database should perform well even with table with million entry.
    – bca
    Dec 14 '20 at 18:29
  • 1
    If you need cross-customer queries and convenient programmability, option 3 favors this. Dec 14 '20 at 18:36
  • @RobertHarvey I won't need cross-customer queries but yes, it might be simpler to program even here will be more table joins.
    – bca
    Dec 14 '20 at 18:39
7

People typically decide, based on the following:

Choose option 1 (separate databases and servers) for isolation and security. For instance, if your customers ever want full-control of their own data but never want to share with other customers, or if you get a new customer who insists that the data must be on Oracle, or DB2 or something, or they occasionally want to operate in a disconnected (no internet) manner.

Choose option 2 (separated by schema) if different customers may want different features which require differences in schemas, or if you need tiered security (farmer A has simple security needs, but farmer B works for USDA and needs higher security).

Choose option 3 (one server, one schema) if cost, maintenance, simplicity are of any consequence to you. Multiple servers often add cost and extra maintenance effort eventually. It requires more planning and design, but those are good things.

Most developers/SWAs would default to option 3 because it is the easiest design for cost, maintenance and managing. It becomes more relevant as your number of customers increases. (imagine maintaining thousands of independent servers).

1
  • Thank you for the detailed answer!
    – bca
    Dec 14 '20 at 20:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.