MySQL Database Design – How to Create a Join on Three Tables from Different Databases

database-designMySQL

Here is a simplified example of what I'd like to do:

SELECT a.create_time, b.create_time
FROM DOB.dob_state AS a
JOIN numID.numID_history AS b ON a.create_time = b.create_time;

I'd like to join THREE tables instead of just two (each in its respective database on the same server), and I'm not sure how to do that concisely. The third database.table is entity.entity AS c. Where do I add that into the join?
Moving to slightly more complicated…I actually want to match on three fields, each of which will be included in the tables of the three databases: hash (int), create_time (timestamp), and update_time (datetime). How would I do this in a cost-efficient way?
In designing the three databases, is it essential that I use the same hash ID in all three databases or is there a way to link the three hashes (each referring to the same record) to a master ID in another database? That is for a related issue that I'll mention elsewhere, but it might affect the query process, so I'm including that consideration as well.

I am joining tables that have actual relationships with each other, but separate databases is part of the security strategy, which also includes encryption, etc. I'm looking to eventually figure out a way to do this same thing with databases on different servers, but this is just a start.

Best Answer

If you are using MySQL you can do this:

select * 
from dob.dob_state 
join numid.numid_history as t2 
    on t2.create_time = dob_state.create_time 
    and t1.last_name = t2.last_name 
    and t1.first_name = t2.first_name
join entity.entity as t3 
    on t3.create_time = t2.create_time
    and t2.last_name = t3.last_name 
    and t2_first_name = t3_last_name;

Tip: You don't have to write inner join, just join.

First, why not 'Option B':

For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.

For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)

Option C

Create a parent table that holds only the columns that all types share
Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.

How to map an IS-A relationship into a database

Way two is the correct way.

Your base class gets a table, and then child classes get their own tables with just the additional fields they introduce, plus foreign key references to the base table.

As Joel suggested in his comments on this answer, you can guarantee that a user will have either a direct login or an OpenID login, but not both (and also possibly neither) by adding a type column to each sub-type table that keys back to the root table. The type column in each sub-type table is restricted to have a single value representing the type of that table. Because this column is foreign keyed to the root table, only one sub-type row can link to the same root row at a time.

For example, the MySQL DDL would look something like:

CREATE TABLE Users
(
      uid               INTEGER AUTO_INCREMENT NOT NULL
    , type              ENUM("DirectLogin", "OpenID") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
);

CREATE TABLE DirectLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("DirectLogin") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
    , FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

CREATE TABLE OpenIDLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("OpenID") NOT NULL
    // ...

    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

(On other platforms you would use a CHECK constraint instead of ENUM.) MySQL supports composite foreign keys so this should work for you.

Way one is valid, though you are wasting space in those NULL-able columns because their use depends on the type of user. The advantage is that if you choose to expand what kinds of user types to store and those types don't require additional columns, you can just expand the domain of your ENUM and use the same table.

Way three forces any queries that reference users to check against both tables. This also prevents you from referencing a single users table via foreign key.

Best Answer

Related Solutions

Database Design – Creating Separate Tables for Different Product Types

First, why not 'Option B':

Option C

How to map an IS-A relationship into a database

Related Question