On Fri, May 22, 2009 at 4:10 PM, Benjamin Smith
<lists@benjamindsmith.com> wrote:
> I have some questions about the best way to best use foreign keys in complex
> schemas. It's becoming cumbersome to manage a large set of foreign keys - is
> there a better way?
>
> // FOUNDATIONAL //
>
> Let's say that you want to keep addresses, and one of the values that you
> need
> to keep is the state. So you have two tables defined:
>
> create table states
> ( state varchar unique);
> create table customers
> (... state varchar not null references states(state), ...);
>
> If you want to be a bit more "pure", you might do it like this:
>
> create table states
> (id serial primary key, state varchar(2), description varchar);
> create table customers
> (... states_id integer not null references states(id), ...);
In this type of instance, where you're looking up mostly static, small
data sets like state names, it's usually better to FK to the actual
value. The reason is obvious, you don't have to join to the state
table to get the state, it's right there in the customer table.
> So far, so good. But when you have a large number of fields with foreign key
> references, you end up with a bazillion reference tables, all with very
> similar layouts. EG:
>
> create table customer_types
> (id serial primary key, title varchar(4), description varchar);
> create table customer_taxcode
> (id serial primary key, title varchar(4), description varchar);
> ...
> create table customers
> (...
> customer_types_id integer not null references customer_types(id),
> customer_taxcode_id integer not null references customer_taxcode(id),
> ...);
>
> Getting the appropriate code tables from all these different tables becomes
> cumbersome, just because there are SO MANY tables to get these values from.
>
> So the next idea is to create a master set of code tables and foreign key to
> there, but this has its own set of problems EG:
You're going to a LOT of effort to use surrogate keys.
> This also becomes cumbersome. Is there a better way? Is there some way to do
> this not covered in the docs? What would be ideal is to treat the reference
> something like a join - might be something like:
Yeah, natural keys. They're a natural fit for many of these types of data.