Re: Composite types for composite primary/foreign keys?

Hello,

and thanks again for your reply.

And excuse me for taking so long to reply.

> I wanted to simplify the schema and make it more "readable" for
>> clueless morons like me. >;->
>
> Simplifying the schema is fine (and good!) as long as it exhibits the
> same behavior as the more complex one:

Well, that (same behaviour) is probably not the case in my case (see
below).

> often in the course of
> simplifying you find a solution yourself. However, we cannot help you
> if you don't provide adequate information.

I'm not sure whether I am violating some copyright, so I didn't want to
post the SQL script here. But the script is publicly downloadable at
www.mimosa.org, and I only need a part of it to explain the basic
concept. So this is the "complex" schema.

CREATE TABLE enterprise_type(
ent_db_site cris_string16_type NOT NULL,
ent_db_id cris_uint_type NOT NULL,
ent_type_code cris_uint_type NOT NULL,
name cris_string254_type NOT NULL,
user_tag_ident cris_string254_type,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
PRIMARY KEY (ent_db_site, ent_db_id, ent_type_code)
)
;

CREATE TABLE enterprise(
enterprise_id cris_uint_type NOT NULL,
ent_db_site cris_string16_type NOT NULL,
ent_db_id cris_uint_type NOT NULL,
ent_type_code cris_uint_type NOT NULL,
user_tag_ident cris_string254_type,
name cris_string254_type NOT NULL,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
PRIMARY KEY (enterprise_id)
)
;

CREATE TABLE site_type(
st_db_site cris_string16_type NOT NULL,
st_db_id cris_uint_type NOT NULL,
st_type_code cris_uint_type NOT NULL,
name cris_string254_type NOT NULL,
user_tag_ident cris_string254_type,
mobile_yn cris_no_or_yes_type,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
PRIMARY KEY (st_db_site, st_db_id, st_type_code)
)
;

CREATE TABLE site_type_child(
st_db_site cris_string16_type NOT NULL,
st_db_id cris_uint_type NOT NULL,
st_type_code cris_uint_type NOT NULL,
child_st_db_site cris_string16_type NOT NULL,
child_st_db_id cris_uint_type NOT NULL,
child_st_type_code cris_uint_type NOT NULL,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
PRIMARY KEY (st_db_site, st_db_id, st_type_code, child_st_db_site,
child_st_db_id, child_st_type_code)
)
;

CREATE TABLE site(
site_code cris_string16_type NOT NULL,
enterprise_id cris_uint_type NOT NULL,
site_id cris_uint_type NOT NULL,
st_db_site cris_string16_type NOT NULL,
st_db_id cris_uint_type NOT NULL,
st_type_code cris_uint_type NOT NULL,
user_tag_ident cris_string254_type,
name cris_string254_type,
duns_number cris_uint_type,
template_yn cris_no_or_yes_type,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
PRIMARY KEY (site_code)
)
;

CREATE TABLE manufacturer(
mf_db_site cris_string16_type NOT NULL,
mf_db_id cris_uint_type NOT NULL,
manuf_code cris_uint_type NOT NULL,
manuf_trade_name cris_string254_type NOT NULL,
company_name cris_string254_type NOT NULL,
phys_addr cris_string254_type,
phys_city_name cris_string254_type,
phys_state_abbr cris_string254_type,
phys_postal_code cris_string254_type,
phys_country_abbr cris_string254_type,
mail_addr cris_string254_type,
mail_city_name cris_string254_type,
mail_state_abbr cris_string254_type,
mail_postal_code cris_string254_type,
mail_country_abbr cris_string254_type,
us_ph_number cris_string254_type,
int_ph_country_no cris_string254_type,
int_ph_city_no cris_string254_type,
int_ph_local_no cris_string254_type,
us_fax_number cris_string254_type,
int_fax_country_no cris_string254_type,
int_fax_city_no cris_string254_type,
int_fax_local_no cris_string254_type,
business_desc cris_string254_type,
primary_sic cris_string254_type,
user_tag_ident cris_string254_type,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
lc_alt_sic1 cris_string254_type,
lc_alt_sic2 cris_string254_type,
PRIMARY KEY (mf_db_site, mf_db_id, manuf_code)
)
;

CREATE TABLE site_database(
db_site cris_string16_type NOT NULL,
db_id cris_uint_type NOT NULL,
user_tag_ident cris_string254_type,
name cris_string254_type,
mf_db_site cris_string16_type,
mf_db_id cris_uint_type,
manuf_code cris_uint_type,
gmt_last_updated cris_datetime_type,
last_upd_db_site cris_string16_type,
last_upd_db_id cris_uint_type,
rstat_type_code cris_ushort_type,
PRIMARY KEY (db_site, db_id)
)
;

Now I wanted to make it more readable and separate the identification
schema from the actual data by defining composite *_key_type types for
each table like this:

CREATE TYPE enterprise_type_key_type AS (
ent_db_key site_database_key_type
ent_type_code cris_uint_type
)
;

CREATE TABLE enterprise_type(
ent_type_key enterprise_type_key_type NOT NULL,
name cris_string254_type NOT NULL,
user_tag_ident cris_string254_type,
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
PRIMARY KEY (ent_type_key)
)
;

CREATE TYPE enterprise_key_type AS (
enterprise_id cris_uint_type
)
;

CREATE TABLE enterprise(
enterprise_key enterprise_key_type NOT NULL,
ent_type_key enterprise_type_key_type NOT NULL,
name cris_string254_type NOT NULL,
user_tag_ident cris_string254_type,
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
PRIMARY KEY (enterprise_key)
)
;

CREATE TYPE site_type_key_type AS (
st_db_key site_database_key_type,
st_type_code cris_uint_type
)
;

CREATE TABLE site_type(
st_type_key site_type_key_type NOT NULL,
name cris_string254_type NOT NULL,
user_tag_ident cris_string254_type,
mobile_yn cris_no_or_yes_type,
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
PRIMARY KEY (st_type_key)
)
;

CREATE TYPE site_type_child_key_type AS (
st_type_key site_type_key_type,
child_st_type_key site_type_key_type
)
;

CREATE TABLE site_type_child(
site_type_child_key site_type_child_key_type
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
PRIMARY KEY (site_type_child_key)
)
;

CREATE TYPE site_key_type AS (
site_code cris_string16_type,
)
;

CREATE TABLE site(
site_key site_key_type NOT NULL,
enterprise_key enterprise_key_type NOT NULL,
site_id cris_uint_type NOT NULL,
st_type_key site_type_key_type NOT NULL,
user_tag_ident cris_string254_type,
name cris_string254_type,
duns_number cris_uint_type,
template_yn cris_no_or_yes_type,
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
PRIMARY KEY (site_key)
)
;

CREATE TYPE manufacturer_key_type AS (
mf_db_key site_database_key_type,
manuf_code cris_uint_type
)
;

CREATE TABLE manufacturer(
manuf_key manufacturer_key_type NOT NULL,
manuf_trade_name cris_string254_type NOT NULL,
company_name cris_string254_type NOT NULL,
phys_addr cris_addr_data_type,
mail_addr cris_addr_data_type,
ph_number cris_telecom_data_type,
fax_number cris_telecom_data_type,
business_desc cris_string254_type,
primary_sic cris_string254_type,
user_tag_ident cris_string254_type,
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
lc_alt_sic1 cris_string254_type,
lc_alt_sic2 cris_string254_type,
PRIMARY KEY (manuf_key)
)
;

CREATE TYPE site_database_key_type AS (
db_site_key site_key_type,
db_id cris_uint_type
)
;

CREATE TABLE site_database(
db_key site_database_key_type NOT NULL,
user_tag_ident cris_string254_type,
name cris_string254_type,
manuf_key manufacturer_key_type,
last_upd_data cris_last_upd_data_type,
rstat_type_key row_status_type_key_type,
PRIMARY KEY (db_key)
)
;

The objective was to make the table definitions more readable (less
fields) and to simplify the work in case the identification schema
changes.

Nonsense? Am I nuts? Is that in fact totally useless? Or is there a
better (simpler) way to achieve this?

TIA,

Sincerely,

Wolfgang Keller