I'm building an application where there are different "Locations". There are Stores, Bars, Schools, and Churches (and so on) all of which are geocoded and defined by existing data sets. While not exactly alike, these entities can probably all fit into a "Locations" table. This will be helpful because users will constantly run reports and maps on these different locations and it will be far easier to run a single query on Locations than try to join 4 (or more) tables.
This data will change daily as stores go out of business or change their name. There will be some complexity in terms of updating these data sets but we can keep track of them with a Location.type field (type = school, or type = bar, etc).
So that's pretty straightforward. But I also want to allow users to add their own custom locations to the app. These will be of type = "custom". User contributed locations will also need to have a userId associated with them, which the other locations won't have. Is there a design issue with allow users to contribute content to the Location table? I'm worried about user data getting mixed up with system data during updates, or when users delete their data.
So, should we have a second, nearly identical CustomLocation table? But then how do we keep the Location IDs unique between the two tables? And we're increasing the code complexity of the application.
The easiest path seems like forcing everything into a single giant Location table, which will really simplify running reports and generating maps, but that doesn't "feel" like the right approach. It seems like a single giant table is really using "type" to create multiple tables within a table. Is there another approach where the Location table just handles IDs and where maybe I have a PredefinedLocation Table and a UserCustomLocation table? Thanks for your help!