Search Postgresql Archives

Re: Composite types for composite primary/foreign keys?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux