How to ensure subtype uniqueness.

[Konstantinos]

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

[Larry Linson]

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" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

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

[Konstantinos]

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" <bo*****@localhost.not> wrote in message
news:MyChd.4440$RA4.3690@trnddc06...

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" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

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

[Larry Linson]

"Konstantinos" wrote

I am using the concept of subtyping
for a number of entities simply because
it looks conceptually stronger
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.

. . .
It looks that if I stick with Access I will
have to master VBA to do descent
development.
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.
One more question. Is SQL server as
dependent on VBA as access is?
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.
Regards, and thanks
Konstantinos

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

[Konstantinos]

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" <bo*****@localhost.not> wrote in message
news:byDhd.9045$vB.4060@trnddc03...

"Konstantinos" wrote

I am using the concept of subtyping
for a number of entities simply because
it looks conceptually stronger

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.

. . .

It looks that if I stick with Access I will
have to master VBA to do descent
development.

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.

One more question. Is SQL server as
dependent on VBA as access is?

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.

Regards, and thanks
Konstantinos

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