Cassandra,
Some more comments on data types if I may...
The use of VARCHAR over CHAR is far more advisable where the size of the
field is greater than a few characters and the length of such fields
varies... i.e. it's far more economical to use a null terminated string to
store a string (VARCHAR) than a fixed length string (CHAR). You save a lot
of db space.

Secondly we had a recent discussion of varhcars here and the concensus was
that varchar 255 was pretty much the economical limit - any larger and a
blob subtype text is in order... so drop the CHAR(500) fields to BLOBs and
make all CHAR field greater than say 4 or 5 in length to VARCHARs

You're obviously writing for Australia... is there a state here by the name
'OTH' ????
You might want to think some more about postcode being an integer and not a
varchar...
You want Country but only provide for Australian states (except 'Oth') and
if it's other countries are what you want you'll definitely lose out on an
integer postcode field.

Alan

-----Original Message-----
From: Cassandra Harley [mailto:cass.harley@...]
Sent: Monday, 24 February 2003 11:05 PM
To: ib-support@yahoogroups.com
Subject: RE: RE: [ib-support] Non-technical database question

>or a local desktop database application made with
>Access that meets the need you perceive to pass data around via the Windows
>clipboard.

Now when I began this whole endeavour, I considered using MSAccess, however
was discouraged from it due to Access not being of the quality of other
RDBMS (such as Firebird, or Interbase). So I took to the task of learning
about Firebird, which I was more then happy to do, as the skills picked up
creating this application (albeit small) would no doubt be helpful.
In any case, isn't MSAccess an RDBMS also? So why is it better to user
Access, then to use a product such as Firebird.
Is it because Firebird is more aimed at being a client/server product.
Whereas to emulate a client server with Access, you have to create a
database, then create a second 'database' which actually links to all the
tables in the first database. In this way you can distribute the second
database, so that the data is shared (because all data is in the first
database, and any number of copies of the second database may exist because
all that they do is point to the first).

>That lawn definitely needed a tractor.

ie not being limited in the future by lack of forethought at design time?
This is one of the reasons why I steered away from Access. I wanted to use
an RDBMS that would continue to meet any needs. Rather then use Access and
find out later that upgrading the application would require converting
database from Access to Firebird (or another product like it).

>You have to know what you want to do before the guides
>become useful by telling you how to do it.

True, I may be lacking in explaining to you what I am trying to achieve, but
I have created detailed DFD, and Entity-relationship diagrams, and am
working through this project with a fair idea of what I want to achieve. My
issues are largely with how to achieve them (ie the subject of all the
emails throughout the previous days). I posted a more detailed explanation
of my project in a post (in response to Svein a few minutes ago). I have
created metadata for what my database is to look like, and if it sheds any
light at all, I will post it below. However I am not expecting you (or
anyone) to go that deep into it all.

Thanks again,
Cassandra




/**************************************
CREATE DATABASE 'se_gdb_0_1_0.gdb' PAGE_SIZE 2048 USER 'ADMIN' PASSWORD
'admin';
COMMIT;

CREATE DOMAIN D_Dummy as CHAR(4);
CREATE DOMAIN D_Address AS CHAR(38) NOT NULL;
CREATE DOMAIN D_ABN AS INTEGER NOT NULL;
CREATE DOMAIN D_Branch AS INTEGER DEFAULT 001 NOT NULL;
CREATE DOMAIN D_Name AS CHAR(200);
CREATE DOMAIN D_Town AS CHAR(27) NOT NULL;
CREATE DOMAIN D_State AS CHAR(3) NOT NULL
CHECK (VALUE IN ('ACT', 'NSW', 'NT', 'QLD', 'SA', 'VIC', 'WA', 'TAS',
'OTH'));
CREATE DOMAIN D_Postcode AS INTEGER NOT NULL
CHECK ((VALUE > 0) AND (VALUE <= 9999));
CREATE DOMAIN D_Country AS CHAR(20);
CREATE DOMAIN D_Contact AS CHAR(20);
CREATE DOMAIN D_Phone AS INTEGER;
CREATE DOMAIN D_TFN AS INTEGER NOT NULL;
CREATE DOMAIN D_Date AS DATE;
CREATE DOMAIN D_Surname AS CHAR(30) NOT NULL;
CREATE DOMAIN D_Given AS CHAR(15);
CREATE DOMAIN D_Memo AS CHAR(500);
CREATE DOMAIN D_Monetary AS DECIMAL(10,2);
CREATE DOMAIN D_ID AS INTEGER;
CREATE DOMAIN D_Hour AS DECIMAL(4,2);

CREATE TABLE T_Employer
(C_Employer_ID D_ID NOT NULL,
C_ABN D_ABN NOT NULL,
C_Branch D_Branch NOT NULL,
CONSTRAINT U_ABN UNIQUE (C_ABN, C_Branch),
C_Name D_Name,
C_Trading D_Name,
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Postcode D_Postcode,
C_Country D_Country,
C_Contact D_Contact,
C_ContactPhone D_Phone,
C_ContactFax D_Phone,
PRIMARY KEY (C_Employer_ID)
);
COMMIT;

CREATE TABLE T_Owner
(C_Owner_ID D_ID NOT NULL,
C_ABN D_ABN,
C_Branch D_Branch,
C_Trading D_Name,
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Postcode D_Postcode,
C_Contact D_Contact,
C_ContactPhone D_Phone,
C_ContactFax D_Phone,
C_Memo D_Memo,
PRIMARY KEY (C_Owner_ID));
COMMIT;
CREATE INDEX I_Owner_Owner ON T_Owner (C_Owner_ID);
COMMIT;

CREATE TABLE T_Field
(C_Field_ID D_ID NOT NULL,
C_Owner_ID D_ID NOT NULL,
C_Name D_Name,
C_Location1 D_Name,
C_Location2 D_Name,
C_Memo D_Memo,
PRIMARY KEY (C_Field_ID),
FOREIGN KEY (C_Owner_ID) REFERENCES T_Owner (C_Owner_ID)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMIT;
CREATE INDEX I_Field_Owner ON T_Field (C_Owner_ID);
COMMIT;

CREATE TABLE T_Payslip
(C_Payslip_ID D_ID NOT NULL,
C_Tax_Withheld D_Monetary,
PRIMARY KEY (C_Payslip_ID));
COMMIT;
CREATE DESCENDING INDEX I_Payslip_Payslip ON T_Payslip (C_Payslip_ID);
COMMIT;

CREATE TABLE T_Employee
(C_Employee_ID D_ID NOT NULL,
C_Employer_ID D_ID NOT NULL,
C_TFN D_TFN,
C_DOB D_Date,
C_Surname D_Surname,
C_Firstname D_Given,
C_Secondname D_Given,
C_Address1 D_Address,
C_Address2 D_Address,
C_Town D_Town,
C_State D_State,
C_Postcode D_Postcode,
C_Country D_Country,
PRIMARY KEY (C_Employee_ID),
FOREIGN KEY (C_Employer_ID) REFERENCES T_Employer (C_Employer_ID)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMIT;
CREATE INDEX I_Employee_Surname ON T_Employee (C_Surname);
CREATE INDEX I_Employee_Firstname ON T_Employee (C_Firstname);
COMMIT;

CREATE TABLE T_Timesheet
(C_Timesheet_ID D_ID NOT NULL,
C_Field_ID D_ID NOT NULL,
C_Employee_ID D_ID NOT NULL,
C_Date D_Date NOT NULL,
CONSTRAINT U_Timesheet UNIQUE (C_Field_ID, C_Employee_ID, C_Date),
C_Hours D_Hour,
C_Rate D_Monetary,
C_Payslip_ID D_ID,
PRIMARY KEY (C_Timesheet_ID),
FOREIGN KEY (C_Field_ID) REFERENCES T_Field (C_Field_ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (C_Employee_ID) REFERENCES T_Employee (C_Employee_ID)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (C_Payslip_ID) REFERENCES T_Payslip (C_Payslip_ID)
ON UPDATE CASCADE
ON DELETE SET NULL);
COMMIT;
CREATE INDEX I_Timesheet_Timesheet ON T_Timesheet (C_Timesheet_ID);
CREATE INDEX I_Timesheet_Field ON T_Timesheet (C_Field_ID);
CREATE INDEX I_Timesheet_Employee ON T_Timesheet (C_Employee_ID);
CREATE INDEX I_Timesheet_Date ON T_Timesheet (C_Date);
CREATE INDEX I_Timesheet_Payslip ON T_Timesheet (C_Payslip_ID);
COMMIT;



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/