At 02:52 PM 26-06-01 +0000, I wrote:

> > First off, I can't quite sort out these relationships. Do you have
>separate tables for Students and Contracts? The two foreign keys
>would suggest that you do...and can one student have multiple
>contracts? You mentioned that you have a student_contract_link table.
> >
> > Could you please show us the structures of these three other tables?

Now I should interpolate that this list is for IB Objects, and intensive tutoring in database design and SQL are really somewhat OFF-TOPIC. So I'll start by recommending that you really get your head around SQL, both the full power of the language and the InterBase implementation. For the former, the latest edition of SQL for Dummies is a good place to start, as it's cheap and, especially, as the author uses Delphi and InterBase examples. But do print out, or keep on the desktop, the LangRef from the IB documentation set.

Joins and sub-selects are not "enemies to be avoided", they are the meat and drink of client/server programming. IB is, after all, a **relational** database!!

>Ok. Here they are (I've trimmed out a few unnecessary columns and
>constraint stuff)
>
>Each company has some students. Each company can have one or more
>contracts, with one or more students per a contract. Each teacher is
>attached to the overall agency for whom the app is targeted. The
>attendance records are filled in by the teacher . (There can be more
>than one teacher used to fulfill a contract.) The hours that get
>plugged into the grid (student hours grid for teacher/month/contract)
>is the total that the student was taught by that teacher (for that
>month and contract).

OK, now, your "grid" (containing a dataset at the lowest level in your structure, containing 0 to many rows) represents the finest granularity of the business problem. Above that level, you have to be able to identify uniquely, and through other relations, each student and each teacher who is "eligible" to exist in an Attendance row.

Now, just follow me through on these structures and I'll comment on them:

>These are the three main chaps :
>
>CREATE TABLE COMPANY (
> COMPANY_ID INTEGER NOT NULL,
>--->>> NAME CHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
>);
>
>CREATE TABLE STUDENT (
> STUDENT_ID INTEGER NOT NULL,
> COMPANY_ID INTEGER NOT NULL,
> --->>> NAME CHAR(40) CHARACTER SET ISO8859_1 NOT NULL,
>);

Possibly it's a mistake to tie a student up in a 1:1 relationship with a company. What do you do with this student if he moves to another company and wants to continue the course? To resolve this, create a table of students and make this table into an intersection table with its own primary key, viz.

CREATE TABLE STUDENT (
STUDENT_ID INTEGER NOT NULL PRIMARY KEY,
STUDENT_NAME etc.);

I recommend avoiding NAME as a column name, as it's a keyword in Delphi, which is bound to bite you sooner or later; and, because you have such a highly-layered structure, you are going to make more work for yourself than you need by duplicating a column name all over the place. It's fairly unlikely that you can avoid joined queries that are going to return NAME from two or more tables...

then...

CREATE TABLE STUDENT_COMPANY (
STUDENT_COMPANY_ID INTEGER NOT NULL PRIMARY KEY,
STUDENT_ID INTEGER NOT NULL,
COMPANY_ID NOT NULL);

>CREATE TABLE TEACHERS (
> TEACHER_ID INTEGER NOT NULL,
>--->>> NAME CHAR(30) CHARACTER SET ISO8859_1 NOT NULL,
>);
>
>Then a contract :
>
>CREATE TABLE CONTRACT (
> CONTRACT_ID INTEGER NOT NULL,
> COMPANY_ID INTEGER NOT NULL,
> --->>> NAME CHAR(20) CHARACTER SET ISO8859_1,
>);
>
>Then attendance records ( which the teacher fills out on a form, a
>line per a student of total hours for the month for that contract)
>
>CREATE TABLE ATTENDANCE (
> ATTENDANCE_ID INTEGER NOT NULL,
> TEACHER_ID INTEGER NOT NULL,
> CONTRACT_ID INTEGER NOT NULL,
> AMONTH MONTHDOMAIN NOT NULL,
>); //all four above constitute a unique record (month is part of
>unique contraint)

TEACHER_ID, CONTRACT_ID, AMONTH ?

>CREATE TABLE ATTENDANCE_DETAIL (
> ATTENDANCE_DETAIL_ID INTEGER NOT NULL, //probably not needed
> ATTENDANCE_ID INTEGER NOT NULL,
> STUDENT_ID INTEGER NOT NULL,
> HOURS NUMERIC(15,2),
>);

The ATTENDANCE_DETAIL_ID is recommended here if you want to avoid having a composite primary key which is composed of two foreign keys. I *always* want to avoid that because you are going to get redundant indexing on those columns. This can play havoc with IB's query optimizer.

>and a single link table :
>
>CREATE TABLE STUDENT_CONTRACT_LINK (
> CONTRACT_ID INTEGER NOT NULL,
> STUDENT_ID INTEGER NOT NULL
>);

For this to be OK, you would need to rule out the possiblility that more than one course would be under the same contract. In that case, you cannot store the required links if a particular student attends two courses under the same contract. If there is any possibility of that, then you need a surrogate primary key. If you need to know the student's attendances at the level of an individual course, you'll also need some kind of course identifier which would participate in a unique constraint.

>The form I wish to construct looks a little like this :
>
>Title : teacher/month/contract
>
>grid : Student name | Hours
>
>I know which students to get for the attendance info because of the
>student-contract_link table (since each attendance sheet is for an
>individual contract).

What happens if there are two students named John Smith? You probably need the student ID there as a non-editable column, too.

>The "attendance detail" rows don't necessarily exist yet, although
>the parent does. I'm hoping to set it up to be as automatic (as
>little coding) as possible. But how do I do that AND get the students
>name?

No problem - think SETS of data, not tables.

For simplicity, let's assume the Attendance row that you want to work with already exists and your dataset is something like this:

SELECT
ATTENDANCE_ID,
TEACHER_ID,
CONTRACT_ID
AMONTH
FROM ATTENDANCE
WHERE ATTENDANCE_ID= :ATTENDANCE_ID
AND TEACHER_ID= :TEACHER_ID
AND CONTRACT_ID= :CONTRACT_ID
AND AMONTH= :AMONTH

This gives you a master dataset of one row.

Then, you have your detail dataset:

SELECT ATTENDANCE_DETAIL_ID,
ATTENDANCE_ID,
STUDENT_ID,
HOURS
FROM ATTENDANCE_DETAIL
WHERE ATTENDANCE_ID = :ATTENDANCE_ID

We want more in this dataset, but I'll get back to that.

This dataset is MasterSourced to the Attendance query, Masterlinking through Attendance_ID. That takes care of the master-detail relationship - each row that is added to the detail dataset will get the Attendance_ID value written automatically.

Now let's turn to the other participant in the relationship, student_id.

Your attendance detail structure is really an intersection table that allows zero to many students to participate in an attendance. You need a query that selects the eligible students and lists them for selection into a new att. detail row. If you use the native IBO components, you can make this a drop-down list in the grid - the user selects the name that has the correct student_id and that id gets written into the linked column of the master dataset (the query over the Attendance table).

Using your structures, (not my suggestions) here is your set:

SELECT S.STUDENT_ID, S.NAME FROM STUDENT
JOIN STUDENT_CONTRACT SC ON SC.STUDENT_ID=S.STUDENT_ID
JOIN ATTENDANCE A ON A.CONTRACT_ID=SC.CONTRACTID
WHERE A.CONTRACT_ID= :CONTRACT_ID

This dataset needs to be "keylinked" to the detail set. First, this dataset's KeySource property is set to the ib_datasource of the att_detail dataset. Then, pairs of keylinks are provided to link with the att_detail column that wants the STUDENT_ID key. (btw, this construction is not master-detail...think of the detail query as the "Keysource parent" that is reaching out to the student name list dataset for its "Keysource child".)

Here is the KeyLinks entry:

STUDENT_ID=ATTENDANCE_DETAIL.STUDENT_ID

Later, you can start getting creative and set the list query up in a TIB_LookupCombo which can be dropped into the grid. In editing mode, this list gets activated and the key of any selection made from this dd list will get written into the keylinked column in the parent. I won't go into that in detail, because exact instructions are in the GSG. However, earlier, I promised that there was "more to come" in the att_detail dataset...

Soooo...we add another column to this dataset:

SELECT ATTENDANCE_DETAIL_ID,
ATTENDANCE_ID,
STUDENT_ID,
HOURS,
(SELECT NAME FROM STUDENT WHERE STUDENT_ID= ATTENDANCE_DETAIL.STUDENT_ID)
AS MY_STUDENT_NAME
FROM ATTENDANCE_DETAIL
WHERE ATTENDANCE_ID = :ATTENDANCE_ID

i.e., we now have a COMPUTED column in the detail dataset, called MY_STUDENT_NAME, which has been been subselected by matching up the student_id columns.

As you work your way through the steps for linking up the dataset behind the name list with its "KeySource parent" you'll need the KeyDescLinks property to tie the computed column with the NAME column in the "KeySource child" (your list).

But that's for another day...

hth

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________