First off, this is my first posting and first experience with pqsql.
I have used MySQL for a long time but this db is full of *really* important
stuff so I thought pgsql iwas better suited.
I am not sure how to handle this situation as I have never designed a db
before, just used other's designs.
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
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.
-- +---------------------------------------------------------
-- | TABLE: tbPackage
-- +---------------------------------------------------------
CREATE TABLE tbPackage
(
pkPackageID serial NOT NULL,
pkName varchar(50) NOT NULL,
Description varchar NOT NULL,
Available boolean,
fkServiceID varchar NOT NULL,
Price money NOT NULL,
PRIMARY KEY (pkPackageID,pkName)
);
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';
--
Lewis Bergman
Texas Communications
4309 Maple St.
Abilene, TX 79602-8044
915-695-6962 ext 115