I'm a full stack developer that's new to the world of GIS. I'm looking at using ArcGIS Enterprise and some of its apps for some future projects and I've got a few questions about database design.
My biggest question revolves around compatibility: Is database design any different if I'm planning on using it with ArcGIS? More specifically, do ArcGIS products benefit from any certain database design paradigms?
My data would contain 20 different types of features (Houses, Vehicles, Stores, etc.). Each type of feature has attributes that are specific to that type (vehicle has model, house has sqft, etc.) and attributes that are shared between all feature types (date, shape/geo, deleted flag, etc.). Each type of feature also has its own types of collectible data that is added periodically (vehicles have maintenance data, houses have inspection data, etc.)
The way I see it, there are two basic ways to make the database.
Option 1: Everything is split into its own table
I've made a basic diagram to show an example. It's straightforward. Queries are basic. Structure is easy to understand. But making changes would require adding and modifying tables. Another downside is the amount of tables - even though I've only shown 6, I'd have at least 40 (20 different feature types, each with at least one type of collectible data).
Option 2: Make everything generic
I've watered down the diagram a bit but the basic idea is there. One table to contain ALL features and their shared attributes, one table to contain features' attribute data, and one table to contain features' collectible data. It would also have the three reference/lookup tables (I think they're called "domains" in the ArcGIS world?). Those reference tables would define the possible attributes and collectible datas for a given feature type.
Option 2 only needs 6 tables and I can add and change feature types, attribute types, and collectible datatypes without modifying the structure of the database.
The downside is complicated queries. Since I'm new to ArcGIS I'm a little worried about how well the ArcGIS apps will handle those queries and the possibility of it resulting in a more complicated UI/UX (it's important that the UI/UX be very straightforward and smooth).
Circling back to my main question
Does either option provide benefits (or drawbacks) when working the ArcGIS apps?
My main worry is designing my database one way then realizing that I've created compatibility issues or even just configuration headaches for myself down the line.
Thanks in advance for any advice!
