Validation? Loop? Commit?

[Sander Verhagen]

Hi,


For implementation in VC/MFC I'm using CDaoDatabase and will soon convert to
CDatabase.

I want to provide functionality in my database layer to insert a combination
of database records into the database that only all together meet
referential integrity validation rules. Thus with loops where table A
records have a foreign key to table B and vice versa. I'll leave out the
actual examples, as I don't want to start a discussion on that. Let's please
just assume there are good reasons for this kind of modeling.
Also I want to strongly enforce referential integrity in my database, thus I
am defining that relations must be valid and key fields are required.

Inserting A without the referenced field in B yet existing will lead to an
error from the database. The other way around just the same. A loop
situation occurs.
Is there a way to postpone this validation?
I have tried doing all the mentioned inserts from within a single
transaction; but it is still validating the individual inserts. Is there a
way around that?

(I know there are workarounds, such as having a dummy field to reference and
replacing the reference once the actual record exists and such. But I don't
see these as anything else but nasty tricks, while I am looking for an
actual solution. I see a solution in postponing the validation, as said, but
I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Frank Hickman]

If your going to have strict referential integrity, then you cannot do what
your attempting without some 'nasty trick', which I'm not entirely sure will
work anyway. The only solution I can see is forcing referential integrity
at the application level and not the database level. But then it's up to
the programmer to ensure that referential integrity is maintained, not a
good idea.

 

[Hank Williams]

Also sounds like a modeling problem. Something is not correct.

--

Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net

If your going to have strict referential integrity, then you cannot do what
your attempting without some 'nasty trick', which I'm not entirely sure will
work anyway. The only solution I can see is forcing referential integrity
at the application level and not the database level. But then it's up to
the programmer to ensure that referential integrity is maintained, not a
good idea.

--
Frank

Hi,


For implementation in VC/MFC I'm using CDaoDatabase and will soon

convert

to
CDatabase.

I want to provide functionality in my database layer to insert a combination
of database records into the database that only all together meet
referential integrity validation rules. Thus with loops where table A
records have a foreign key to table B and vice versa. I'll leave out the
actual examples, as I don't want to start a discussion on that. Let's please
just assume there are good reasons for this kind of modeling.
Also I want to strongly enforce referential integrity in my database,

thus

I
am defining that relations must be valid and key fields are required.

Inserting A without the referenced field in B yet existing will lead to an
error from the database. The other way around just the same. A loop
situation occurs.
Is there a way to postpone this validation?
I have tried doing all the mentioned inserts from within a single
transaction; but it is still validating the individual inserts. Is there a
way around that?

(I know there are workarounds, such as having a dummy field to reference and
replacing the reference once the actual record exists and such. But I don't
see these as anything else but nasty tricks, while I am looking for an
actual solution. I see a solution in postponing the validation, as said, but
I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Sander Verhagen]

Hi,

Also sounds like a modeling problem. Something is not correct.

I get that comment more often. But even the remodeled propositions people
around me come up with suffer from the same basic problem, namely that there
is a "looped" sort of referencing.
But feel free to tell me how you would model groups of persons where each
group has always exactly one leader (that itself is also a member of the
group) and each person always belongs to exactly one group. I have not yet
seen a way to model this in such a way that a database implementation can
strictly enforce integrity rules on the model without inducing integrity
violations when inserting data. But, agreed, there always is the possibility
of me being wrong ;-)

Gr.,


Sander Verhagen
[ (e-mail address removed) ]

Also sounds like a modeling problem. Something is not correct.

--

Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net

If your going to have strict referential integrity, then you cannot do what
your attempting without some 'nasty trick', which I'm not entirely sure will
work anyway. The only solution I can see is forcing referential integrity
at the application level and not the database level. But then it's up to
the programmer to ensure that referential integrity is maintained, not a
good idea.

to

an

there

a

way around that?

(I know there are workarounds, such as having a dummy field to

reference
and

replacing the reference once the actual record exists and such. But I don't
see these as anything else but nasty tricks, while I am looking for an
actual solution. I see a solution in postponing the validation, as

said,
but

I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Frank Hickman]

hmmm,

I'd do it something like this...

Groups
----------------
GID int
GLID int
Description vc

GroupMembers
----------------
GID int
MID int

Members
----------------
MID int
Name vc

Then for the relationships...

Primary Key Foreign Key
----------------------------------------
Groups.GID GroupMembers.GID
Groups.GLID Members.MID
Members.MID GroupMembers.MID

You could add validation in a trigger that forces only one member assigned
to one group at a time.

HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

Hi,

Also sounds like a modeling problem. Something is not correct.

I get that comment more often. But even the remodeled propositions people
around me come up with suffer from the same basic problem, namely that there
is a "looped" sort of referencing.
But feel free to tell me how you would model groups of persons where each
group has always exactly one leader (that itself is also a member of the
group) and each person always belongs to exactly one group. I have not yet
seen a way to model this in such a way that a database implementation can
strictly enforce integrity rules on the model without inducing integrity
violations when inserting data. But, agreed, there always is the possibility
of me being wrong ;-)

Gr.,


Sander Verhagen
[ (e-mail address removed) ]

Also sounds like a modeling problem. Something is not correct.

--

Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net

If your going to have strict referential integrity, then you cannot do what
your attempting without some 'nasty trick', which I'm not entirely

sure
will

work anyway. The only solution I can see is forcing referential integrity
at the application level and not the database level. But then it's up to
the programmer to ensure that referential integrity is maintained, not a
good idea.

--
Frank

Hi,


For implementation in VC/MFC I'm using CDaoDatabase and will soon convert
to
CDatabase.

I want to provide functionality in my database layer to insert a
combination
of database records into the database that only all together meet
referential integrity validation rules. Thus with loops where table A
records have a foreign key to table B and vice versa. I'll leave out the
actual examples, as I don't want to start a discussion on that. Let's
please
just assume there are good reasons for this kind of modeling.
Also I want to strongly enforce referential integrity in my

database,
thus

I
am defining that relations must be valid and key fields are required.

Inserting A without the referenced field in B yet existing will lead

to

an
error from the database. The other way around just the same. A loop
situation occurs.
Is there a way to postpone this validation?
I have tried doing all the mentioned inserts from within a single
transaction; but it is still validating the individual inserts. Is

there

a
way around that?

(I know there are workarounds, such as having a dummy field to reference
and
replacing the reference once the actual record exists and such. But I
don't
see these as anything else but nasty tricks, while I am looking for an
actual solution. I see a solution in postponing the validation, as said,
but
I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Sander Verhagen]

Hi,


In the end it seems that the model doesn't really matter that much, does it?
But it is still all about how to implement it.

I don't really understand what you were proposing for that in your last
sentence ("You could add validation in a trigger" etc.). Could you elaborate
a bit further on that?

Greetings,


Sander Verhagen
[ (e-mail address removed) ]

hmmm,

I'd do it something like this...

Groups
----------------
GID int
GLID int
Description vc

GroupMembers
----------------
GID int
MID int

Members
----------------
MID int
Name vc

Then for the relationships...

Primary Key Foreign Key
----------------------------------------
Groups.GID GroupMembers.GID
Groups.GLID Members.MID
Members.MID GroupMembers.MID

You could add validation in a trigger that forces only one member assigned
to one group at a time.

HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

Hi,

Also sounds like a modeling problem. Something is not correct.

I get that comment more often. But even the remodeled propositions people
around me come up with suffer from the same basic problem, namely that there
is a "looped" sort of referencing.
But feel free to tell me how you would model groups of persons where each
group has always exactly one leader (that itself is also a member of the
group) and each person always belongs to exactly one group. I have not yet
seen a way to model this in such a way that a database implementation can
strictly enforce integrity rules on the model without inducing integrity
violations when inserting data. But, agreed, there always is the possibility
of me being wrong ;-)

Gr.,


Sander Verhagen
[ (e-mail address removed) ]

Also sounds like a modeling problem. Something is not correct.

--

Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net

If your going to have strict referential integrity, then you cannot do
what
your attempting without some 'nasty trick', which I'm not entirely sure
will
work anyway. The only solution I can see is forcing referential integrity
at the application level and not the database level. But then it's

up
to

the programmer to ensure that referential integrity is maintained,

not

a

table

A out
the lead
to

But

I

for

an

actual solution. I see a solution in postponing the validation, as said,
but
I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Frank Hickman]

Well actually, I noticed that your posting to some Access groups as well, if
your using Access you cannot create/use triggers. What you would want to do
in that case is create unique indexes on the ID fields so Access would
complain about duplicate entries. The only problem with this approach is if
the people leave one group and move to another you would not be able to keep
a "history" of what people were in what group when.

Triggers are like procedures/functions that get executed when data is being
inserted, updated, or deleted.

HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

Hi,


In the end it seems that the model doesn't really matter that much, does it?
But it is still all about how to implement it.

I don't really understand what you were proposing for that in your last
sentence ("You could add validation in a trigger" etc.). Could you elaborate
a bit further on that?

Greetings,


Sander Verhagen
[ (e-mail address removed) ]

hmmm,

I'd do it something like this...

Groups
----------------
GID int
GLID int
Description vc

GroupMembers
----------------
GID int
MID int

Members
----------------
MID int
Name vc

Then for the relationships...

Primary Key Foreign Key
----------------------------------------
Groups.GID GroupMembers.GID
Groups.GLID Members.MID
Members.MID GroupMembers.MID

You could add validation in a trigger that forces only one member assigned
to one group at a time.

HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

Hi,


Also sounds like a modeling problem. Something is not correct.

I get that comment more often. But even the remodeled propositions people
around me come up with suffer from the same basic problem, namely that there
is a "looped" sort of referencing.
But feel free to tell me how you would model groups of persons where each
group has always exactly one leader (that itself is also a member of the
group) and each person always belongs to exactly one group. I have not yet
seen a way to model this in such a way that a database implementation can
strictly enforce integrity rules on the model without inducing integrity
violations when inserting data. But, agreed, there always is the possibility
of me being wrong ;-)

Gr.,


Sander Verhagen
[ (e-mail address removed) ]


"Hank Williams" <HaWilliams(at)msn.com> wrote in message
Also sounds like a modeling problem. Something is not correct.

--

Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net

If your going to have strict referential integrity, then you

cannot

do

it's

up

to
the programmer to ensure that referential integrity is maintained,

not

a
good idea.

--
Frank

Hi,


For implementation in VC/MFC I'm using CDaoDatabase and will soon
convert
to
CDatabase.

I want to provide functionality in my database layer to insert a
combination
of database records into the database that only all together meet
referential integrity validation rules. Thus with loops where

table

A
records have a foreign key to table B and vice versa. I'll leave out
the
actual examples, as I don't want to start a discussion on that. Let's
please
just assume there are good reasons for this kind of modeling.
Also I want to strongly enforce referential integrity in my database,
thus
I
am defining that relations must be valid and key fields are required.

Inserting A without the referenced field in B yet existing will lead
to
an
error from the database. The other way around just the same. A loop
situation occurs.
Is there a way to postpone this validation?
I have tried doing all the mentioned inserts from within a single
transaction; but it is still validating the individual inserts. Is
there
a
way around that?

(I know there are workarounds, such as having a dummy field to
reference
and
replacing the reference once the actual record exists and such.

But

I
don't
see these as anything else but nasty tricks, while I am looking

for

an
actual solution. I see a solution in postponing the validation, as
said,
but
I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Lyle Fairfield]

Well actually, I noticed that your posting to some Access groups as
well, if your using Access you cannot create/use triggers.

Total Bullshit! Did you ever think of checking what you say before you post?

 

[Roy Fine]

Total Bullshit! Did you ever think of checking what you say before you post?

Lyle

Sounds like good advice -- PLEASE FOLLOW IT in future posts

MS Access (at least up through vesion 10) does not support triggers -

(p.s. - profanities are a sign of either ignorance or arrogance - which are
you)
roy fine

 

[Lyle Fairfield]

Lyle

Sounds like good advice -- PLEASE FOLLOW IT in future posts

MS Access (at least up through vesion 10) does not support triggers -

(p.s. - profanities are a sign of either ignorance or arrogance - which
are you)
roy fine

Why do so many really stupid people post to this group?

 

[Roy Fine]

Why do so many really stupid people post to this group?

Nothing better to do... what's your reason?

 

[Frank Hickman]

And what would that be? My mistake about the Access group (not seeing the
adp.sqlserver part) or Access not supporting triggers? If you can get
Access to create and use a trigger, please enlighten me...I'm perfectly
willing to learn something new, and admit if I'm wrong in something.

--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

 

[Frank Hickman]

Just to piss you off.

--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

 

[Sander Verhagen]

Hi,


I am sorry that my question is something you felt the need to fight over :-o

I did not specify too much about the database platform used since I
consider(ed) this problem to be independent from the chosen DBMS. Also I
really expect(ed) there to be a simple, straightforward solution in SQL or
something, as I cannot imagine that not many, many people before me have had
this very same problem.

I am indeed currently implementing for Jet (CDaoDatabase in MFC) but will
convert to MS SQL Server DE (CDatabase) as soon as my admin gets it up and
running and I have a few hours to spare to convert some classes.

Gr.,


Sander Verhagen
[ (e-mail address removed) ]

Well actually, I noticed that your posting to some Access groups as well, if
your using Access you cannot create/use triggers. What you would want to do
in that case is create unique indexes on the ID fields so Access would
complain about duplicate entries. The only problem with this approach is if
the people leave one group and move to another you would not be able to keep
a "history" of what people were in what group when.

Triggers are like procedures/functions that get executed when data is being
inserted, updated, or deleted.

HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

Hi,


In the end it seems that the model doesn't really matter that much, does it?
But it is still all about how to implement it.

I don't really understand what you were proposing for that in your last
sentence ("You could add validation in a trigger" etc.). Could you elaborate
a bit further on that?

Greetings,


Sander Verhagen
[ (e-mail address removed) ]

hmmm,

I'd do it something like this...

Groups
----------------
GID int
GLID int
Description vc

GroupMembers
----------------
GID int
MID int

Members
----------------
MID int
Name vc

Then for the relationships...

Primary Key Foreign Key
----------------------------------------
Groups.GID GroupMembers.GID
Groups.GLID Members.MID
Members.MID GroupMembers.MID

You could add validation in a trigger that forces only one member assigned
to one group at a time.

HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.


Hi,


Also sounds like a modeling problem. Something is not correct.

I get that comment more often. But even the remodeled propositions people
around me come up with suffer from the same basic problem, namely that
there
is a "looped" sort of referencing.
But feel free to tell me how you would model groups of persons where each
group has always exactly one leader (that itself is also a member of the
group) and each person always belongs to exactly one group. I have

not
yet

seen a way to model this in such a way that a database

implementation
can

strictly enforce integrity rules on the model without inducing integrity
violations when inserting data. But, agreed, there always is the
possibility
of me being wrong ;-)

Gr.,


Sander Verhagen
[ (e-mail address removed) ]


"Hank Williams" <HaWilliams(at)msn.com> wrote in message
Also sounds like a modeling problem. Something is not correct.

--

Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net

If your going to have strict referential integrity, then you

cannot

do
what
your attempting without some 'nasty trick', which I'm not entirely
sure
will
work anyway. The only solution I can see is forcing referential
integrity
at the application level and not the database level. But then

it's

up
to
the programmer to ensure that referential integrity is

maintained,
not

a
good idea.

--
Frank

Hi,


For implementation in VC/MFC I'm using CDaoDatabase and will soon
convert
to
CDatabase.

I want to provide functionality in my database layer to insert a
combination
of database records into the database that only all together meet
referential integrity validation rules. Thus with loops where table
A
records have a foreign key to table B and vice versa. I'll

leave
out

the
actual examples, as I don't want to start a discussion on that.
Let's
please
just assume there are good reasons for this kind of modeling.
Also I want to strongly enforce referential integrity in my
database,
thus
I
am defining that relations must be valid and key fields are
required.

Inserting A without the referenced field in B yet existing

will
lead

to
an
error from the database. The other way around just the same. A loop
situation occurs.
Is there a way to postpone this validation?
I have tried doing all the mentioned inserts from within a single
transaction; but it is still validating the individual

inserts.

Is such.
But looking
for

validation,

as

said,
but
I don't know how to do that.)

Help!

Thanks already,


Sander Verhagen
[ (e-mail address removed) ]

 

[Vadim Rapp]

SV> But feel free to tell me how you would model
SV> groups of persons where each group has always
SV> exactly one leader (that itself is also a member
SV> of the group) and each person always belongs to
SV> exactly one group.

SV> (I know there are workarounds, such as having a
SV> dummy field to reference and replacing the
SV> reference once the actual record exists and such.
SV> But I don't see these as anything else but nasty
SV> tricks, while I am looking for an actual solution.

I don't think those are nasty tricks; they actually represent the business.

Let's pretend there's employee X belonging to the group A. We decide to
create new group B and make X its lead. At some point of our thinking, X is
already "alienated" from his current group A, i.e. we recognize that he
should better be out of it; but we have not decided on the name of his own
group. In the database, we model it by creating a pseudo-group like "We Have
Not Decided Yet" (indeed we have not), so we first assign X to it; then we
make up our mind and create group B with X as its head; and then reassign X
to it.

Or, it may be that we first realize that we need a new group, and then we
begin to think who deserves to be its leader. In the database, we create the
new group with a fake leader "We Have Not Decided Yet" (which is true);
then, when we make the decision, we assign X to the new group, and change
the group's leader to X.

We can further implement some business rules, for instance that no group
should remain with "have not decided" as its leader for too long, noone
should be in the group "have not decided" for too long; etc.


Vadim

 

[Lyle Fairfield]

And what would that be? My mistake about the Access group (not seeing
the adp.sqlserver part) or Access not supporting triggers? If you can
get Access to create and use a trigger, please enlighten me...I'm
perfectly willing to learn something new, and admit if I'm wrong in
something.

In an Access XP ADP
note we are in microsoft.public.access.adp.sqlserver

right click on the SQL-Server table name
a dialog box will open in which you may create a trigger

eg.

ALTER TRIGGER KingofTheCowboys
ON dbo.Table1
FOR INSERT
AS
INSERT INTO Table2 (fldSonsofthePioneers) VALUES ('Lyle did it')

or

ALTER TRIGGER Buttermilk
ON dbo.Table1
FOR DELETE
AS
INSERT INTO Table2 (fldSonsofthePioneers) VALUES ('Lyle undid it')

If you are thinking that Access = JET then you need a few months on a
quiet tropical island with some books ....

 

[Michel Walsh]

Hi,


Sometimes, using NULL may allow to solve that problem. Indeed, if A(f1,
f2), read fields f1 and f2 in tableA, requires that A.f2 is in B.g1
and if B(g1, g2) requires that B.g2 in A.f1, then inserting (newName,
Null) in A satisfies the first requirement. Then insert, in B, (WouldBe,
NewName) and finally, update A(newName, Null) to A(NewName, WouldBe), would
have insert the two records without having to resort to incoherent
insert/update. There may be many potential problems, on the other hand, one
you have in MS SQL Server (and not in Jet) is that if any of the implied
fields has a UNIQUE constraint on it, then MS SQL Server considers, in that
context, that NULL=NULL is true, and allow just one record with a NULL,
under that column.


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Probably because Access is not a database, but an application using
database. Jet is a database. MS SQL Server is a database. Access is an
application use either Jet, either MS SQL Server for "native" database
engine.


Hoping it may help,
Vanderghast, Access MVP

And what would that be? My mistake about the Access group (not seeing the
adp.sqlserver part) or Access not supporting triggers? If you can get
Access to create and use a trigger, please enlighten me...I'm perfectly
willing to learn something new, and admit if I'm wrong in something.

--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.

 

[Lyle Fairfield]

[email protected]:

Probably because Access is not a database, but an application using
database. Jet is a database. MS SQL Server is a database. Access is an
application use either Jet, either MS SQL Server for "native" database
engine.

I am 100% sure that you, Michel, are completely aware that Access, as an
application, supports the creation of ADPs, and that ADPs provide an
interface for the creation of triggers in MS SQL Server and that, of course,
as MS SQL Server is THE database, these triggers work perfectly well,
regardless of what application is using the MS SQL Server Database. To say
that triggers cannot be created nor used in an Access ADP
(and this thread was posted to:
microsoft.public.access.adp.sqlserver)
is just plain wrong.

 

[Frank Hickman]

Who said ANY thing about ADPs? Perhaps you should practice what you preach.
And as for the triggers, your correct that they can be created for SQL
Server which is NOT Access. Access's underlining database is Jet which does
NOT support triggers. Can we please drop it now?