Polymorphic ORM database pattern

I remember when - a long time ago - I was messing around with the Java ActiveObjects ORM, I came across a database pattern it claimed to support.

However, it is very difficult to find the pattern's name, by search for the general idea, thus I would really appreciate it if someone could give me the name of this pattern, and some thoughts on the "cleanness" of using it.

The pattern was defined as such:

Table:
  reference_type <enum>
  reference      <integer>
  ...

... where the va开发者_Python百科lue of the field reference_type would determine the type (and thus the table) to which was being referred. Thus:

User:
  location_type <l&l, address, city, country>
  location      <integer>
  ...

... where depending on the value of the location_type field, the foreign key location would refer to either the l&l, address, city or country table.

You're having difficulty finding it because it's not a real (in the sense of widely adopted and encouraged) database design pattern.

Stay away from patterns like this. While ORM's make mapping database tables to types easier, tables are not types, and vice versa. While it's not clear what the model you've described is supposed to do, you should not have columns that serve as fake foreign keys to multiple tables (when I say "fake", I mean that you're storing a simple identifier value that corresponds to the primary key of another table, but you can't actually define the column as a foreign key).

Model your database to represent the data, model your objects to represent the process, and use your ORM and intermediate layers to do the translation; don't try to push the database into your code, and don't push your code into the database.

Edit in reponse to comment

You're mixing database and OO terminology; while I'm not familiar with the syntax you're using to define that function, I'm assuming it's an instance function on the User type called getLocation that takes no parameters and returns a Location object. Databases don't support the concepts of instance (or any type-based) functions; relational databases can have user-defined functions, but these are simple procedural functions that take parameters and return either values or result sets. They do not correspond to particular tables or field in any way, other than the fact that you can use them within the body of the function.

That being said, there are two questions to answer here: how to do what you've asked, and what might be a better solution.

For what you've asked, it sounds like you have a supertype-subtype relationship, which is a standard database design pattern. In this case, you have a single supertype table that represents the parent:

Location
---------------
LocationID (PK)
...other common attributes

(Note here that I'm using LocationID for the sake of simplicity; you should have more specific and logical attributes to define the primary key, if possible)

Then you have one or more tables that define subtypes:

Address
-----------
LocationID (PK, FK to Location)
...address-specific attributes

Country
-----------
LocationID (PK, FK to Location)
...country-specific attributes

If a specific instance of Location can only be one of the subtypes, then you should add a discriminator value to the parent table (Location) that indicates which of the subtypes it corresponds to. You can use CHECK constraints to ensure that only valid values are in this field for a given row.

In the end, though, it sounds like you might be better served with a hybrid approach. You're fundamentally representing two different types of locations, from what I can see:

• Coordinate-based locations (L&L)
• Municipal/Postal/Etc.-based locations (Country, City, Address), and each of these is simply a more specific version of the previous

Given this, a simple model would look like this:

Location
------------
LocationID (PK)
LocationType (non-nullable) ('C' for coordinate, 'P' for postal)

LocationCoordinate
------------------
LocationID (PK; FK to Location)
Latitude (non-nullable)
Longitude (non-nullable)

LocationPostal
------------------
LocationID (PK, FK to Location)
Country (non-nullable)
City (nullable)
Address (nullable)

Now the only problem that remains is that we have nullable columns. If you want to keep your queries simple but take (justified!) flak from people about leaving nullable columns, then you can leave it as-is. If you want to go to what most people would consider a better-designed database, you can move to 6NF for our two nullable columns. Doing this will also have the nice side-effect of giving us a little more control over how these fields are populated without having to do anything extra.

Our two nullable fields are City and Address. I am going to assume that having an Address without a City would be nonsense. In this case, we remove these two attributes from the LocationPostal table and create two more tables:

LocationPostalCity
------------------
LocationID (PK; FK to LocationPostal)
City (non-nullable)

LocationPostalCityAddress
-------------------------
LocationID (PK; FK to LocationPostalCity)
Address (non-nullable)

Seems to me that city and country would be part of the address table, and that L&L wouldn't be mutually exclusive with address (you might have both...), so, why limit yourself like that to one or the other?

Further more, this would prevent the location column from enforcing referential integrity, would it not, since it wouldn't always reference the same table?