SQL One-to-One Relationship and Resulting Schema

Question

I am creating a mortgage/loans database - I have a table called mortgages with the fields:

• mortgage_id
• client_id
• *rate_type* (which may be: fixed or tracker) ...etc

Depending on the choice for rate_type - the fields that follow will be different. For instance, if a mortgage with a fixed rate_type is selected, then the user will only enter that fixed rate. If it is a tracker mortgage, then a track rate e.g. +0.90% and a base rate(which is being tracked) e.g. 0.50% is needed[giving adjusted rate = 1.40%].

My question is: how can I go about implementing the correct table/field structure for this. Ideally I don't want to have all the the fields (highlighted):

• mortgage_id
• client_id
• rate_type
• fixed_rate
• track_rate
• base_rate

Because it could lead to confusion. Is there any way to separate these into other tables? Perhaps another table with the rate_details (one for fixed and another for tracker) with a one-to-one relationship?

Answer 1

based on your question, i'd suggest that you can create 3 tables.

1 for the Basic information, a table for storing details on fixed rates, and a table for storing details about base rates.

tblMortgages:

MortgageID | client id | rate type

tblFixedRates:

id | mortgageID | fixed_rate

tblTrackerRates:

id | mortgageID | track_rate, base_rate

Answer 2

What you have is an E-R model with a type (a mortgage) that provides a common set of attributes, each of which is an instance of 0 or 1 subtypes, that provide additional subtype-specific attributes. See my answers at

Derived concepts - database design considerations

and

Should I Design a SQL Server database to rely on UNION or avoid it?

for how to solve this.

Answer 3

It is valid to have all three columns in the same table but only use 1 or 2 of them as needed. All three columns can be NULLABLE.

Another alternative is use 2 columns for either type of rate type but set the one of the columns to 0 when dealing with fixed rates. Since you are adding two rates to come up with the total tracking rate then the value for the fixed rate plus 0 would be the fixed rate.

[dbo].[theTable]
   [mortgage_id],
   [client_id],
   [rate_type],
   [base_rate],
   [rate]       // or whatever generic name is appropriate

So when the [rate_type] is track you have

[base_rate] = 0.50%
[rate] = 0.90%

total = 1.40%

but when the [rate_type] is fixed you have

[base_rate] = 0%
[rate] = 0.70%

total = 0.70%

Answer 4

As others suggested, my advice is 2 sub-tables for FixedRateMortgages and TrackerRateMortgages that have the MortgageID as the Primary Key and also as a Foreign Key back to the main Mortgages table.

This ensures a one-to-one, but it will not enforce that a mortgage should only exist in one of the two sub-tables. This is a constraint that databases don't enforce well, it is not referential integrity we're talking about here. You could use triggers on the sub-tables to ensure that only mortgages that don't exist in the other sub-table are being inserted, but triggers are pretty ugly IMHO. I would probably stick to enforcing that invariant in your application layer (i.e. code) and not the database.

Answer 5

The best you can achieve using SQL FOREIGN KEY constraints is to ensure that each mortgage type appears at most once in each subtype table, a one-to-zero-or-one relationship, if you will. One way to enforce this constraint is to use a two-column composite key on { ID , type } to be used throughout the schema and allowing the type to be tested in subtable constraints. Here's a rough sketch using two mortgage subtypes (curly braces indicate a list with no implied order):

Mortgages { mortgage_ID , mortgage_type } 
   KEY { mortgage_ID } 
   KEY { mortgage_ID , mortgage_type }
   CONSTRAINT mortgage_type = 'Tracker'
              OR mortgage_type = 'Fixed'

FixedRateMortgages { mortgage_ID , mortgage_type , fixed_rate }
   KEY { mortgage_ID , mortgage_type }
   FOREIGN KEY { mortgage_ID , mortgage_type } REFERENCES Mortgages
   CONSTRAINT mortgage_type = 'Fixed';

FixedRateMortgages { mortgage_ID , mortgage_type , base_rate , track_rate }
   KEY { mortgage_ID , mortgage_type }
   FOREIGN KEY { mortgage_ID , mortgage_type } REFERENCES Mortgages
   CONSTRAINT mortgage_type = 'Tracker';

Clients { client_ID } 
   KEY { client_ID } ;

Agreements { mortgage_ID , mortgage_type , client_ID }
   KEY { mortgage_ID , mortgage_type , client_ID }
   FOREIGN KEY { mortgage_ID , mortgage_type } REFERENCES Mortgages
   FOREIGN KEY { client_ID } REFERENCES Client;

You did not specify a SQL product. Strict one-to-one referential integrity may be maintained in Standard SQL-92 using CREATE ASSERTION constraints declared as DEFERRABLE INITIALLY DEFERRED to encapsulate this 'distributed' one per subtype table logic. A SQL statment could then, in a tranasction, defer the ASSERTIONs, modify referenced and referencing tables then reapply the ASSERTIONs (or do this automatically by committing the transaction). Sadly, there are no real life SQL products that support CREATE ASSERTION. There are workarounds depending on vendor e.g. triggers, a table expression in a SQL function called from a row-level CHECK constraint, revoking write privileges from the tables then forcing users to update the tables via CRUD procedures that ensure referential integrity, etc.

That said, it is usually acceptable in SQL to have one-to-zero-or-one relationships and indeed there may be advantages to doing so e.g. to make database constraints easier to write (and therefore fewer bugs), flexibility of not forcing users to use one set of procedures, etc.