table design

Поиск
Список
Период
Сортировка
Искать
От
Lewis Bergman
Тема
table design
Дата
Msg-id
200202011822.g11IMEn02262@lewis.abi.tconline.net
Список
Дерево обсуждения
table design Lewis Bergman <lbergman@abi.tconline.net>
Re: table design Oliver Elphick <olly@lfix.co.uk>
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
В списке pgsql-novice по дате отправления
От: Josh Berkus
Дата:
От: Oliver Elphick
Дата:
Сообщение: Re: table design
FAQ