Re: table design - Mailing list pgsql-novice

On Fri, 2002-02-01 at 18:22, Lewis Bergman wrote:
...
> I have a db that will be used for inventory like purposes in that it will
> contain descriptions of things we can sell.
>
> The part that is confusing me is this:
> I want tbPackage to have names of packages which consist of different rows
         ^^^^^^^^^

Don't use capitalisation to separate words; PostgreSQL converts
everything to lower case.  Use underscores (tb_package) (or drop the
"tb" prefix altogether.  It doesn't add to the meaning; it is more
likely to be confusing, and similarly for pk..., fk....)

> from tbService. The indexed field pkName should be unique. So a package might
> have something like:
> basic package:
>     mail   -----------------|
>     web                          +-- all these from tbService
>     dialup                       |
>     m-f 8-5 support  ----|
>
> If tbPackage.pkName is unique, how am I going to insert different rows to
> "build" the package? Do I need another table? I am really confused here. If
> someone could suggest a solution, and maybe a book I could buy, I would be
> very grateful.

It sounds as though you need a package_service table to let you have
multiple services per package:

CREATE TABLE table_service (
   package         INTEGER NOT NULL REFERENCES tbPackage(pkPackageID),
   service         TEXT NOT NULL REFERENCES tbService(serviceID),
   PRIMARY KEY (package, service)
)

The primary key definition ensures that services cannot be duplicated
within a package.  The foreign key references ensure that you don't
enter invalid package ids or service names; to use them you have to
define the corresponding fields in tbPackage and tbService as primary
keys for their tables (see comment below on tbPackage's primary key).

> -- +---------------------------------------------------------
> -- | TABLE: tbPackage
> -- +---------------------------------------------------------
> CREATE TABLE tbPackage
> (
>   pkPackageID serial NOT NULL,
>   pkName varchar(50) NOT NULL,

pkName varchar(50) NOT NULL UNIQUE,  -- then you needn't separately
                                     -- define an index
>   Description varchar NOT NULL,
>   Available boolean,
>   fkServiceID varchar NOT NULL,

Drop this line; service ids will be in the cross-referencing table.

>   Price money NOT NULL,
>   PRIMARY KEY (pkPackageID,pkName)

If pkName is unique, does this mean that pkPackageID is not?  Seeing you
have it defined as SERIAL, it is presumably meant to be unique, so it
sounds as though pkPackageID alone should be the primary key.  It is
much easier to use a small number as a key (for foreign key references)
than a large piece of text.  As you have it now, any foreign key
reference to this table will have to specify both fields.

> );
> COMMENT ON TABLE tbPackage IS 'Build packages, which are assigned to
> customers, from services.';
> COMMENT ON COLUMN tbPackage.pkPackageID IS 'The auto generated packae number';
> COMMENT ON COLUMN tbPackage.pkName IS 'The unique name of the package';
> COMMENT ON COLUMN tbPackage.Description IS 'The services included in the
> package along with anything else pertinent.';
> COMMENT ON COLUMN tbPackage.Available IS 'Is the package currently sellable?';
> COMMENT ON COLUMN tbPackage.fkServiceID IS 'Packages must contain only valid
> services.';
> COMMENT ON COLUMN tbPackage.Price IS 'Price of package';
> CREATE INDEX Name ON tbPackage USING BTREE
> (
>   pkName
> );
>
> COMMENT ON INDEX Name IS 'Speed searches';

You don't need to create this index if you specify that pkName is unique
- that automatically creates an index.  The way you have it defined at
the moment, you have an index on a non-unique field, which is valid but
not what you want.
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "And be not conformed to this world; but be ye
      transformed by the renewing of your mind, that ye may
      prove what is that good, and acceptable, and perfect,
      will of God."             Romans 12:2