How can I have only one one-2-one?

[Ilan Sebba]

Hello to all

I have a parent table called party. This has a one to one relationship with
different types of parties, eg BirthdayParty, WeddingParty, Funeral (not a
happy party).


My problem is this, if I have a party with an ID = 1, then I want there to
be only one 'child' party using that ID. In other words I want this: 'give
me the ID number of the 'parent party' and I will find the corresponding
'child' party. Right now, I can have a funeral and a wedding both using the
same Party.ID. I am sure you will agree, the two should not be mixed up.
Her is what my table design looks like:

tblParty
PartyID [Primary Key]
FieldsAttributableToAllParties

tblBirthday
PartyFK [one to one, enforce referencial integrity, no cascade update or
delete] [Also serves as primary key for this table]
FieldsAttributableToBirthdayParties

tblWedding
PartyFK [one to one, enforce referencial integrity, no cascade update or
delete] [Also serves as primary key for this table]
FieldsAttributableToWeddings

tblLotsOfOtherPartyTable
... same structure as tblBirhday and tblWedding


Right now for each record in tblParty, I can also have one record in each of
tblBirthday, tblWedding etc. I don't want this. If I have a record in
table Party, that record should belong to only one other record in all the
the other tables.

Many thanks for your suggestions (kindly use layman's language)


Ilan

 

[Gary Miller]

llan,

Where your problem and confusions lie is at the root of a
slight design concept problem with your database.

I suggest a slight re-arrangement of your underlying tables
to this...

tblClient
ClientID
** Any unique fields for the client. IE... Address
Phone, etc...

tblParty
PartyID [Primary Key]
ClientID [Foreign Key]
PartyDate
PartyType


tblPartyTypes
PartyTypeID
PartyType
(PartyTypeID=1,PartyType="Birthday")

Now you just poke into every party what type it was using
the PartyTypeID. What you definitely do NOT want to do is to
create seperate tables for each party type. You can never
tie them together at the end when you need to consolidate
your history.

Gary Miller
Sisters, OR


"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in message
news:[email protected]...

 

[TC]

You will not be able to do this using normal referential integrity.

However, here's how to do it in code.

Rename all your "party" tables (except tblParty) to have a common prefix -
say:
tblZZBirthday
tblZZWedding
tblZZFuneral
etc.

(ZZ is not very good. Choose something better!)

The next code will search *all* of those tables for the record with ID=99.
(I'm just using 99 as an example.) It then returns the number of tables that
*have* that record, or zero if none of them do.

(untested)

dim db as database, td as tabledef, nFound as integer
set db = currentdb()
for each td in db.tabledefs
if td.name like "tblZZ*" then
if dlookup (1, td.name, "ID=99") = 1 then
nfound = nfound + 1
endif
endif
next
set db = nothing
' now nFound = number of tblZZ* tables that contain a record with ID=99.

You could put that code in a module as a public function, pass the ID value
to it, & return nFound as the function value. Then, whenever you need to see
whether an ID value is or is not in those tables, just call that function, &
see what returns. If you wanted to make it more efficient, you could change
the code to terminate the loop as soon as the first matching record is
found. Then, you will not need nFound, and you would just return True if a
record was found, False otherwise.

HTH,
TC

 

[Ilan Sebba]

Thank you for this.

Gary, I believe your reply is saying that in order for me to avoid the
problem, I must give up the supertype/subtype structure.
While your suggestion would solve my problem of not having one record in
more than one table (because in your suggestion I will only have one table),
it will create other problems, namely that tblPartyTypes would require many
fields (each party has different fields), and the table, when populated wll
have lots of voids.

TC suggests I write code. I appreciate that this would be a solution, but I
was hoping that I can rely on relational dababases to avoid having to write
lots of code.

In an entertaining thread
http://www.google.com/groups?hl=en&...9tqr47%24obl%241%40news7.svr.pol.co.uk&rnum=1

Craig Alexander Morrison promotes the idea of using one to one tables. In
particula, I quote the following:

"An example of a One to One Relationship where a "supertype" record must
have
one related "subtype" record is say you have a Client table and your clients
can be individuals, partnerships or corporations. Now the Client (supertype)
has specific information recorded about all clients, however, depending upon
which subtype they were (individual etc) different information would be
recorded. You would be better to have three subtyped tables containing the
information that was required for each different type of client. As your
client can only be an Individual or a Partnership or a Corporation then in
this example only one of the subtypes would be used for each supertype
record."

He makes it very clear that for each type of client, individual, corporation
or partnerships. Now the next step: how do avoid a schizophrenic client,
one who is both a corporation and a partnership at the same time?

Should I give up?

Many thanks

Ilan

 

[Tim Ferguson]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in

TC suggests I write code. I appreciate that this would be a solution,
but I was hoping that I can rely on relational dababases to avoid
having to write lots of code.

With a real RDBMS you can, but not with Access. Once you move up to
industrial-strength databases, you can use things called triggers that are
procedures that run in response to events in tables: updates, inserts and
deletes. In this way, you could certainly get the db to check for the
existence of the PartyID in one of the other subtypes.

In Access, though, you'll have to make do with restricting the users to
your forms and control stuff through that. There is nothing wrong with the
supertype/ subtyping structure, and it looks like a very reasonable
implementation of it. But you have to make some compromises when using a
(relatively) cheap desktop platform compared to an industrial-strength
client-server. If you really want to push this design, you can always
install MSDE, which comes with Access and Office Pro, is free, and does
everything that a full blown SQL Server does including triggers.

Hope that helps


Tim F

 

[Ilan Sebba]

Thanks for this. The trigger idea is helpful, sort of like an 'event' in
VB.

 

[TC]

Triggers are great, but IMO they are not part of the relational model. So I
feel that it is not very fair to say that you could meet your requirement
"in a >real< relational database".

Anyway, you do not need to write "lots" of code. I gave you the code! It was
a dozen lines or so. Just call it from the relevant form(s).

Cheers,
TC

 

[Ilan Sebba]

You are right, on both counts ;-)

Many thanks

Ilan