Search code examples
mysqldatabasenormalizationone-to-onedatabase-normalization

How do I join on the correct one-to-one table (super-type, sub-type model)?

I've been looking into first, second, and third normal forms, and I want to do a better job normalizing my tables. Part of this, I realized, was that I've never understood the purpose of one-to-one tables. From what I understand, "optional" data should be grouped into another table, leaving distinct entities intact, while avoiding the nuances of maintaining several NULL fields in one monolithic table.

So, a real-world scenario. In a CMS, I want to maintain several different types of "pages," making it extendable by additional plugins without affecting the original schema. I have these as sample tables so far:

  • Pages (title, path, type, etc.)
  • ContentPages (same as base page, but with keyword/description/content fields)
  • LinkedPages (same as base page, but contains a reference to another page)
  • ProductPages (same as base page, but with SKU and other ecomm-related info)

So far, so good. No NULLS. Self-documented design. Super-typing / Sub-typing is consistent between my PHP models and database. Everything's DANDY.

EXCEPT, given any page ID, I don't want to do a first query to get the base page info, figure out what type of page it is, and then get the corresponding sub-type information with another query. Do I have to keep track of this with application state (or URL), or is there a way to know which table to join on, while only knowing the page ID and nothing else?

This is really easy with only one table (obviously), as the NULL fields imply the type, or an ENUM can tell me what it is. Switching back to 1NF isn't an acceptable answer, as I already know how to do it. I want to learn this way ;)

UPDATE: Also wanted to mention that each of the sub-type properties is unique to that type. So, any common property shared by all types will, of course, go into the base page table. Sub-types won't share any other properties. This seemed like a logical way to group the sub-tables, but maybe I'm defeating the purpose of one-to-one tables with this arrangement...

Solution

  • It depends on who's asking the question. If your plugin is driving the query then it can start at its specific subtype and join in the supertype, which it knows must exist.

    I don't know what your business requirements are, but it seems to me that if you are trying to keep things modular then you want to drive as many joins from the child side (i.e. the plugin side) as possible.

    If you are going to have a query driven from the supertype to the subtype then you can use an outer join and just be ready in your code to handle null columns if the subtype in question isn't present. Obviously that approach is less modular, but I suppose there could be times when that is what you need or want to do.