Thread: Using meta-data for foreign key?

I have an existing table in an app, along the lines of:

CREATE TABLE foo (
  name text,
  address text,
  some_numeric_info integer,
  <a bunch of additional fields here>
);

I now need to be able to associate additional information (e.g. printing
order) with each field.  Is it a bad idea to use the (fully qualified)
field name as a foreign key to link back to the table meta-data?  Is
there a better way?

On Apr 17, 2008, at 10:23 AM, Mike Blackwell wrote:
> I have an existing table in an app, along the lines of:
>
> CREATE TABLE foo (
>  name text,
>  address text,
>  some_numeric_info integer,
>  <a bunch of additional fields here>
> );
>
> I now need to be able to associate additional information (e.g.
> printing
> order) with each field.  Is it a bad idea to use the (fully qualified)
> field name as a foreign key to link back to the table meta-data?  Is
> there a better way?

You're going to have to be *much* more specific about what you're
trying to do/talk about.  A complete example would be best.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

More detail, as suggested.

I have an existing table in an app, along the lines of:
>>
>> CREATE TABLE foo (
>>  name text,
>>  address text,
>>  some_numeric_info integer,
>>  <a bunch of additional fields here>
>> );
>>


I essentially need another table

CREATE TABLE foo_printing_options (
  field_name text,
  print_order int not null,
  suppress boolean not null,
  <a couple additional options here>
);


The values of field_name in foo_printing_options would be the individual
field names in foo (i.e. name, address, etc).   foo_printing_options
would be available to the user via the app to allow them to specify
report layout.

My question, then, is if it considered acceptable practice to, instead
of having field_name in the secondary table, have a foreign key
reference back to the field definition in the meta-data.  This would
allow the app to be less sensitive to schema changes (fairly common).

Is linking to the metadata from the app tables directly bad?  Unsafe?  Ok?

Mike Blackwell <maiku41@sbcglobal.net> writes:
> My question, then, is if it considered acceptable practice to, instead
> of having field_name in the secondary table, have a foreign key
> reference back to the field definition in the meta-data.

If you mean a foreign key reference into the system catalogs, we don't
support that :-(

            regards, tom lane

> I have an existing table in an app, along the lines of:
> >>
> >> CREATE TABLE foo (
> >>  name text,
> >>  address text,
> >>  some_numeric_info integer,
> >>  <a bunch of additional fields here>
> >> );
> >>
>
>
> I essentially need another table
>
> CREATE TABLE foo_printing_options (
>   field_name text,
>   print_order int not null,
>   suppress boolean not null,
>   <a couple additional options here>
> );
>
>
> The values of field_name in foo_printing_options would be the
individual
> field names in foo (i.e. name, address, etc).   foo_printing_options
> would be available to the user via the app to allow them to specify
> report layout.
>
> My question, then, is if it considered acceptable practice to, instead
> of having field_name in the secondary table, have a foreign key
> reference back to the field definition in the meta-data.  This would
> allow the app to be less sensitive to schema changes (fairly common).
>

Yes, of course you want to normalize your data in an operational
application and when doing so, you will want to use primary and foreign
keys.

CREATE TABLE foo (
id integer primary key not null,
name text,
address text,
some_numeric_info integer,
<a bunch of additional fields here>);


CREATE TABLE foo_printing_options (
id integer primary key not null,
foo_id integer not null,
field_name text,
print_order int not null,
suppress boolean not null,
<a couple additional options here>
> );

alter table foo_printing_options
add foreign key (foo_id) references foo (id);


I can also see you adding a field_name table, a unique key on
printing_options.id and printing_options.print_order.  Name and address
in foo makes me think you should probably have a customer table (or is
foo the customer)?  What if your customer can have multiple addresses?
You'll need just an address table in that case.  A State table is likely
needed with maybe zip codes or cities.

> Is linking to the metadata from the app tables directly bad?  Unsafe?
Ok?
>

Using the term "metadata" is misleading in your example.  Metadata is
data about data and in your example, you just have data.  It is fine to
join tables in an RDBMS.



Jon

On Thu, Apr 17, 2008 at 1:55 PM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
>  Using the term "metadata" is misleading in your example.  Metadata is
>  data about data and in your example, you just have data.  It is fine to
>  join tables in an RDBMS.
>

I believe you missed the OP's actual goal. He's not interested in
linking printing options to specific rows of the foo table; he wants
each column of the foo table to have a single entry in the printing
options table; that is, he wants a foreign key reference to the system
catalog giving the columns of entity foo. He does, in fact, appear to
be interested in a foreign key reference to a table's metadata.

--
- David T. Wilson
david.t.wilson@gmail.com