Re: 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 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