From: | Lewis Bergman <lbergman(at)abi(dot)tconline(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | table design |
Date: | 2002-02-01 18:22:14 |
Message-ID: | 200202011822.g11IMEn02262@lewis.abi.tconline.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2002-02-01 21:12:45 | Re: table design |
Previous Message | Josh Berkus | 2002-02-01 18:15:59 | Re: How many clients does pg allow? |