Thoughts on Database Schemas: variable column number? Schema-less databases?

Hello fellow MODx-ers.

I’m working to architect a database for a project and you guys have some good ideas for all sorts of things, so let me know what you think about the following...

Imagine a database that needs to store information about different types of locations, e.g. schools, malls, hospitals. The problem is that schools require different attributes than do malls or hospitals. (Sound familiar?) I’ve identified 3 possible ways to store this type of information, and each has it’s advantages and disadvantages:

-----------------------------------
SOLUTION 1
-----------------------------------
Follow the same type of table schema used by MODx for pages with Template Variables. This means that I’d have a "locations" table that contained columns like "address, city, state, zip", etc. which are common to schools, hospitals, etc. This is comparable to how a standard MODx page has attributes (i.e. columns) like "title, summary, content" etc. -- then another table would define new attributes, e.g. "number_of_cells" and then a third table would join the attribute with the location and store a value so that a "prison" location would have a "number_of_cells" attribute*.

*MODx does this slightly differently, but the idea is the same -- you end up storing your extra "columns" as rows in another table.

Pros: Pure MySQL, easy to add location types or attributes. The analytics people who want to make reports can query the database as needed.
Cons: The queries require a lot of awkward JOINs and there is some work required in getting columns from one table to match up with rows from another.


-----------------------------------
SOLUTION 2
-----------------------------------
Again, use a "locations" table to hold attributes that apply to all location types, but create separate tables for each location type. In this approach, I’d have a "schools" table and a "hospital" table etc. with a one-to-one link back to a location_id.

Pros: Certain queries would be much easier -- e.g. a simple join between "schools" and "locations" would give me all the attributes for one or many schools. Reporting/analytics people could query the database normally using any SQL tool e.g. phpMyAdmin.
Cons: I’d have to make a dedicated query to deal with each location type. MySQL doesn’t have a good way to join the locations table on some arbitrary other table. Each time I added a new attribute or a new location type, I’d have to modify the database (by adding a table or a column, respectively).

-----------------------------------
SOLUTION 3
-----------------------------------
This would rely on a schema-less "document" database such as Mongo (http://www.mongodb.org/). If you’ve ever been guilty of stashing a bunch of variables in the $_SESSION array, you can quickly understand how this database works: you basically tell it to save a JSON formatted object with ANY attributes and it saves it using a unique document id (quite similar to the session_id used to store session info).

Pros: this allows for extreme flexibility in storing ANY number of location attributes. I wouldn’t have to change the database (or ORM) at all if suddenly I needed to track hundreds of new attributes for a location type. It’d be equally easy if I added hundreds of new location types; Mongo doesn’t care. You just get your array of data and store it. I wouldn’t even need to have a locations table at all... I could simply put all the data into a single Mongo "table"... if it was a "school" location, it would have different attributes than a "hospital" location.
Cons: This would obscure the data from analysts and anyone doing reporting. Querying the "documents" stored in my Mongo "table" would require some RTFM’ing to use their command-line interface OR I’d have to code specific PHP searches etc. ... but that’s the price you pay for riding the cutting edge.


That’s what I’m looking at... what do you guys think? Are there better ways to handle this problem?

My first impulse would be to put all the fields in the same table and have a location_type field, then create templates (or Tpl chunks) for each type of location. Adding new fields to the DB periodically wouldn’t break anything (and you could create a snippet to do it for you).

You’d have a huge table, but you’d avoid having redundant fields and/or the hassle of dealing with multiple table structures and remembering which fields were "extra" ones. XPDO would cache much of it for you.

I would think that most of the fields would be used for most locations, but then I don’t really know your application.

This is a perfect problem for the Class Table Inheritance SQL anti-pattern IMO. See http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back; it’s solution #3 under the Entity-Attribute-Value (slides 29 through 31).

I’ve been considering implementing built-in support for many of these solutions (including this one) in xPDO, but all of these are possible to implement using xPDO as-is.

Thanks for the input. Another friend suggested to have one extra column on the "locations" table where I could stash a JSON array of all additional attributes. That’s very similar to what Mongo would do, but I think the Mongo solution is better though... we could search the data in the Mongo DB (it would require some RTFM’ing) whereas the JSON objects wouldn’t be searchable.

I really have to crack open xPDO -- I know you (Jason) have been spending a lot of time and thought on this type of problem. Thanks for the link. That slideshow has come up in several conversations over the past year...