Rose::DB::Object::Loader - Automatically create Rose::DB::Object subclasses based on database table

       John C. Siracusa (siracusa@gmail.com)

newPARAMS
           Returns  a  new Rose::DB::Object::Loader constructed according to PARAMS, where PARAMS are name/value
           pairs.  Any object method is a valid parameter name.

       Rose::DB::Object::Loader will automatically create Rose::DB::Object subclasses for all the tables in a
       database.  It will configure column data types, default values, primary keys, unique keys, and foreign
       keys.  It can also discover and set up inter-table relationships.  It uses Rose::DB::Object's auto-
       initialization capabilities to do all of this.

       To do its work, the loader needs to know how to connect to the database.  This information can be
       provided in several ways.  The recommended practice is to set up Rose::DB according to the instructions
       in the Rose::DB::Tutorial, and then pass a Rose::DB-derived object or class name to the loader.  The
       loader will also accept traditional DBI-style connection information: DSN, username, password, etc.

       Once the loader object is configured, the make_classes method does all the work.  It takes a few options
       specifying which tables to make classes for, whether or not to make manager classes for each table, and a
       few other options.  The convention manager is used to convert table names to class names, generate
       foreign key and relationship method names, and so on.  The result of this process is a suite of
       Rose::DB::Object (and Rose::DB::Object::Manager) subclasses ready for use.

       Rose::DB::Object::Loader inherits from, and follows the conventions of, Rose::Object.  See the
       Rose::Object documentation for more information.

       Database schema information is extracted using DBI's schema interrogation methods, which dutifully report
       exactly how the database describes itself.  In some cases, what the database reports about a particular
       table may not exactly match what you specified in your table definition.

       The most egregious offender is (surprise!) MySQL, which, to give just one example, tends to offer up
       empty string default values for non-null character columns.  That is, if you write a table definition
       like this:

           CREATE TABLE widgets
           (
             id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
             name VARCHAR(64) NOT NULL
           );

       and then interrogate it using DBI, you will find that the "name" column has a default value (as reflected
       in the "COLUMN_DEF" column returned by DBI's column_info() method) of '' (i.e., an empty string).  In
       other words, it's as if your table definition was this instead:

           CREATE TABLE widgets
           (
             id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
             name VARCHAR(64) NOT NULL DEFAULT ''
           );

       MySQL is full of such surprises, and it's not the only database to do such things.  Consult the
       documentation for your database (or do a Google search for "<mydbname> gotchas") for the gory details.

       To work around these kinds of problems, try the pre_init_hook feature.  For example, in your
       pre_init_hook subroutine you could walk over the list of columns for each class, eliminating all the
       empty string default values (i.e., changing them to undef instead).

       Copyright  (c)  2010  by  John C. Siracusa.  All rights reserved.  This program is free software; you can
       redistribute it and/or modify it under the same terms as Perl itself.

perl v5.36.0                                       2022-10-14                      Rose::DB::Object::Loader(3pm)

       Rose::DB::Object::Loader - Automatically create Rose::DB::Object subclasses based on database table
       definitions.

base_classCLASS
           This is an alias for the base_classes method.

       base_classes[CLASS|ARRAYREF]
           Get or set the list of base classes to  use  for  the  Rose::DB::Object  subclasses  created  by  the
           make_classes method.  The argument may be a class name or a reference to an array of class names.  At
           least one of the classes should inherit from Rose::DB::Object.

           Returns  a  list  (in list context) or reference to an array (in scalar context) of base class names.
           Defaults to a dynamically-generated Rose::DB::Object subclass name.

       class_prefix[PREFIX]
           Get or set the prefix affixed to all class names created  by  the  make_classes  method.   If  PREFIX
           doesn't end in "::", it will be added automatically.

       convention_manager[CLASS|MANAGER]
           Get or set the Rose::DB::Object::ConventionManager-derived class name or object to be used during the
           auto-initialization   process  for  each  class  created  by  the  make_classes  method.   Returns  a
           Rose::DB::Object::ConventionManager-derived     object,     which     defaults     to      a      new
           Rose::DB::Object::ConventionManager object.

           Unless  this  attribute  is explicitly set or fetched before the call to the make_classes method, the
           convention manager object used by make_classes will be produced  by  calling  the  convention_manager
           method of the metadata object of the first (left-most) base class.

       db[DB]
           Get  or set the Rose::DB-derived object used to connect to the database.  This object will be used by
           the make_classes method when extracting information from the database.  It will also be used  as  the
           prototype for the db object used by each Rose::DB::Object subclass to connect to the database.

           Setting  this attribute also sets the db_class attributes, overwriting any previously existing value,
           and sets the  db_dsn value to undef.

       db_catalog[CATALOG]
           Get or set the catalog for the database connection.

       db_class[CLASS]
           Get or set the name of the Rose::DB-derived class used by the make_classes method to construct  a  db
           object if one has not been set via the method of the same name.

           Setting this attribute sets the db attribute to undef unless its class is the same as CLASS.

       db_dsn[DSN]
           Get  or  set the DBI-style Data Source Name (DSN) used to connect to the database.  This will be used
           by the make_classes method when extracting  information  from  the  database.   The  Rose::DB-derived
           objects  used  by  each Rose::DB::Object subclass to connect to the database will be initialized with
           this DSN.

           Setting this attribute immediately sets the dsn of the db attribute, if it is defined.

       db_options[HASHREF]
           Get or set the options used to connect to the database.

       db_password[PASSWORD]
           Get or set the password used to connect to the database.

       db_schema[SCHEMA]
           Get or set the schema for the database connection.

       db_username[USERNAME]
           Get or set the username used to connect to the database.

       exclude_tables[REGEX|ARRAYREF]
           Get or set a regular expression or reference to an array of table names to exclude.  Table names that
           match REGEX or are contained in ARRAYREF will be skipped by default during calls to the  make_classes
           method.  Tables without primary keys are automatically (and always) skipped.

           Table  names  are  compared  to  REGEX  and  the  names in ARRAYREF in a case-insensitive manner.  To
           override this in the case of the REGEX, add "(?-i)" to the front of the REGEX.   Otherwise,  use  the
           filter_tables method instead.

       filter_tablesCODEREF
           Get  or  set  a reference to a subroutine that takes a single table name argument and returns true if
           the table should be processed by default during calls to the make_classes method, false if the  table
           should be skipped.  The $_ variable will also be set to the table name before the call to CODEREF.

           This attribute should not be combined with the exclude_tables or include_tables attributes.

       force_lowercase[BOOL]
           Get  or  set  a boolean value that indicates whether or not metadata entity names should be forced to
           lowercase even when the related entity (e.g., table or column  name)  is  uppercase  or  mixed  case.
           ("Metadata  entities"  are  thing  like columns, relationships, and foreign keys.)  The default value
           undef.

       generate_manager_class_nameCLASS[,CM]
           Given  the   name   of   a   Rose::DB::Object-derived   class,   returns   a   class   name   for   a
           Rose::DB::Object::Manager-derived class to manage such objects.  The default implementation calls the
           auto_manager_class_name  method  on the convention manager object passed as the optional CM argument,
           or returned from the convention_manager method if a CM argument is not passed.

       include_tables[REGEX|ARRAYREF]
           Get or set a regular expression or reference to an array of table names to include.  Table names that
           do not match REGEX or are not contained in ARRAYREF will be skipped by default during  calls  to  the
           make_classes method.  Tables without primary keys are automatically (and always) skipped.

           Table  names  are  compared  to  REGEX  and  the  names in ARRAYREF in a case-insensitive manner.  To
           override this in the case of the REGEX, add "(?-i)" to the front of the REGEX.   Otherwise,  use  the
           filter_tables method instead.

       include_predicated_unique_indexesBOOL
           Get  or  set a boolean value that will be assigned to the include_predicated_unique_indexes attribute
           of the Rose::DB::Object::Metadata object for each class created  by  the  make_classes  method.   The
           default value is false.

       include_viewsBOOL
           If  true,  database  views will also be processed by default during calls to the make_classes method.
           Defaults to false.

       make_classes[PARAMS]
           Automatically create Rose::DB::Object and (optionally) Rose::DB::Object::Manager subclasses for  some
           or  all of the tables in a database.  The class creation process is controlled by the loader object's
           attributes.  Optional name/value pairs passed to this method  may  override  some  of  those  values.
           Valid PARAMS are:

           db[DB]
               The  Rose::DB-derived  object  used to connect to the database.  This object will also be used as
               the prototype for the db object used by each Rose::DB::Object subclass created by  this  call  to
               make_classes.  Defaults to the value of the loader object's db attribute.

           db_class[CLASS]
               The  name of the Rose::DB-derived class used to construct a db object if one has not been set via
               the parameter or object attribute of the same name.  Defaults to the value of the loader object's
               db_class attribute.

           include_tables[REGEX|ARRAYREF]
               Table names that do not match REGEX or are not contained in ARRAYREF will be  skipped.   Defaults
               to  the  value  of the loader object's include_tables attribute.  Tables without primary keys are
               automatically (and always) skipped.

               Table names are compared to REGEX and the names in ARRAYREF in  a  case-insensitive  manner.   To
               override  this  in  the case of the REGEX, add "(?-i)" to the front of the REGEX.  Otherwise, use
               the "filter_tables" parameter instead.

           exclude_tables[REGEX|ARRAYREF]
               Table names that match REGEX or are contained in ARRAYREF will be skipped.  Defaults to the value
               of the loader object's exclude_tables attribute.  Tables without primary keys  are  automatically
               (and always) skipped.

               Table  names  are  compared  to REGEX and the names in ARRAYREF in a case-insensitive manner.  To
               override this in the case of the REGEX, add "(?-i)" to the front of the  REGEX.   Otherwise,  use
               the "filter_tables" parameter instead.

           filter_tablesCODEREF
               A reference to a subroutine that takes a single table name argument and returns true if the table
               should  be  processed,  false  if  it should be skipped.  The $_ variable will also be set to the
               table name before the call.  This parameter cannot  be  combined  with  the  "exclude_tables"  or
               "include_tables" options.

               Defaults  to  the  value  of  the loader object's filter_tables attribute, provided that both the
               "exclude_tables" and "include_tables" values are undefined.   Tables  without  primary  keys  are
               automatically skipped.

           force_lowercaseBOOL
               A boolean value that indicates whether or not metadata entity names should be forced to lowercase
               even  when  the  related  entity is uppercase or mixed case.  ("Metadata entities" are thing like
               columns, relationships, and foreign keys.)

               If this parameter is omitted and if the loader object's force_lowercase attribute is not defined,
               then the value is chosen based on the database currently being  examined.   If  the  database  is
               Oracle, then it defaults to true.  Otherwise, it defaults to false.

               The final value is propagated to the convention manager attribute of the same name.

           include_predicated_unique_indexesBOOL
               This   value   will  be  assigned  to  the  include_predicated_unique_indexes  attribute  of  the
               Rose::DB::Object::Metadata object for each class created by this method.  Defaults to  the  value
               of the loader object's include_predicated_unique_indexes attribute.

           include_viewsBOOL
               If  true,  database  views  will also be processed.  Defaults to the value of the loader object's
               include_views attribute.

           post_init_hook[CODEREF|ARRAYREF]
               A reference to a subroutine or a reference to an array of code references  that  will  be  called
               just  after  each Rose::DB::Object-derived class is initialized.  Each referenced subroutine will
               be passed the class's metadata object plus any arguments to the initialize method.   Defaults  to
               the value of the loader object's post_init_hook attribute.

           pre_init_hook[CODEREF|ARRAYREF]
               A  reference  to  a  subroutine or a reference to an array of code references that will be called
               just before each Rose::DB::Object-derived class is initialized.  Each referenced subroutine  will
               be  passed  the class's metadata object plus any arguments to the initialize method.  Defaults to
               the value of the loader object's pre_init_hook attribute.

           require_primary_keyBOOL
               If true, then any table that does not have a primary key will be skipped.  Defaults to the  value
               of the loader object's require_primary_key attribute.  Note that a Rose::DB::Object-derived class
               based  on a table with no primary key will not function correctly in all circumstances.  Use this
               feature at your own risk.

           warn_on_missing_pkBOOL
               This is an alias for the "warn_on_missing_primary_key" parameter.

           warn_on_missing_primary_keyBOOL
               If true, then any table that does not have a primary key will trigger a warning.

               If "require_primary_key" is false and the loader object's  warn_on_missing_primary_key  attribute
               is  undefined,  or if the "warn_on_missing_primary_key" parameter is set to an undefined value or
               is not passed to the make_classes call at  all,  then  "warn_on_missing_primary_key"  is  set  to
               false.   Otherwise,  it  defaults to the value of the loader object's warn_on_missing_primary_key
               attribute.  Note that a Rose::DB::Object-derived class based on a table with no primary key  will
               not function correctly in all circumstances.

               These  complicated  defaults  are  intended  to honor the intentions of the "require_primary_key"
               attribute/parameter.  If not requiring primary keys and no explicit decision has been made  about
               whether to warn about missing primary keys, either in the parameters to the  make_classes call or
               in the loader object itself, then we don't warn about missing primary keys.  The idea is that not
               requiring primary keys is a strong indication that their absence is not worth a warning.

           with_foreign_keysBOOL
               If true, set up foreign key metadata for each Rose::DB::Object-derived.  Defaults to the value of
               the loader object's with_foreign_keys attribute.

           with_managersBOOL
               If  true,  create  Rose::DB::Object::Manager-derived  manager  classes  for each Rose::DB::Object
               subclass.  Defaults to the value of the loader object's with_managers attribute.

               The manager class name is determined by passing the Rose::DB::Object-derived class  name  to  the
               generate_manager_class_name method.

               The  Rose::DB::Object  subclass's  metadata  object's  make_manager_class  method will be used to
               create the manager class.  It will be  passed  the  return  value  of  the  convention  manager's
               auto_manager_base_name method as an argument.

           with_relationships[BOOL|ARRAYREF]
               A  boolean  value  or  a  reference  to  an array of relationship type names.  If set to a simple
               boolean value, then all types of relationships will be considered when making classes.  If set to
               a list of relationship type names, then only relationships of those  types  will  be  considered.
               Defaults to the value of the loader object's with_relationships attribute.

           with_unique_keysBOOL
               If  true, set up unique key metadata for each Rose::DB::Object-derived.  Defaults to the value of
               the loader object's with_unique_keys attribute.

           Any remaining name/value parameters will be passed on to the call to auto_initialize used to  set  up
           each   class.    For  example,  to  ask  the  loader  not  to  create  any  relationships,  pass  the
           "with_relationships" parameter with a false value.

               $loader->make_classes(with_relationships => 0);

           This parameter will be passed on to the  auto_initialize  method,  which,  in  turn,  will  pass  the
           parameter    on    to    its   own   call   to   the   auto_init_relationships   method.    See   the
           Rose::DB::Object::Metadata documentation for more information on these methods.

           Each Rose::DB::Object subclass will be created according to the "best  practices"  described  in  the
           Rose::DB::Object::Tutorial.  If a base class is not provided, one (with a dynamically generated name)
           will  be  created  automatically.   The  same  goes for the db object.  If one is not set, then a new
           (again, dynamically named) subclass of Rose::DB, with its own private data source registry,  will  be
           created automatically.

           This  method  returns  a list (in list context) or a reference to an array (in scalar context) of the
           names of all the classes that were created.  (This list will include manager class names as well,  if
           any were created.)

       make_modules[PARAMS]
           Automatically  create Rose::DB::Object and (optionally) Rose::DB::Object::Manager subclasses for some
           or all of the tables in a database, then create Perl module (*.pm) files for each class.

           This method calls make_classes to make the actual classes.

           Note: If you are trying to regenerate a set  of  module  files  that  already  exist  in  the  target
           "module_dir",  please make sure that this "module_dir" is not in your @INC path.  (That is, make sure
           it is not in the set of paths that perl will search when looking for module files in  response  to  a
           "use"  or  "require"  statement.)   More  generally, you must make sure that existing versions of the
           modules you are attempting to generate are not in your @INC path.

           (If you do not do this, when make_classes makes a class and looks for a related class, it  will  find
           and  load  the previously generated ".pm" file, which will then cause make_classes to skip that class
           later when it sees that it already exists in memory.  And if make_classes skips it, make_modules will
           never see it and therefore will never regenerate the ".pm" file.)

           This method takes all of the same parameters as make_classes, with several additions:

           module_dirDIR
               The path to the directory where the Perl module files will be created.  For example, given a  DIR
               of  "/home/john/lib",  the  Perl  module  file for the class "My::DB::Object" would be located at
               "/home/john/lib/My/DB/Object.pm".

               Defaults to the value of the loader object's module_dir attribute.  If the  module_dir  attribute
               is  also undefined, then the current working directory (as determined by a call to cwd()) is used
               instead.

           module_preamble[SCALAR|CODE]
               If defined as a scalar, inserts the contents of the variable into the auto-generated file  before
               any  of  the  auto-generated  class  information.  If provided as a code ref, calls the indicated
               function, passing the metadata object as a parameter.  (The metadata object that belongs  to  the
               "object_class" and the Rose::DB::Object::Manager-derived class name are passed if the module is a
               Rose::DB::Object::Manager-derived  class.)  The returned value of the function is inserted as the
               preamble text.

               Defaults to the value of the loader object's module_preamble attribute.

           module_postamble[SCALAR|CODE]
               If defined as a scalar, inserts the contents of the variable into the auto-generated  file  after
               any  of  the  auto-generated  class  information.  If provided as a code ref, calls the indicated
               function, passing the metadata object as a parameter.  (The metadata object that belongs  to  the
               "object_class" and the Rose::DB::Object::Manager-derived class name are passed if the module is a
               Rose::DB::Object::Manager-derived  class.)  The returned value of the function is inserted as the
               postamble text.

               Defaults to the value of the loader object's module_postamble attribute.

       module_dir[DIR]
           Get or set the path to the directory where make_modules will create  its  Perl  modules  files.   For
           example,    given    a    DIR    of    "/home/john/lib",   make_modules   would   create   the   file
           "/home/john/lib/My/DB/Object.pm" for the class "My::DB::Object".

       module_preamble[SCALAR|CODE]
           If defined as a scalar, inserts the contents of the variable into the auto-generated file before  any
           of  the  auto-generated  class information.  If provided as a code ref, calls the indicated function,
           passing the metadata object as a parameter.  (The metadata object that belongs to the  "object_class"
           and   the   Rose::DB::Object::Manager-derived   class   name   are   passed   if   the  module  is  a
           Rose::DB::Object::Manager-derived class.)  The returned value of the  function  is  inserted  as  the
           preamble text.

       module_postamble[SCALAR|CODE]
           If  defined  as a scalar, inserts the contents of the variable into the auto-generated file after any
           of the auto-generated class information.  If provided as a code ref, calls  the  indicated  function,
           passing  the metadata object as a parameter.  (The metadata object that belongs to the "object_class"
           and  the  Rose::DB::Object::Manager-derived  class   name   are   passed   if   the   module   is   a
           Rose::DB::Object::Manager-derived  class.)   The  returned  value  of the function is inserted as the
           postamble text.

       pre_init_hook[CODE]
           Get or set a reference to a subroutine to be called just before each  Rose::DB::Object-derived  class
           is  initializeed  within  the make_classes method.  The subroutine will be passed the class's metdata
           object as an argument.

       require_primary_keyBOOL
           Get or set a boolean value that determines whether or not the make_classes method will skip any table
           that does not have a primary key will be skipped.  Defaults to true.

           Note that a Rose::DB::Object-derived class based on a table with no primary  key  will  not  function
           correctly in all circumstances.  Use this feature at your own risk.

       warn_on_missing_pkBOOL
           This is an alias for the warn_on_missing_primary_key method.

       warn_on_missing_primary_keyBOOL
           Get or set a boolean value that determines whether or not the make_classes method will emit a warning
           when it encounters a table that does not have a primary key.  Defaults to undefined.

       with_foreign_keysBOOL
           If  true,  the make_classes method will set up foreign key metadata for each Rose::DB::Object-derived
           class it creates.  Defaults to true.

       with_managers[BOOL]
           If true, the make_classes method will create Rose::DB::Object::Manager-derived  manager  classes  for
           each Rose::DB::Object subclass by default.  Defaults to true.

           The  manager  class  name  is  determined  by  passing the Rose::DB::Object-derived class name to the
           generate_manager_class_name method.

           The Rose::DB::Object subclass's metadata object's make_manager_class method will be  used  to  create
           the   manager   class.    It   will   be   passed  the  return  value  of  the  convention  manager's
           auto_manager_base_name method as an argument.

       with_relationships[BOOL|ARRAYREF]
           A boolean value or a reference to an array of relationship type names.  If set to  a  simple  boolean
           value, then the make_classes method will consider all types of relationships when making classes.  If
           set  to  a list of relationship type names, then only relationships of those types will be considered
           by  make_classes.  Defaults to true.

       with_unique_keysBOOL
           If true, the make_classes method will set up unique key metadata  for  each  Rose::DB::Object-derived
           class it creates.  Defaults to true.

       manager_base_classCLASS
           This is an alias for the manager_base_classes method.

       manager_base_classes[CLASS|ARRAYREF]
           Get  or  set  the list of base classes to use for the Rose::DB::Object::Manager subclasses created by
           the make_classes method.  The argument may be a class name or a reference to an array of class names.
           At least one of the classes should inherit from Rose::DB::Object::Manager.

           Returns a list (in list context) or reference to an array (in scalar context) of  base  class  names.
           Defaults to Rose::DB::Object::Manager.

       Sample database schema:

         CREATE TABLE vendors
         (
           id    SERIAL NOT NULL PRIMARY KEY,
           name  VARCHAR(255) NOT NULL,

           UNIQUE(name)
         );

         CREATE TABLE products
         (
           id      SERIAL NOT NULL PRIMARY KEY,
           name    VARCHAR(255) NOT NULL,
           price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,

           vendor_id  INT REFERENCES vendors (id),

           status  VARCHAR(128) NOT NULL DEFAULT 'inactive'
                     CHECK(status IN ('inactive', 'active', 'defunct')),

           date_created  TIMESTAMP NOT NULL DEFAULT NOW(),
           release_date  TIMESTAMP,

           UNIQUE(name)
         );

         CREATE TABLE prices
         (
           id          SERIAL NOT NULL PRIMARY KEY,
           product_id  INT NOT NULL REFERENCES products (id),
           region      CHAR(2) NOT NULL DEFAULT 'US',
           price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,

           UNIQUE(product_id, region)
         );

         CREATE TABLE colors
         (
           id    SERIAL NOT NULL PRIMARY KEY,
           name  VARCHAR(255) NOT NULL,

           UNIQUE(name)
         );

         CREATE TABLE product_color_map
         (
           product_id  INT NOT NULL REFERENCES products (id),
           color_id    INT NOT NULL REFERENCES colors (id),

           PRIMARY KEY(product_id, color_id)
         );

       To start, make a Rose::DB::Object::Loader object, specifying the database connection information and an
       optional class name prefix.

         $loader =
           Rose::DB::Object::Loader->new(
             db_dsn       => 'dbi:Pg:dbname=mydb;host=localhost',
             db_username  => 'someuser',
             db_password  => 'mysecret',
             db_options   => { AutoCommit => 1, ChopBlanks => 1 },
             class_prefix => 'My::Corp');

       It's even easier to specify the database information if you've set up Rose::DB (say, by following the
       instructions in Rose::DB::Tutorial).  Just pass a Rose::DB-derived object pointing to the database you're
       interested in.

         $loader =
           Rose::DB::Object::Loader->new(
             db           => My::Corp::DB->new('main'),
             class_prefix => 'My::Corp');

       Finally, automatically create Rose::DB::Object subclasses for all the tables in the database.  All it
       takes is one method call.

         $loader->make_classes;

       Here's what you get for your effort.

         $p = My::Corp::Product->new(name => 'Sled');

         $p->vendor(name => 'Acme');

         $p->prices({ price => 1.23, region => 'US' },
                    { price => 4.56, region => 'UK' });

         $p->colors({ name => 'red'   },
                    { name => 'green' });

         $p->save;

         $products =
           My::Corp::Product::Manager->get_products_iterator(
             query           => [ name => { like => '%le%' } ],
             with_objects    => [ 'prices' ],
             require_objects => [ 'vendor' ],
             sort_by         => 'vendor.name');

         $p = $products->next;

         print $p->vendor->name; # Acme

         # US: 1.23, UK: 4.56
         print join(', ', map { $_->region . ': ' . $_->price } $p->prices);

       See the Rose::DB::Object and Rose::DB::Object::Manager documentation for learn more about the features
       these classes provide.

       The contents of the database now look like this.

         mydb=# select * from products;
          id |  name  | price | vendor_id |  status  |       date_created
         ----+--------+-------+-----------+----------+-------------------------
           1 | Sled 3 |  0.00 |         1 | inactive | 2005-11-19 22:09:20.7988

         mydb=# select * from vendors;
          id |  name
         ----+--------
           1 | Acme 3

         mydb=# select * from prices;
          id | product_id | region | price
         ----+------------+--------+-------
           1 |          1 | US     |  1.23
           2 |          1 | UK     |  4.56

         mydb=# select * from colors;
          id | name
         ----+-------
           1 | red
           2 | green

         mydb=# select * from product_color_map;
          product_id | color_id
         ------------+----------
                   1 |        1
                   1 |        2