How to ensure subtype uniqueness.

I cant find much info or a code on how to do this.
I have a generic table called Contact.
Then I have tables Person and Company that are subtypes of contact each with
attributes that are specific to the subtype.

The primary key for each table is a field called ContactID (PK to CONTACT,
FK and PK for PERSON and COMPANY).
How do I force an instance of contact to be either a person or a company but
not both?

Or in other words, if I create a new Contact how do I make sure the
ContactID exists also in the Person or the Company field but not both?

I know a solution is to combine all attributes in the contact table, but I
dont want to do that.
Everywhere I ve seen references on doing this with triggers or constraints
but not an actual sample of how it is done.
Closest to a solution I came is here,
http://www.mvps.org/access/tables/tbl0013.htm
but this is a solution based on the interface (forms) rather than the
database engine.
Is it possible in Access to have this done the way I want it?

Thanks for any help

Konstantinos

re: How to ensure subtype uniqueness.

Access, using the Jet engine, does not support triggers, nor stored
procedures.

If you choose to use Access to create a front-end or user interface to a
server database that supports them, you could use triggers. But, I see no
need for a trigger to retrieve the proper information; see below for
suggestions on a query.

You will have to handle ensuring that one or the other of the related
records is written, but not both, in your application... VBA event code.
That is why the solution at the FAQ site is as it is.

Because you do not specify what is in the "generic table", it is difficult
to offer specific suggestions. I can suggest that in the generic table, you
identify the type of contact, create your query linking to both, and use an
outer join in order to allow one of the related tables to return a Null
entry. By outer join, I mean rightclicking on the join line between the data
sources in the Query, and choosing "All records from Contacts and only those
that match from People" and "All records from Contacts and only those that
match from Companies".

Best of luck with your application.

Larry Linson
Microsoft Access MVP


"Konstantinos" <noemail@noemail.net> wrote in message
news:10odqd1qaqaiu0f@corp.supernews.com...[color=blue]
> I cant find much info or a code on how to do this.
> I have a generic table called Contact.
> Then I have tables Person and Company that are subtypes of contact each[/color]
with[color=blue]
> attributes that are specific to the subtype.
>
> The primary key for each table is a field called ContactID (PK to CONTACT,
> FK and PK for PERSON and COMPANY).
> How do I force an instance of contact to be either a person or a company[/color]
but[color=blue]
> not both?
>
> Or in other words, if I create a new Contact how do I make sure the
> ContactID exists also in the Person or the Company field but not both?
>
> I know a solution is to combine all attributes in the contact table, but I
> dont want to do that.
> Everywhere I ve seen references on doing this with triggers or constraints
> but not an actual sample of how it is done.
> Closest to a solution I came is here,
> http://www.mvps.org/access/tables/tbl0013.htm
> but this is a solution based on the interface (forms) rather than the
> database engine.
> Is it possible in Access to have this done the way I want it?
>
> Thanks for any help
>
> Konstantinos
>
>[/color]

re: How to ensure subtype uniqueness.

Thanks Larry,
I am using the concept of subtyping for a number of entities simply because
it looks conceptually stronger and at the implementation level it helps
avoid large numbers of nulls while at the same time avoids redundant
relationships (relationships are mapped to the supertype or subtype entities
as needed). I understand the performance might be affected as joins might
increase to handle subtypes but that issue does not concern me for now.
It looks that if I stick with Access I will have to master VBA to do descent
development.
One more question. Is SQL server as dependent on VBA as access is?

Regards, and thanks
Konstantinos

"Larry Linson" <bouncer@localhost.not> wrote in message
news:MyChd.4440$RA4.3690@trnddc06...[color=blue]
> Access, using the Jet engine, does not support triggers, nor stored
> procedures.
>
> If you choose to use Access to create a front-end or user interface to a
> server database that supports them, you could use triggers. But, I see no
> need for a trigger to retrieve the proper information; see below for
> suggestions on a query.
>
> You will have to handle ensuring that one or the other of the related
> records is written, but not both, in your application... VBA event code.
> That is why the solution at the FAQ site is as it is.
>
> Because you do not specify what is in the "generic table", it is difficult
> to offer specific suggestions. I can suggest that in the generic table,
> you
> identify the type of contact, create your query linking to both, and use
> an
> outer join in order to allow one of the related tables to return a Null
> entry. By outer join, I mean rightclicking on the join line between the
> data
> sources in the Query, and choosing "All records from Contacts and only
> those
> that match from People" and "All records from Contacts and only those that
> match from Companies".
>
> Best of luck with your application.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Konstantinos" <noemail@noemail.net> wrote in message
> news:10odqd1qaqaiu0f@corp.supernews.com...[color=green]
>> I cant find much info or a code on how to do this.
>> I have a generic table called Contact.
>> Then I have tables Person and Company that are subtypes of contact each[/color]
> with[color=green]
>> attributes that are specific to the subtype.
>>
>> The primary key for each table is a field called ContactID (PK to
>> CONTACT,
>> FK and PK for PERSON and COMPANY).
>> How do I force an instance of contact to be either a person or a company[/color]
> but[color=green]
>> not both?
>>
>> Or in other words, if I create a new Contact how do I make sure the
>> ContactID exists also in the Person or the Company field but not both?
>>
>> I know a solution is to combine all attributes in the contact table, but
>> I
>> dont want to do that.
>> Everywhere I ve seen references on doing this with triggers or
>> constraints
>> but not an actual sample of how it is done.
>> Closest to a solution I came is here,
>> http://www.mvps.org/access/tables/tbl0013.htm
>> but this is a solution based on the interface (forms) rather than the
>> database engine.
>> Is it possible in Access to have this done the way I want it?
>>
>> Thanks for any help
>>
>> Konstantinos
>>
>>[/color]
>
>[/color]

re: How to ensure subtype uniqueness.

"Konstantinos" wrote
[color=blue]
> I am using the concept of subtyping
> for a number of entities simply because
> it looks conceptually stronger[/color]

I am not certain, from the example you gave, that it is as advantageous as
you hope.While a person and a company can both be considered "contacts", I
deal with people and entities rather differently. But, you can certainly
work with what you have described.

. . .
[color=blue]
> It looks that if I stick with Access I will
> have to master VBA to do descent
> development.[/color]

Server databases do not provide a user front end*. You are going to need
_some_ front end, and Access (IMNSHO) is the best and easiest in which to
develop front ends for the client-server environment. Assuming that you use
Access for your front end, you are going to need to learn VBA _anyway_. It
is not hard... it's descended from BASIC. Remember that was created with the
goal of being easy for development and was quite successful in attaining
that goal.

* some server database manufacturers do
sell (separately priced) software tools to
create front end / user interface -- for
example, Sybase sells PowerBuilder, and
Oracle has a product also. People that I
know who have used those and have also
used Access prefer Access as a front-end
development tool.
[color=blue]
> One more question. Is SQL server as
> dependent on VBA as access is?[/color]

No, SQL Server does not "speak" VBA, but relies on T-SQL, a procedural
language based on SQL. But no matter how well-versed in T-SQL you are, you
can't use it to create the user interface, only to manipulate and extract
information within the SQL database.

The next version of SQL Server, however, will support use of VB.NET (and C#,
another .NET language) and this is considered by many to be a great
improvement.
[color=blue]
> Regards, and thanks
> Konstantinos[/color]

You're welcome. I hope my comments have been helpful.

re: How to ensure subtype uniqueness.

Many thanks for the long and informative table.
Btw, I use the term "entity" as the equivalent in a conceptual model for a
"table" in the physical and logical models.

Cheers,

Konstantinos

"Larry Linson" <bouncer@localhost.not> wrote in message
news:byDhd.9045$vB.4060@trnddc03...[color=blue]
> "Konstantinos" wrote
>[color=green]
> > I am using the concept of subtyping
> > for a number of entities simply because
> > it looks conceptually stronger[/color]
>
> I am not certain, from the example you gave, that it is as advantageous as
> you hope.While a person and a company can both be considered "contacts", I
> deal with people and entities rather differently. But, you can certainly
> work with what you have described.
>
> . . .
>[color=green]
> > It looks that if I stick with Access I will
> > have to master VBA to do descent
> > development.[/color]
>
> Server databases do not provide a user front end*. You are going to need
> _some_ front end, and Access (IMNSHO) is the best and easiest in which to
> develop front ends for the client-server environment. Assuming that you
> use
> Access for your front end, you are going to need to learn VBA _anyway_. It
> is not hard... it's descended from BASIC. Remember that was created with
> the
> goal of being easy for development and was quite successful in attaining
> that goal.
>
> * some server database manufacturers do
> sell (separately priced) software tools to
> create front end / user interface -- for
> example, Sybase sells PowerBuilder, and
> Oracle has a product also. People that I
> know who have used those and have also
> used Access prefer Access as a front-end
> development tool.
>[color=green]
> > One more question. Is SQL server as
> > dependent on VBA as access is?[/color]
>
> No, SQL Server does not "speak" VBA, but relies on T-SQL, a procedural
> language based on SQL. But no matter how well-versed in T-SQL you are, you
> can't use it to create the user interface, only to manipulate and extract
> information within the SQL database.
>
> The next version of SQL Server, however, will support use of VB.NET (and
> C#,
> another .NET language) and this is considered by many to be a great
> improvement.
>[color=green]
> > Regards, and thanks
> > Konstantinos[/color]
>
> You're welcome. I hope my comments have been helpful.
>
>[/color]