Subdomain and Database per customer?

Question

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.

Answer 1

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