Validation? Loop? Commit?
Sander Verhagen
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
[ Verh...@nonono.Sander.com ]
Frank Hickman
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
"Sander Verhagen" <Verh...@nonono.Sander.com> wrote in message
news:%23d522zC...@TK2MSFTNGP09.phx.gbl...
Hank Williams
--
Hank Williams
Quantum Technologies, Inc.
HaWilliams(at)spamcop.net
"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:hvidnbwf8vL...@comcast.com...
Sander Verhagen
> 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
[ Verh...@nonono.Sander.com ]
"Hank Williams" <HaWilliams(at)msn.com> wrote in message
news:OM0qWNP6...@tk2msftngp13.phx.gbl...
Frank Hickman
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.
"Sander Verhagen" <Verh...@nonono.Sander.com> wrote in message
news:uVXRrLX6...@TK2MSFTNGP10.phx.gbl...
Sander Verhagen
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
[ Verh...@nonono.Sander.com ]
"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:2OGdnRORa7F...@comcast.com...
Frank Hickman
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.
"Sander Verhagen" <Verh...@nonono.Sander.com> wrote in message
news:ewZzRyc6...@TK2MSFTNGP09.phx.gbl...
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?
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Roy Fine
"Lyle Fairfield" <Missing...@Invalid.Com> wrote in message
news:Xns9483C71...@130.133.1.4...
> "Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in
> news:ibednZ9blri...@comcast.com:
>
> > 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?
>
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
news:e9rVz4e6...@TK2MSFTNGP09.phx.gbl:
Why do so many really stupid people post to this group?
Roy Fine
"Lyle Fairfield" <Missing...@Invalid.Com> wrote in message
Nothing better to do... what's your reason?
Frank Hickman
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" <Missing...@Invalid.Com> wrote in message
news:Xns9483C71...@130.133.1.4...
Frank Hickman
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.
"Lyle Fairfield" <Missing...@Invalid.Com> wrote in message
news:Xns9483CD2...@130.133.1.4...
> "Roy Fine" <rlf...@twt.obfuscate.net> wrote in
> news:e9rVz4e6...@TK2MSFTNGP09.phx.gbl:
>
>
Sander Verhagen
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
[ Verh...@nonono.Sander.com ]
"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:ibednZ9blri...@comcast.com...
Vadim Rapp
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
Michel Walsh
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
"Sander Verhagen" <Verh...@nonono.Sander.com> wrote in message
news:%23d522zC...@TK2MSFTNGP09.phx.gbl...
Michel Walsh
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
"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:vv2dnaxEs5F...@comcast.com...
Lyle Fairfield
$x7DH...@TK2MSFTNGP12.phx.gbl:
> 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
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?
--
Frank
"Lyle Fairfield" <Missing...@Invalid.Com> wrote in message
news:Xns948A77A...@130.133.1.17...
Michel Walsh
It seems you still miss the fundamental point that Access is NOT a
database engine and that JET is not more, not less, underlying to Access
than MS SQL Server actually is. Access and Jet, or MS SQL Server is like
Visual Studio 7.x with classical VC++, or Managed C++. There is nothing
underlying Access "exclusively", except by misconception (or wrong selection
of terms).
Who spoke ANY thing about adp? let see... the first newsgroup in
the list of newsgroups is anything but exactly that.
Vanderghast, Access MVP
"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:BO-dnXtuYOG...@comcast.com...
Frank Hickman
error and the post mentioned nothing about using Access as a front end to
SQL Server. Since Access could NOT function without Jet, I do not believe
your analogy holds weight since the same applies for SQL Server. Without
it's underlying database engine it would pretty much be useless. Try
removing Jet from your system and launching Access and tell me what
happens...
--
Frank
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:ORl5Hj17...@TK2MSFTNGP12.phx.gbl...
Michel Walsh
Well, last time you used Access was probably more than six years ago,
with Access 97. Indeed, with Access 2000, you can use it without DAO and
without Jet at all, just with MSDE or any other version of MS SQL Server,
all alone, natively, no Jet. Sure, you CAN also continue to use JET too, if
this is what you want (and that seems what most people still continue to
do), but that is no more an absolute pre-requisite.
Vanderghast, Access MVP
"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message
news:lvGdnWBfkOU...@comcast.com...
Frank Hickman
new.
--
Frank
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:uwSZ2X87...@TK2MSFTNGP12.phx.gbl...