Drew Jensen wrote:
OK - another question for some group wisdom..
Having created the second cut of the schema for the database:
tutorial_example_clinic.odb
The decision is now about lookup tables - To use an integer ID or not...
For instance here are two lookup tables:
-- PhoneType is a lookup table
-- for use in FK relations to
-- PatientPhone and TherapistPhone
-- tables
--
CREATE TABLE "PhoneType" (
"PhoneDesc" VARCHAR(10) NOT NULL PRIMARY KEY
);
-- Initial data for PhoneType table
--
INSERT INTO "PhoneType" VALUES ( 'Home' );
INSERT INTO "PhoneType" VALUES ( 'Work' );
INSERT INTO "PhoneType" VALUES ( 'Cell' );
INSERT INTO "PhoneType" VALUES ( 'FAX' );
I would use a char(4) field (fixed length) as a primary key. It would
serve both as an ID and at the same time would have been informational,
and I think fix length strings are faster than varchars.
-- TransactionType is a lookup table
-- used by the patient billing
-- funcitons
-- It is referecned as a FK
-- from the Account table
--
CREATE TABLE "TransactionType" (
"Tran_Type_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)
NOT NULL PRIMARY KEY,
"Tran_Desc" VARCHAR(50)
"Debit" CHAR(6) NOT NULL,
CONSTRAINT CK_DBT CHECK("Debit" IN ('DEBIT', 'CREDIT'))
);
--
-- Intial values for TransactionType
-- table
--
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Session',
'DEBIT' );
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Missed
Session', 'DEBIT' );
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Payment',
'CREDIT' );
INSERT INTO "TransactionType" ("Tran_Desc", "Debit" ) VALUES( 'Returned
Payment', 'DEBIT' );
The same goes here. I generally avoid to use varchars in place where
they can be foreign keys to other tables. I use fix char fields or
integers. If i need a longer description i put it in another field.
--
Manolis Christodoulou MD
[EMAIL PROTECTED]
http://mobi-doc.com
http://palmpowereddoctor.blogspot.com
For document attachments please prefer OASIS/ISO OpenDocument Format.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
