How to Design Database with User-Contributed Content

Question

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!

Answer 1

If the number of fields that are not shared by all different types of Location are small, I would just go for a single table for all Locations (including the custom ones) and just put a NULL value in the fields that are irrelevant for that location type.

If there are more than a few non-shared fields, I would go for a "shared primary key" approach. Here you have one table for the common fields (Location) and additional tables with the fields for each specific type of location (SchoolLocation, ChurchLocation, CustomLocation, etc.).
The trick here is that the type-specific tables don't have their own primary key, but they share the primary key with the Location table.

For example, you could have these tables and entries:

Location:
id |  Type  | Name
 1 | School | MIT
 2 | Custom | Home
 3 | Church | St. Pauls Cathedral

CustomLocation:
id | UserId
 2 | 12345

ChurchLocation:
id | Religion
 3 | Roman Catholic

Note each <Type>Location table only contains entries for the corresponding type and that the values in the id column match with the corresponding entry in the Location table.

---

With regard to your concerns for data integrity, it should be rare that all the non-custom data will get updated at the same time, so you need to design your update process such that changes to part of the database don't corrupt unrelated entries. And unless you were thinking of having separate tables for each user, the same goes with deleting custom entries after a user closes their account. That is all standard practice when working with databases.

The technique I described for using shared primary keys is also a know technique for modeling an inheritance relationship between classes onto the database.

Answer 2

You can manage all locations in a single table and then move columns into other tables that are unique to a location group (System or Custom). Creating a new table for each type can be cumbersome and difficult to manage. This also gives you flexibility as this system grows. My gut feeling at this point is, you're not going to need it, but keep your positive attitude that you'll have millions of locations.

It sounds like you're relying on some data structuring to handle security and integrity concerns.

I'm worried about user data getting mixed up with system data during updates, or when users delete their data.

Your app is going to have different "view" or whatever your development environment calls them, for system locations and custom/user locations. Two different tables will help, but since you need more than that, one table will work as well. Even if you deny permission to users to alter the system location table, you still need to make sure each user can only alter their records in the custom table.

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.

You could use GUIDs to make sure the identifiers are unique or just start one of the table with a number that you can predict will exceed the other.

As far as one big table, be more concerned with unused columns instead of too many rows. Most RDBMS have plenty of strategies using indexing, files and other techniques to aid performance with large datasets.