At 05:38 AM 07-03-02 +0000, you wrote:

>Hello,
>
> I have two tables:
> AGENT - which stores all info specific to the real estate agent
> PERSON - stores all personal info of everyone
>
> Since the AGENT is also a PERSON, I have to add to both tables when
>I want to add an AGENT.
> What's the best approach for this:
> 1) Having the AGENT as the master, and PERSON as the detail?

No, because it doesn't represent the real model.

> 2) Having the AGENT as the KEYSOURCE for the PERSON Dataset?

No, because Keysource-Lookup relationships are not for master-detail at
all. You would use if, for example, you had PersonRole table and you
wanted a lookup on RoleType

> 3) Not having any relationship between the two datasets?

No, that is wrong too.

What you seem to be missing here is probably fairly crucial to your mindset
about this structure. You have a table of Persons, all of whom have one or
more roles (otherwise, why would they be in your database?) It's possible
for one person to "be" more than one role, too. For example, a person who
is an Agent could also be a Registrant, a Tutor, a Shareholder.... So it
would be fairly classic to channel these Person-Role relationships out into
a detail table and distinguish them by role type.

If it's not possible to establish a "data paradigm" with this 1:Many model,
then the opposite extreme is to do what you are doing: having Person as
Master still, but having it Master to a variety of 1:1 or 1:many
relationships. In your model, the Person-Agent relationship is 1:1 - or,
more correctly, 1:zero or 1, i.e. it is an optional relationship.

> And how is an insert done properly here? What I do is when the user
>presses the '+' button, the AGENT->AfterInsert event contains a
>PERSON->Insert() which sets the proper keylinks in the PERSON
>dataset. However, I still haven't got it right, so I think there must
>be a easy way here.

Yes indeed - the easy and proper way to do this is to have TWO datasets
linked in a master-detail relationship...no joined datasets are either
necessary or desirable. Your master set is a select from the Person
table. Your detail set (from the Agent table) uses its Mastersource and
Masterlinks properties to connect to the master and IBO takes care of the
rest. You don't need any funny UpdateSQL or storedprocedures or whatever.

If you are not using the native TIB_* data access comps, then you are
"stuck" with the very lumpy way master-detail queries are linked by the
TDataset architecture. You use the Datasource property instead of
Mastersource to link the detail to the master, and the detail column which
links to the master's primary key has to have a matching column name.

In a 1:1 relationship, the master's PK and the detail's PK can be the same,
with this column in the detail serving as both primary and foreign
key. However, in your case, you might prefer to store an AgentID as the
primary key and use the PersonID singularly as the foreign
key. Firebird/InterBase doesn't always behave nicely when the PK and the
FK overlap...

> It's more complicated than normal because I have to code-up my own
>InsertSQL,EditSQL,DeleteSQL since the AGENT select is a JOIN of the
>two tables (AGENT & PERSON).

It's more complicated than it should be, as well. Get rid of that joined
dataset and replace it with a single-table query on Person. You have lots
of options for your UI but, because it is 1:1, I would display the master
set as some kind of single grid-style component (single-column grid,
Listbox, etc.) to display the names as a selection list; and show all of
the maintainable columns for both the master and the detail in a single
panel, in whatever CustomEdit controls are appropriate.

hth

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com