I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query
and add 2 tables together it creates the correct relationship between
the two tables. I believe this is due to using a foreign key with the
same name. I cared not about cascading deletes or cascading updates or
the type of relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy
like a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
45  3347 
salad <oi*@vinegar.com> wrote in news:Sow4g.7230$BS2.6977
@newsread1.news.pas.earthlink.net: I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query
and add 2 tables together it creates the correct relationship between
the two tables. I believe this is due to using a foreign key with the
same name. I cared not about cascading deletes or cascading updates or
the type of relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy
like a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
Relationships are the Database! There is nothing else to say.
This is not a matter of opinion and it is not an item for debate.
--
Lyle Fairfield
salad wrote: I'm curious about your opinion on setting relationships.
Well, unless you write SPs to enforce all your participation rules, I
would think *not* enforcing RI with relationships is a PITA. It's much
easier, IMO, to have Access do it for you.
salad wrote: My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just
more overhead in creating an app and unnecessary...or do you believe
the app should have all relationships defined?
Hi Salad,
One of the basic precepts of relational database design is to have the
data structure do as much for you as possible. Presumably you are
doing much of this already with your table design, ie, allowing nulls,
setting indexes with no repeats and myriad other tasks.
In other database systems, the terms for these sorts of things are
generally called constraints - I believe that is the term used in the
ANSI/SQL standards as well.
One important constraint is what in Oracle is called the foreign key
constraint - this is the equivalent of relationships in Jet.
It's nothing whatsoever to do with speed of queries or SQL statements.
They are to do with ensuring records are not orphaned. There are many
examples were you don't want orphaned records - think of a table of
purchase orders and a table of individual transactions that are all
linked to a purchase order. You don't want the any transactions
standing around without a purchase order - it makes no sense.
FK constraints/relationships are meant to address this sort of thing.
This is relational database 101. 8)
It's a very established concept in relational design that it's dangerous
to rely on constraints that are enforced by form design:
Many of the times when I create a new query and add 2 tables together
it creates the correct relationship between the two tables.
This is not a relationship. What you are describing is a join. It's
not a constraint or a relationship and it does not prevent deletion of
linked records that should not be deleted if you don't want orphaned
records.
I
believe this is due to using a foreign key with the same name.
No, this is a simple join. A foreign key has absolutely nothing
whatsoever to do with with what you are describing. The name of the
joined fields/cloumns is immaterial.
I
cared not about cascading deletes or cascading updates or the type of
relationship so the relationships window is clean of tables.
I think you've missed the point of what relationships are. See my
ruminating above! 8)
And if I need to, I'll create a query on the fly via code. Again, I
set the relationships. I know these queries aren't compiled for
optimacy like a querydef but operate well.
You are talking apples and oranges. I think you've mixed up what
queries, ie, select sql statements, and relationships are.
The purpose of relationship is *NOT*, repeat, *NOT* to make the
construction of queries easier!!! In the Access query builder design
window, they are used to help you as a bonus, but again, that is *NOT*
what the purpose of relationships, ie, foreign key constraints are.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same.
Speed is not a benefit of relationships. Here, you seem to be confusing
indexes with relationships.
So is setting relationships just
more overhead in creating an app and unnecessary...or do you believe
the app should have all relationships defined?
I know some here might jump on me for saying this, but an application
without foreign key constraints/relationships is not worth its weight in
electrons.
If you aren't having problems, then your applications are reasonably
small with respect to numbers of tables and records and the
"relationship" between tables.
Salad, your knowledge of VBA and various coding techniques as
demonstrated by your responses given here are excellent and over the
past couple of years, many folks, including me, have benefited from your
posts. But given your question here, I think you really, really, really
need to read up on theory and practicality of relational database design
and structure.
I hope this was of some help. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
TIMMY! wrote: salad wrote:
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just
more overhead in creating an app and unnecessary...or do you believe
the app should have all relationships defined?
Hi Salad,
One of the basic precepts of relational database design is to have the
data structure do as much for you as possible. Presumably you are
doing much of this already with your table design, ie, allowing nulls,
setting indexes with no repeats and myriad other tasks.
In other database systems, the terms for these sorts of things are
generally called constraints - I believe that is the term used in the
ANSI/SQL standards as well.
One important constraint is what in Oracle is called the foreign key
constraint - this is the equivalent of relationships in Jet.
It's nothing whatsoever to do with speed of queries or SQL statements.
They are to do with ensuring records are not orphaned. There are many
examples were you don't want orphaned records - think of a table of
purchase orders and a table of individual transactions that are all
linked to a purchase order. You don't want the any transactions
standing around without a purchase order - it makes no sense.
FK constraints/relationships are meant to address this sort of thing.
This is relational database 101. 8)
It's a very established concept in relational design that it's dangerous
to rely on constraints that are enforced by form design:
Many of the times when I create a new query and add 2 tables together
it creates the correct relationship between the two tables.
This is not a relationship. What you are describing is a join. It's
not a constraint or a relationship and it does not prevent deletion of
linked records that should not be deleted if you don't want orphaned
records.
I
believe this is due to using a foreign key with the same name.
No, this is a simple join. A foreign key has absolutely nothing
whatsoever to do with with what you are describing. The name of the
joined fields/cloumns is immaterial.
> I
cared not about cascading deletes or cascading updates or the type of
relationship so the relationships window is clean of tables.
I think you've missed the point of what relationships are. See my
ruminating above! 8)
And if I need to, I'll create a query on the fly via code. Again, I
set the relationships. I know these queries aren't compiled for
optimacy like a querydef but operate well.
You are talking apples and oranges. I think you've mixed up what
queries, ie, select sql statements, and relationships are.
The purpose of relationship is *NOT*, repeat, *NOT* to make the
construction of queries easier!!! In the Access query builder design
window, they are used to help you as a bonus, but again, that is *NOT*
what the purpose of relationships, ie, foreign key constraints are.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same.
Speed is not a benefit of relationships. Here, you seem to be confusing
indexes with relationships.
So is setting relationships just
more overhead in creating an app and unnecessary...or do you believe
the app should have all relationships defined?
I know some here might jump on me for saying this, but an application
without foreign key constraints/relationships is not worth its weight in
electrons.
If you aren't having problems, then your applications are reasonably
small with respect to numbers of tables and records and the
"relationship" between tables.
Salad, your knowledge of VBA and various coding techniques as
demonstrated by your responses given here are excellent and over the
past couple of years, many folks, including me, have benefited from your
posts. But given your question here, I think you really, really, really
need to read up on theory and practicality of relational database design
and structure.
I hope this was of some help. 8)
Yes. It sounds like you developers that responded to me spend a lot of
time in the Relationship window.
Not doing so is one task I don't think about...or want to think about.
If I have EmpId in Employees, and EmpID in table Orders, if I add those
2 tables in the QB, a relationship line is automatically drawn. If it
does that, why do I need to add 2 tables in the Relationship window and
set relationships? What do I care if I didn't go in into the
relationships window to set the relationship....for the most part its
always done for me.
The spaghetti relationships graphically displayed in the Relationships
window from my first app turned me off from using the Relationship
window. I guess the graphical view of relationships is unnecessary for
me. It may be a necessity for others like you.
We may be talking apples and oranges. I'm glad I've helped you out in
the past.
salad wrote: TIMMY! wrote: salad wrote:
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just
more overhead in creating an app and unnecessary...or do you believe
the app should have all relationships defined?
Hi Salad,
One of the basic precepts of relational database design is to have
the data structure do as much for you as possible. Presumably you
are doing much of this already with your table design, ie, allowing
nulls, setting indexes with no repeats and myriad other tasks.
In other database systems, the terms for these sorts of things are
generally called constraints - I believe that is the term used in the
ANSI/SQL standards as well.
One important constraint is what in Oracle is called the foreign key
constraint - this is the equivalent of relationships in Jet.
It's nothing whatsoever to do with speed of queries or SQL
statements. They are to do with ensuring records are not orphaned. There
are many examples were you don't want orphaned records - think
of a table of purchase orders and a table of individual transactions
that are all linked to a purchase order. You don't want the any
transactions standing around without a purchase order - it makes no
sense. FK constraints/relationships are meant to address this sort of
thing.
This is relational database 101. 8)
It's a very established concept in relational design that it's
dangerous to rely on constraints that are enforced by form design:
Many of the times when I create a new query and add 2 tables
together it creates the correct relationship between the two tables.
This is not a relationship. What you are describing is a join. It's
not a constraint or a relationship and it does not prevent deletion
of linked records that should not be deleted if you don't want
orphaned records.
I
believe this is due to using a foreign key with the same name.
No, this is a simple join. A foreign key has absolutely nothing
whatsoever to do with with what you are describing. The name of the
joined fields/cloumns is immaterial.
> I
cared not about cascading deletes or cascading updates or the type
of relationship so the relationships window is clean of tables.
I think you've missed the point of what relationships are. See my
ruminating above! 8)
And if I need to, I'll create a query on the fly via code. Again, I
set the relationships. I know these queries aren't compiled for
optimacy like a querydef but operate well.
You are talking apples and oranges. I think you've mixed up what
queries, ie, select sql statements, and relationships are.
The purpose of relationship is *NOT*, repeat, *NOT* to make the
construction of queries easier!!! In the Access query builder design
window, they are used to help you as a bonus, but again, that is
*NOT* what the purpose of relationships, ie, foreign key constraints
are. My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same.
Speed is not a benefit of relationships. Here, you seem to be
confusing indexes with relationships.
So is setting relationships just
more overhead in creating an app and unnecessary...or do you believe
the app should have all relationships defined?
I know some here might jump on me for saying this, but an application
without foreign key constraints/relationships is not worth its
weight in electrons.
If you aren't having problems, then your applications are reasonably
small with respect to numbers of tables and records and the
"relationship" between tables.
Salad, your knowledge of VBA and various coding techniques as
demonstrated by your responses given here are excellent and over the
past couple of years, many folks, including me, have benefited from
your posts. But given your question here, I think you really,
really, really need to read up on theory and practicality of
relational database design and structure.
I hope this was of some help. 8)
Yes. It sounds like you developers that responded to me spend a lot
of time in the Relationship window.
Not doing so is one task I don't think about...or want to think about.
If I have EmpId in Employees, and EmpID in table Orders, if I add
those 2 tables in the QB, a relationship line is automatically drawn.
If it does that, why do I need to add 2 tables in the Relationship
window and set relationships? What do I care if I didn't go in into
the relationships window to set the relationship....for the most part
its always done for me.
It was just explained to you:)
What you experience is an automatic join in the Access query editor.
A relationship is an enforced link between to tables.
The spaghetti relationships graphically displayed in the Relationships
window from my first app turned me off from using the Relationship
window. I guess the graphical view of relationships is unnecessary
for me. It may be a necessity for others like you.
It's a bit of an art not crossing lines (or avoiding it).
Sounds like you've never done any formal database design? (E-R diagrams,
yada yada)
We may be talking apples and oranges. I'm glad I've helped you out in
the past.
If you notice in your query when you get an auto join it is just a line? If
you have relationships set correctly you will see the type of relationship
('many to one' usually).
It's a question of good design. Setting relationships is basic database
design.
I recommend if you are really interested in good design that you get a book
on database theory. A lot of it becomes automatic (eg. most of us normalise
our tables at least to some degree without thinking about it). It's like
maths. You may have some quirky way that seems to work ok, but it's much
better if you have a good foundation in the basic theory. If you're going to
be developing a decent size application I'd shudder at the thought of not
setting relationships between my tables. In fact I usually spend a good
amount of time just designing my tables/relationships to make sure they will
handle the requirements. Forms/etc are just nice interfaces for users.
*end rant* :)
regards,
Bradley
Br@dley wrote: Yes. It sounds like you developers that responded to me spend a lot
of time in the Relationship window.
Not doing so is one task I don't think about...or want to think about.
If I have EmpId in Employees, and EmpID in table Orders, if I add
those 2 tables in the QB, a relationship line is automatically drawn.
If it does that, why do I need to add 2 tables in the Relationship
window and set relationships? What do I care if I didn't go in into
the relationships window to set the relationship....for the most part
its always done for me.
It was just explained to you:)
What you experience is an automatic join in the Access query editor.
A relationship is an enforced link between to tables.
The spaghetti relationships graphically displayed in the Relationships
window from my first app turned me off from using the Relationship
window. I guess the graphical view of relationships is unnecessary
for me. It may be a necessity for others like you.
It's a bit of an art not crossing lines (or avoiding it).
Sounds like you've never done any formal database design? (E-R diagrams,
yada yada)
Nope. 20 years of designing database applications, nothing formal tho.
Left the tux at the cleaners, picked up swim trunks and Hawaiian
shirts instead.
We may be talking apples and oranges. I'm glad I've helped you out in
the past.
If you notice in your query when you get an auto join it is just a line? If
you have relationships set correctly you will see the type of relationship
('many to one' usually).
Yeah. I set it there in the QB, not in the relationship window. I have
no relationships in the Relationship window. My MsysRelationships table
is empty. I figure someone at MS was smart enough to check keys in
tables and set the joins in the QB if possible. Not much else to it.
I've never had the problem of creating records in the many table and not
creating records in the ones table.
It's a question of good design. Setting relationships is basic database
design.
I recommend if you are really interested in good design that you get a book
on database theory. A lot of it becomes automatic (eg. most of us normalise
our tables at least to some degree without thinking about it). It's like
maths. You may have some quirky way that seems to work ok, but it's much
better if you have a good foundation in the basic theory. If you're going to
be developing a decent size application I'd shudder at the thought of not
setting relationships between my tables. In fact I usually spend a good
amount of time just designing my tables/relationships to make sure they will
handle the requirements. Forms/etc are just nice interfaces for users.
*end rant* :)
Why? So I can echo back Codds Rules to impress people? Yawn.
It's like a many-to-many relationship. I think I had one one once in an
application...back b4 PC database systems used SQL in the language.
I've seen no need to use one since.
It appears that the Relationship window is used by quite a few. I was
curious if it was. I guess I'm missing all the fun.
regards,
Bradley
Why would you even *consider* taking on the responsibility to ensure that
every table entry will still relate correctly to every other table, before
you allow any insert, delete, and append to take place, in any form, action
query, or recordset ... when the Access data engine can do all that for you?
That would increase the development time by at least one order of magnitude,
make maintaining the database a nightmare, and leave you still uncertain you
got absolutely everything covered.
The relationship diagram is a brilliant way to view the big picture of the
database. You can fit most of an average sized database on a printed A3
page. If the database is too large, Stephen Lebans lets you break it into
blocks so you can save and restore different views: http://www.lebans.com/saverelationshipview.htm
I depend so heavily on engine-level integrity that I modified the
relationship report so I can see the field types, indexes, and the
properties that affect the relational integrity:
Relationship Report with extended field information
at: http://allenbrowne.com/AppRelReport.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"salad" <oi*@vinegar.com> wrote in message
news:So*****************@newsread1.news.pas.earthl ink.net... I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query and
add 2 tables together it creates the correct relationship between the two
tables. I believe this is due to using a foreign key with the same name.
I cared not about cascading deletes or cascading updates or the type of
relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy like
a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
salad wrote: Br@dley wrote: Yes. It sounds like you developers that responded to me spend a lot
of time in the Relationship window.
Not doing so is one task I don't think about...or want to think
about. If I have EmpId in Employees, and EmpID in table Orders, if I add
those 2 tables in the QB, a relationship line is automatically
drawn. If it does that, why do I need to add 2 tables in the
Relationship window and set relationships? What do I care if I
didn't go in into the relationships window to set the
relationship....for the most part its always done for me.
It was just explained to you:)
What you experience is an automatic join in the Access query editor.
A relationship is an enforced link between to tables.
The spaghetti relationships graphically displayed in the
Relationships window from my first app turned me off from using the
Relationship window. I guess the graphical view of relationships
is unnecessary for me. It may be a necessity for others like you.
It's a bit of an art not crossing lines (or avoiding it).
Sounds like you've never done any formal database design? (E-R
diagrams, yada yada)
Nope. 20 years of designing database applications, nothing formal
tho. Left the tux at the cleaners, picked up swim trunks and Hawaiian
shirts instead.
What does "database design" mean to you?
We may be talking apples and oranges. I'm glad I've helped you out
in the past.
If you notice in your query when you get an auto join it is just a
line? If you have relationships set correctly you will see the type
of relationship ('many to one' usually).
Yeah. I set it there in the QB, not in the relationship window. I
have no relationships in the Relationship window. My
MsysRelationships table is empty. I figure someone at MS was smart
enough to check keys in tables and set the joins in the QB if
possible. Not much else to it.
As has been said before relationships are far more important than just
making it easy to join tables when creating queries.
I've never had the problem of creating records in the many table and
not creating records in the ones table.
What do you do when data is updated/deleted? What stops data being added to
a secondary table when there is none in the parent?
It's a question of good design. Setting relationships is basic
database design.
I recommend if you are really interested in good design that you get
a book on database theory. A lot of it becomes automatic (eg. most
of us normalise our tables at least to some degree without thinking
about it). It's like maths. You may have some quirky way that seems
to work ok, but it's much better if you have a good foundation in
the basic theory. If you're going to be developing a decent size
application I'd shudder at the thought of not setting relationships
between my tables. In fact I usually spend a good amount of time
just designing my tables/relationships to make sure they will handle
the requirements. Forms/etc are just nice interfaces for users. *end
rant* :)
Why? So I can echo back Codds Rules to impress people? Yawn.
We're talking about good design, not impressions.
It's perplexing that you say you develop database applications for 20 years
yet shun Codds Rules...?
It's like a many-to-many relationship. I think I had one one once in
an application...back b4 PC database systems used SQL in the language.
I've seen no need to use one since.
Doesn't mean they are not valid/useful/etc. I have used them a number of
times (obviously implemented as three tables and one-to-many).
It appears that the Relationship window is used by quite a few. I was
curious if it was. I guess I'm missing all the fun.
For me it's not about who uses the relationship window, rather how people
design their apps. IMO start with designing the data structures
(tables/relationship/indexes) and make sure they suit the requirements (ie.
design the database). It's the basic building block on which you build the
interface/etc.
Understanding Relational Database Design http://www.support.microsoft.com/?scid=kb;EN-US;234208
--
regards,
Bradley
salad wrote: I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships.
So you don't use the database engine to enforce referential
integrity because the gui tool provided by Access annoys you?
Now I am curious. Do you create unique indexes in tables that have
an autonumber as the primary key? How about using validation rules?
Field sizes? Allow zero length strings? Required?
very cool, Allen, it went right into my "common code" database. thanks for
sharing! :)
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au... Why would you even *consider* taking on the responsibility to ensure that
every table entry will still relate correctly to every other table, before
you allow any insert, delete, and append to take place, in any form,
action query, or recordset ... when the Access data engine can do all that for
you? That would increase the development time by at least one order of
magnitude, make maintaining the database a nightmare, and leave you still uncertain
you got absolutely everything covered.
The relationship diagram is a brilliant way to view the big picture of the
database. You can fit most of an average sized database on a printed A3
page. If the database is too large, Stephen Lebans lets you break it into
blocks so you can save and restore different views:
http://www.lebans.com/saverelationshipview.htm
I depend so heavily on engine-level integrity that I modified the
relationship report so I can see the field types, indexes, and the
properties that affect the relational integrity:
Relationship Report with extended field information
at:
http://allenbrowne.com/AppRelReport.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"salad" <oi*@vinegar.com> wrote in message
news:So*****************@newsread1.news.pas.earthl ink.net... I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query
and add 2 tables together it creates the correct relationship between the
two tables. I believe this is due to using a foreign key with the same
name. I cared not about cascading deletes or cascading updates or the type of
relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy
like a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
On Fri, 28 Apr 2006 22:09:54 GMT, salad <oi*@vinegar.com> wrote:
For a long time I have had a bet going: show me any database of a
serious size that has been used for a serious amount of time, without
RI, and I will show you orphaned data.
I have not yet had to pay up.
Why tie one hand behind your back and not use the greatest asset of a
DBMS?
Might as well define all fields as varchar(255) and convert data types
yourself. Or enforce unique indexes or required fields in code rather
than have the DBMS do it for you. Ahh, a Notepad database!
-Tom. I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query
and add 2 tables together it creates the correct relationship between
the two tables. I believe this is due to using a foreign key with the
same name. I cared not about cascading deletes or cascading updates or
the type of relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy
like a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
salad wrote: If I have EmpId in Employees, and EmpID in table Orders, if I add those
2 tables in the QB, a relationship line is automatically drawn. If it
does that, why do I need to add 2 tables in the Relationship window and
set relationships? What do I care if I didn't go in into the
relationships window to set the relationship....for the most part its
always done for me.
Salad, with all due respect,
You're still not clear on what relationships in Jet are. Yes, it helps
with query design just as the intelligence of the query design window
(see note) "detects" the fields to be linked if they have the same name.
But that IS NOT WHAT RELATIONSHIPS ARE!
If you do not care to "go in into the relationships window to set the
relationship" you are, quite simply, going to fail badly any relational
database course. It sounds as if you have fallen into the sort of trap
ironically caused by the cushion provided by the excellent database
design tools in Access.
Relationships are NOT for providing an easy way to set up queries.
Relationships are the constraints that are needed to be set up to ensure
database structural integrity.
Ensuring integrity of database structure through coding only is again
grounds for failing database 101 and I've personally fired a designer
for doing this sort of thing in a critical application. When a database
application gets large and widely used, the following is an example of
what happens that simply screams for the lowest level constraints to be
used.
Frequently, the data in a successful application may need to be tweaked
from outside the application. This could be because of corporate
changes, or, commonly, a transaction has been mistakenly completed with
the wrong debit account codes and incorrect credit account codes. The
application forms might not let posted transactions (I'm talking about
G.A.A.P. stuff here) be changed once posted. Thus, when the mistake is
detected, an administrator has to go in through the back door, ie, not
through the application, through the tables themselves, and apply
insert, delete, and/or update statements to correct the data.
Note that when I'm talking about an admin here, I'm not talking about
the original developer or a normal user. I'm talking about someone who
has inherited the application and data for looking after it. This
happens when you sell your app to more than one customer.
Getting back to the admin changing data: This can be a very dangerous
activity when there are DBAs who are several times removed from the
original developer. Constraints will help such DBAs learn about the app
and help prevent disastrous things happening.
A good database designer sets his application up so that vital
constraints are at the database level.
This is what the relationship window is about. The setting up of
queries is NOT RELATIONSHIPS. It's a bit of chrome that helps, but it's
about constraining your data.
I guess the graphical view of relationships is unnecessary for
me. It may be a necessity for others like you.
Again, you have completely missed the purpose of relationships. They
are necessary for all database designs, whether or not you prefer to
have a graphical view (the Jet approach) or have to remember them or
look at them in text (Oracle as shown in Enterprise Schema Manager).
This last sentence shows you have completely missed what
relationships/constraints are about. You really need to go over this in
detail, because right now I can tell you, no offence intended and with
the greatest of respect, that you are NOT DOING RELATIONAL DATABASE
DESIGN WORK at present.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
* Tom van Stiphout: On Fri, 28 Apr 2006 22:09:54 GMT, salad <oi*@vinegar.com> wrote:
For a long time I have had a bet going: show me any database of a
serious size that has been used for a serious amount of time, without
RI, and I will show you orphaned data.
I have not yet had to pay up.
Tom, I had to chuckle when I read this. There was a time when I
probably would have taken your bet. I now know that I would have lost.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Hi, Salad. Over time I'd go into the window and see relationship
spaghetti....tables/queries all overthe place with lots of relationship
lines between here and there.
It's up to you to organize the positions of the tables in that window. If
it looks like a mess of spaghetti, then take a few minutes to organize it
better. As the database designer, you're in charge of this.
After that first app I didn't do relationships. If I had a query, I
defined the relationship.
If you dragged a field from one table to another in the QBE Design Grid,
then you defined a relationship. Otherwise, Jet defined the relationship
whenever the name of the foreign key in the child table matched the name of
the primary key in the parent table. If you planned your database well,
then Jet did all of this defining for you.
But defining a relationship doesn't mean that referential integrity will be
enforced. For referential integrity to be enforced between two tables, one
must:
1.) Create a foreign key constraint between the two tables, or
2.) Define a relationship between the two tables and ensure that the
"Enforce Referential Integrity" checkbox is marked, or
3.) Use VBA code such as the DAO.Relationship object to create the
relationship between the two tables.
Without referential integrity, orphan records can, and probably will, be
created.
so the relationships window is clean of tables.
I don't mean this disparagingly, but this tells me that you are not building
complex databases. One can get away with a lot of bad habits in simple
databases that would cause immense problems in larger, complex databases.
You haven't encountered the ill effects of a lack of referential integrity
because:
1.) You have designed your applications so that tables with one-to-many
relationships have forms/subforms for data input that prevent child records
from being created without a parent, and
2.) Either your code and queries never create records in the child tables,
or if they do, then they don't create the child records without first
checking that there's a matching record in the parent table, and
3.) Either your code and queries never delete parent records, or if they
do, then they don't delete the parent records without first checking that
there are a matching records in the child table and deleting them first, and
4.) If you don't have Table Datasheet Views locked, then your users are
behaving in that they don't edit the tables in Datasheet View.
This scenario can be successful in a small IT environment if the database
developer keeps a tight lid on things, but it requires extra work. And when
this application becomes complex, or is handed off to another database
developer for application maintenence, the lack of referential integrity on
your tables is going to become obvious, due to anomolies in the data and the
time-consuming difficulties to code for operations that the database engine
should be handling.
And when there are 25 to hundreds of tables in a database, it's very helpful
to have a diagram of the relationships between the tables. If you aren't
using diagrams, then either you have an excellent memory, or you're building
simple databases without very many tables.
My apps don't appear to suffer from no relationships.
You have relationships. What you don't have are relationships with
referential integrity enforced, and there's a huge difference between the
two in their effects on data integrity.
So is setting relationships just more overhead in creating an app and
unnecessary...or do you believe the app should have all relationships
defined?
Professional database designers define the relationships and enforce
referential integrity on tables in all of the relational databases they
design.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"salad" <oi*@vinegar.com> wrote in message
news:So*****************@newsread1.news.pas.earthl ink.net... I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query and
add 2 tables together it creates the correct relationship between the two
tables. I believe this is due to using a foreign key with the same name.
I cared not about cascading deletes or cascading updates or the type of
relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy like
a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
Correction: 3.) Use VBA code such as the DAO.Relationship object to create the
relationship between the two tables.
Should read:
3.) Use VBA code such as the DAO.Relation object to create the relationship
between the two tables.
Sorry for any confusion.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:4s******************************@adelphia.com ... Hi, Salad.
Over time I'd go into the window and see relationship
spaghetti....tables/queries all overthe place with lots of relationship
lines between here and there.
It's up to you to organize the positions of the tables in that window. If
it looks like a mess of spaghetti, then take a few minutes to organize it
better. As the database designer, you're in charge of this.
After that first app I didn't do relationships. If I had a query, I
defined the relationship.
If you dragged a field from one table to another in the QBE Design Grid,
then you defined a relationship. Otherwise, Jet defined the relationship
whenever the name of the foreign key in the child table matched the name
of the primary key in the parent table. If you planned your database
well, then Jet did all of this defining for you.
But defining a relationship doesn't mean that referential integrity will
be enforced. For referential integrity to be enforced between two tables,
one must:
1.) Create a foreign key constraint between the two tables, or
2.) Define a relationship between the two tables and ensure that the
"Enforce Referential Integrity" checkbox is marked, or
3.) Use VBA code such as the DAO.Relationship object to create the
relationship between the two tables.
Without referential integrity, orphan records can, and probably will, be
created.
so the relationships window is clean of tables.
I don't mean this disparagingly, but this tells me that you are not
building complex databases. One can get away with a lot of bad habits in
simple databases that would cause immense problems in larger, complex
databases. You haven't encountered the ill effects of a lack of
referential integrity because:
1.) You have designed your applications so that tables with one-to-many
relationships have forms/subforms for data input that prevent child
records from being created without a parent, and
2.) Either your code and queries never create records in the child
tables, or if they do, then they don't create the child records without
first checking that there's a matching record in the parent table, and
3.) Either your code and queries never delete parent records, or if they
do, then they don't delete the parent records without first checking that
there are a matching records in the child table and deleting them first,
and
4.) If you don't have Table Datasheet Views locked, then your users are
behaving in that they don't edit the tables in Datasheet View.
This scenario can be successful in a small IT environment if the database
developer keeps a tight lid on things, but it requires extra work. And
when this application becomes complex, or is handed off to another
database developer for application maintenence, the lack of referential
integrity on your tables is going to become obvious, due to anomolies in
the data and the time-consuming difficulties to code for operations that
the database engine should be handling.
And when there are 25 to hundreds of tables in a database, it's very
helpful to have a diagram of the relationships between the tables. If you
aren't using diagrams, then either you have an excellent memory, or you're
building simple databases without very many tables.
My apps don't appear to suffer from no relationships.
You have relationships. What you don't have are relationships with
referential integrity enforced, and there's a huge difference between the
two in their effects on data integrity.
So is setting relationships just more overhead in creating an app and
unnecessary...or do you believe the app should have all relationships
defined?
Professional database designers define the relationships and enforce
referential integrity on tables in all of the relational databases they
design.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"salad" <oi*@vinegar.com> wrote in message
news:So*****************@newsread1.news.pas.earthl ink.net... I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots of
relationship lines between here and there.
After that first app I didn't do relationships. If I had a query, I
defined the relationship. Many of the times when I create a new query
and add 2 tables together it creates the correct relationship between the
two tables. I believe this is due to using a foreign key with the same
name. I cared not about cascading deletes or cascading updates or the
type of relationship so the relationships window is clean of tables.
And if I need to, I'll create a query on the fly via code. Again, I set
the relationships. I know these queries aren't compiled for optimacy
like a querydef but operate well.
My apps don't appear to suffer from no relationships. Speeds very
acceptable, the results the same. So is setting relationships just more
overhead in creating an app and unnecessary...or do you believe the app
should have all relationships defined?
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:4s******************************@adelphia.com ... Hi, Salad.
Over time I'd go into the window and see relationship
spaghetti....tables/queries all overthe place with lots of relationship
lines between here and there.
It's up to you to organize the positions of the tables in that window. If
it looks like a mess of spaghetti, then take a few minutes to organize it
better. As the database designer, you're in charge of this.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database into a
new one (which is one way of fixing corrupted databases).... after doing
this many times during development, I've just "given up" on redoing the
window each time, until I have "finished" the database (of course, with my
"biggest" application, it seems I'm continually adding new features as soon
as one is done, so...... < g >)
--
Ken Snell
<MS ACCESS MVP>
Hi, Ken. One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database into
a new one (which is one way of fixing corrupted databases)
It doesn't seem like it would be that difficult to program this into Access,
does it? Oh, well. At least we have the built-in Print Relationships tool,
so Microsoft has been making some effort into making database documentation
a little easier.
If you don't have access to professional E-R diagram software, then the
Relationships window is the only way to see the table and query
relationships. When you have your tables arranged correctly, use Stephen
Lebans's tool to save the current Relationship View in a table. That way
you can restore that same view to the current database or to any other
database you import these tables into. As Allen Browne mentioned in a
previous post, you'll find Stephen's tool on the following Web page:
http://www.lebans.com/saverelationshipview.htm
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:c7******************************@comcast.com. .. "'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote
in message news:4s******************************@adelphia.com ... Hi, Salad.
Over time I'd go into the window and see relationship
spaghetti....tables/queries all overthe place with lots of relationship
lines between here and there.
It's up to you to organize the positions of the tables in that window.
If it looks like a mess of spaghetti, then take a few minutes to organize
it better. As the database designer, you're in charge of this.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database into
a new one (which is one way of fixing corrupted databases).... after doing
this many times during development, I've just "given up" on redoing the
window each time, until I have "finished" the database (of course, with my
"biggest" application, it seems I'm continually adding new features as
soon as one is done, so...... < g >)
--
Ken Snell
<MS ACCESS MVP>
"Allen Browne" <Al*********@SeeSig.Invalid> schreef in bericht news:44***********************@per-qv1-newsreader-01.iinet.net.au... The relationship diagram is a brilliant way to view the big picture of the
database. You can fit most of an average sized database on a printed A3
page. If the database is too large, Stephen Lebans lets you break it into
blocks so you can save and restore different views:
http://www.lebans.com/saverelationshipview.htm
First thing I do when I get an mdb not made by me is to check this diagram.
Also for mdb's I made some time ago.
I depend so heavily on engine-level integrity that I modified the
relationship report so I can see the field types, indexes, and the
properties that affect the relational integrity:
Relationship Report with extended field information
at:
http://allenbrowne.com/AppRelReport.html
Yeh, great report Allen!
Btw: Still having the problem with wrong links shown when the *first* fields in the various tables are not visible in the relationships-view.
(when there are vertical scrollbars in the rel-view).
Arno R
* Ken Snell:
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database into a
new one (which is one way of fixing corrupted databases)....
Yeah! I've always found that really annoying. I need to check out the
solutions from Stephen Lebans and Allen Browne, mentioned elsewhere in
this thread.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Gunny I also have another Relationship window tool in the works. I nearly
finished it several weeks ago but work in the real world caught up to me.
The solution was originally built for developers who defined a Relationship
window view that would not fit onto the max page size width supported by the
Access report object(22.75 in).
Basically, I recreated the view in the Relationship window within a PDF
document. I chose a PDF doc as you can easily send a PDF to a printing
center to be rendered on a plotter or the use the native scaling props of a
PDF reader app to output the doc to the page size of your choosing.
Allen Browne's code is used to show the properties of each relationship,
table field size etc.The two most difficult issues were the logic behind the
rendering of the Relationship Lines and creating logic to move/place and
size individual Table windows so that all fields are visible(even if they
are not in the Relationship Window view). These two issues are completely
intertwined and took several days to solve.
I should find time to finish Beta testing next week.
--
HTH
Stephen Lebans http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:vo********************@adelphia.com... Hi, Ken.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database into
a new one (which is one way of fixing corrupted databases)
It doesn't seem like it would be that difficult to program this into
Access, does it? Oh, well. At least we have the built-in Print
Relationships tool, so Microsoft has been making some effort into making
database documentation a little easier.
If you don't have access to professional E-R diagram software, then the
Relationships window is the only way to see the table and query
relationships. When you have your tables arranged correctly, use Stephen
Lebans's tool to save the current Relationship View in a table. That way
you can restore that same view to the current database or to any other
database you import these tables into. As Allen Browne mentioned in a
previous post, you'll find Stephen's tool on the following Web page:
http://www.lebans.com/saverelationshipview.htm
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:c7******************************@comcast.com. .. "'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote
in message news:4s******************************@adelphia.com ... Hi, Salad.
Over time I'd go into the window and see relationship
spaghetti....tables/queries all overthe place with lots of relationship
lines between here and there.
It's up to you to organize the positions of the tables in that window.
If it looks like a mess of spaghetti, then take a few minutes to
organize it better. As the database designer, you're in charge of this.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database into
a new one (which is one way of fixing corrupted databases).... after
doing this many times during development, I've just "given up" on redoing
the window each time, until I have "finished" the database (of course,
with my "biggest" application, it seems I'm continually adding new
features as soon as one is done, so...... < g >)
--
Ken Snell
<MS ACCESS MVP>
Stephen, it's been said many times, i know, but....for your amazing
solutions and your generosity in sharing them with the Access community -
thank you! <bows low>
"Stephen Lebans" <ForEmailGotoMy.WebSite.-WWWdotlebansdot...@linvalid.com>
wrote in message news:rq*****************@ursa-nb00s0.nbnet.nb.ca... Gunny I also have another Relationship window tool in the works. I nearly
finished it several weeks ago but work in the real world caught up to me.
The solution was originally built for developers who defined a
Relationship window view that would not fit onto the max page size width supported by
the Access report object(22.75 in).
Basically, I recreated the view in the Relationship window within a PDF
document. I chose a PDF doc as you can easily send a PDF to a printing
center to be rendered on a plotter or the use the native scaling props of
a PDF reader app to output the doc to the page size of your choosing.
Allen Browne's code is used to show the properties of each relationship,
table field size etc.The two most difficult issues were the logic behind
the rendering of the Relationship Lines and creating logic to move/place and
size individual Table windows so that all fields are visible(even if they
are not in the Relationship Window view). These two issues are completely
intertwined and took several days to solve.
I should find time to finish Beta testing next week.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote
in message news:vo********************@adelphia.com... Hi, Ken.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database
into a new one (which is one way of fixing corrupted databases)
It doesn't seem like it would be that difficult to program this into
Access, does it? Oh, well. At least we have the built-in Print
Relationships tool, so Microsoft has been making some effort into making
database documentation a little easier.
If you don't have access to professional E-R diagram software, then the
Relationships window is the only way to see the table and query
relationships. When you have your tables arranged correctly, use
Stephen Lebans's tool to save the current Relationship View in a table. That
way you can restore that same view to the current database or to any other
database you import these tables into. As Allen Browne mentioned in a
previous post, you'll find Stephen's tool on the following Web page:
http://www.lebans.com/saverelationshipview.htm
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:c7******************************@comcast.com. .. "'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM>
wrote in message news:4s******************************@adelphia.com ...
Hi, Salad.
> Over time I'd go into the window and see relationship
> spaghetti....tables/queries all overthe place with lots of
relationship> lines between here and there.
It's up to you to organize the positions of the tables in that window.
If it looks like a mess of spaghetti, then take a few minutes to
organize it better. As the database designer, you're in charge of
this.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database
into a new one (which is one way of fixing corrupted databases).... after
doing this many times during development, I've just "given up" on
redoing the window each time, until I have "finished" the database (of course,
with my "biggest" application, it seems I'm continually adding new
features as soon as one is done, so...... < g >)
--
Ken Snell
<MS ACCESS MVP>
Br@dley wrote: salad wrote:
Br@dley wrote:
Yes. It sounds like you developers that responded to me spend a lot
of time in the Relationship window.
Not doing so is one task I don't think about...or want to think
about. If I have EmpId in Employees, and EmpID in table Orders, if I add
those 2 tables in the QB, a relationship line is automatically
drawn. If it does that, why do I need to add 2 tables in the
Relationship window and set relationships? What do I care if I
didn't go in into the relationships window to set the
relationship....for the most part its always done for me.
It was just explained to you:)
What you experience is an automatic join in the Access query editor.
A relationship is an enforced link between to tables.
The spaghetti relationships graphically displayed in the
Relationships window from my first app turned me off from using the
Relationship window. I guess the graphical view of relationships
is unnecessary for me. It may be a necessity for others like you.
It's a bit of an art not crossing lines (or avoiding it).
Sounds like you've never done any formal database design? (E-R
diagrams, yada yada)
Nope. 20 years of designing database applications, nothing formal
tho. Left the tux at the cleaners, picked up swim trunks and Hawaiian
shirts instead.
What does "database design" mean to you?
Creating tables. Sometimes tables have associations with other tables.
Not much else.
We may be talking apples and oranges. I'm glad I've helped you out
in the past.
If you notice in your query when you get an auto join it is just a
line? If you have relationships set correctly you will see the type
of relationship ('many to one' usually).
Yeah. I set it there in the QB, not in the relationship window. I
have no relationships in the Relationship window. My
MsysRelationships table is empty. I figure someone at MS was smart
enough to check keys in tables and set the joins in the QB if
possible. Not much else to it.
As has been said before relationships are far more important than just
making it easy to join tables when creating queries.
I've never had the problem of creating records in the many table and
not creating records in the ones table.
What do you do when data is updated/deleted?
I usually don't delete. In the relatively few places I may need to, I
usually set flags as active/inactive. I don't change primary keys so
updates are irrelevent.
What stops data being added to a secondary table when there is none in the parent?
Common sense.
It's a question of good design. Setting relationships is basic
database design.
I recommend if you are really interested in good design that you get
a book on database theory. A lot of it becomes automatic (eg. most
of us normalise our tables at least to some degree without thinking
about it). It's like maths. You may have some quirky way that seems
to work ok, but it's much better if you have a good foundation in
the basic theory. If you're going to be developing a decent size
application I'd shudder at the thought of not setting relationships
between my tables. In fact I usually spend a good amount of time
just designing my tables/relationships to make sure they will handle
the requirements. Forms/etc are just nice interfaces for users. *end
rant* :)
Why? So I can echo back Codds Rules to impress people? Yawn.
We're talking about good design, not impressions.
It's perplexing that you say you develop database applications for 20 years
yet shun Codds Rules...?
Do I?
I remember a time where I knew an owner of a business where his
developer was going to change his system from related to flat-file. I
thought it dumb, and wrote essays to him on why not to do it. He
trusted his developer. He's now out of business.
It's like a many-to-many relationship. I think I had one one once in
an application...back b4 PC database systems used SQL in the language.
I've seen no need to use one since.
Doesn't mean they are not valid/useful/etc. I have used them a number of
times (obviously implemented as three tables and one-to-many).
I'm glad you've needed them. In the ADH, I remember reading to avoid
them if possible. I've been able to do that for years w/o breaking a sweat.
It appears that the Relationship window is used by quite a few. I was
curious if it was. I guess I'm missing all the fun.
For me it's not about who uses the relationship window, rather how people
design their apps. IMO start with designing the data structures
(tables/relationship/indexes) and make sure they suit the requirements (ie.
design the database). It's the basic building block on which you build the
interface/etc.
That's Database 101.
Understanding Relational Database Design
http://www.support.microsoft.com/?scid=kb;EN-US;234208
Tim Marshall wrote: salad wrote:
If I have EmpId in Employees, and EmpID in table Orders, if I add
those 2 tables in the QB, a relationship line is automatically drawn.
If it does that, why do I need to add 2 tables in the Relationship
window and set relationships? What do I care if I didn't go in into
the relationships window to set the relationship....for the most part
its always done for me.
Salad, with all due respect,
You're still not clear on what relationships in Jet are. Yes, it helps
with query design just as the intelligence of the query design window
(see note) "detects" the fields to be linked if they have the same name.
But that IS NOT WHAT RELATIONSHIPS ARE!
If you do not care to "go in into the relationships window to set the
relationship" you are, quite simply, going to fail badly any relational
database course. It sounds as if you have fallen into the sort of trap
ironically caused by the cushion provided by the excellent database
design tools in Access.
Relationships are NOT for providing an easy way to set up queries.
Relationships are the constraints that are needed to be set up to ensure
database structural integrity.
Ensuring integrity of database structure through coding only is again
grounds for failing database 101 and I've personally fired a designer
for doing this sort of thing in a critical application.
I'm not worried about getting fired. Nor am I worried about the way I
design databases or code. I've fixed too much crap other "developers"
have created over the years.
When a database application gets large and widely used, the following is an example of
what happens that simply screams for the lowest level constraints to be
used.
Frequently, the data in a successful application may need to be tweaked
from outside the application. This could be because of corporate
changes, or, commonly, a transaction has been mistakenly completed with
the wrong debit account codes and incorrect credit account codes. The
application forms might not let posted transactions (I'm talking about
G.A.A.P. stuff here) be changed once posted. Thus, when the mistake is
detected, an administrator has to go in through the back door, ie, not
through the application, through the tables themselves, and apply
insert, delete, and/or update statements to correct the data.
Sounds like screwed up developing to me. In reality, no user actually
needs to use forms for data entry. They can simply pound in data into
the tables. What happens then is that there is no data verification and
that's where problems occur.
Note that when I'm talking about an admin here, I'm not talking about
the original developer or a normal user. I'm talking about someone who
has inherited the application and data for looking after it. This
happens when you sell your app to more than one customer.
Sounds like screwed up developing to me. If I had customers doing that
behind my back I'd let them know they are on their own.
Getting back to the admin changing data: This can be a very dangerous
activity when there are DBAs who are several times removed from the
original developer. Constraints will help such DBAs learn about the app
and help prevent disastrous things happening.
A good database designer sets his application up so that vital
constraints are at the database level.
If someone wants to go behind me and screw things up I certainly am not
going to worry about his problem. For example, in the example you
provided, there should be a method to create adjustments once an item is
posted. The need for adjustments should be referred to me. I create a
form to do so and voila, no problem anymore.
This is what the relationship window is about. The setting up of
queries is NOT RELATIONSHIPS. It's a bit of chrome that helps, but it's
about constraining your data.
I guess the graphical view of relationships is unnecessary for me. It
may be a necessity for others like you.
Again, you have completely missed the purpose of relationships. They
are necessary for all database designs, whether or not you prefer to
have a graphical view (the Jet approach) or have to remember them or
look at them in text (Oracle as shown in Enterprise Schema Manager).
This last sentence shows you have completely missed what
relationships/constraints are about. You really need to go over this in
detail, because right now I can tell you, no offence intended and with
the greatest of respect, that you are NOT DOING RELATIONAL DATABASE
DESIGN WORK at present.
OK.
rkc wrote: salad wrote:
I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships
and set the relationships. Over time I'd go into the window and see
relationship spaghetti....tables/queries all overthe place with lots
of relationship lines between here and there.
After that first app I didn't do relationships.
So you don't use the database engine to enforce referential
integrity because the gui tool provided by Access annoys you?
Now I am curious. Do you create unique indexes in tables that have
an autonumber as the primary key?
Almost 99.999%
How about using validation rules?
No. The presentation of the error's too ugly.
Field sizes?
If text.
Allow zero length strings?
Fewer than a blue moon.
Required?
Fewer than a blue moon.
Allen Browne wrote: Why would you even *consider* taking on the responsibility to ensure that
every table entry will still relate correctly to every other table, before
you allow any insert, delete, and append to take place, in any form, action
query, or recordset ... when the Access data engine can do all that for you?
That would increase the development time by at least one order of magnitude,
make maintaining the database a nightmare, and leave you still uncertain you
got absolutely everything covered.
The relationship diagram is a brilliant way to view the big picture of the
database. You can fit most of an average sized database on a printed A3
page. If the database is too large, Stephen Lebans lets you break it into
blocks so you can save and restore different views:
http://www.lebans.com/saverelationshipview.htm
I depend so heavily on engine-level integrity that I modified the
relationship report so I can see the field types, indexes, and the
properties that affect the relational integrity:
Relationship Report with extended field information
at:
http://allenbrowne.com/AppRelReport.html
Nice work Allen.
Tom van Stiphout wrote: On Fri, 28 Apr 2006 22:09:54 GMT, salad <oi*@vinegar.com> wrote:
For a long time I have had a bet going: show me any database of a
serious size that has been used for a serious amount of time, without
RI, and I will show you orphaned data.
I have not yet had to pay up.
I'll check it out. I guess the easiest way will be to make a copy of my
database and set the relationships and see if something groans. I've
got 10 years of data in some tables so I'd have a good test base.
Why tie one hand behind your back and not use the greatest asset of a
DBMS?
The ugly error messages Access produces and displays to the end user.
What a lousy interface.
Might as well define all fields as varchar(255) and convert data types
yourself. Or enforce unique indexes or required fields in code rather
than have the DBMS do it for you. Ahh, a Notepad database!
I really don't work hard to ensure integrity.
-Tom. pi********@hotmail.com wrote in
news:11**********************@v46g2000cwv.googlegr oups.com: salad wrote: I'm curious about your opinion on setting relationships.
Well, unless you write SPs to enforce all your participation
rules, I would think *not* enforcing RI with relationships is a
PITA. It's much easier, IMO, to have Access do it for you.
This is one of the reasons I always considered MySQL before InnoDB
tables to be a toy database, because it had no engine-level
foreign-key enforcement (i.e., no RI).
A database that stores data but does not enforce entity
relationships may be a database but it is not a relational database.
I can't see how anyone would not *require* engine-level RI, as
anyone who has worked without it should have encountered the problem
of orphan records, which is a pain to clean up. I used Paradox
before it had engine-level RI, and it was really tough to keep data
properly related without creating orphans. When I started using
Access, it seemed like a great gift to be able to depend on the
database engine to insure that the data entered in child tables was
validly related to the parent table, rather than having to program
that myself.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
salad <oi*@vinegar.com> wrote in
news:%Y****************@newsread3.news.pas.earthli nk.net: It sounds like you developers that responded to me spend a lot of
time in the Relationship window.
No, not at all. I spend time there only when designing the database
schema. Once that's done, I never go back, unless for some reason I
discover I need to revise the db scheme. It happens, of course, but
I certainly don't spend a lot of time with relationships.
As to "spaghetti" in the relationships window, all you have to do is
arranged the window once to have the layout you want and save it,
and it stays that way forever. The one thing that may make it seem
like this is not the case is that a front end inherits the
relationships from the back end, but does not inherit the
relationships window layout, so you have to do it again in the front
end if you are intending to look at the relationships from the front
end (which I only do for reference, as you can't make changes
there).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Br@dley" <do***********@google.com> wrote in
news:e2**********@news-02.connect.com.au: It's a question of good design. Setting relationships is basic
database design.
It's not just an esthetic issue.
The point of good design is to produce good data. Relationships
prevent the entry of invalid data. Why would anyone want to spend
the time programming that themselves every time they edit related
tables when they can depend on the db engine to do it for them?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"salad" <oi*@vinegar.com> wrote in message
news:Uy*****************@newsread1.news.pas.earthl ink.net... rkc wrote: So you don't use the database engine to enforce referential
integrity because the gui tool provided by Access annoys you?
Now I am curious. Do you create unique indexes in tables that have
an autonumber as the primary key?
Almost 99.999%
How about using validation rules?
No. The presentation of the error's too ugly.
Field sizes?
If text.
Allow zero length strings?
Fewer than a blue moon.
Required?> Fewer than a blue moon.
At the risk of piling on let me give you my take on this. You seem to treat
your database tables as just other "pieces" of *your* Access application just
like the forms, reports, etc..
In fact the database should be considered a separate and independent thing. It
should be set up such that it that could be given to another developer who could
build a different front end using the tool of his choosing. If the db was used
thusly for an extended period of time that other developer should be able to
give that db back to you and you would find nothing wrong with the data.
Your front end is only one doorway to the db. If all the rules exist only there
then they don't exist at all. As for "ugly error messages" you can (and should)
put redundant checking in your front end so you can circumvent those and replace
them with your own "less ugly" messages, but that does NOT mean that those
mechanisms in the back end should not be in place.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
salad <oi*@vinegar.com> wrote in
news:Su***************@newsread4.news.pas.earthlin k.net: It appears that the Relationship window is used by quite a few.
You're missing the point. The relationships window is not some
cosmetic tool that is used to set up joins.
It's a fundamental part of the design of your underlying data
schema. A relationship prevents the addition of bad data. That's the
sole purpose for it (as you've noted, you don't need it to get the
Access auto-join with identically-named fields).
And anyone who doesn't use RI for enforcing the integrity of
parent/child relationships between tables is fundamentally lacking
in basic knowledge and understanding of how relational databases
work.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
salad <oi*@vinegar.com> wrote in
news:Ur*****************@newsread1.news.pas.earthl ink.net: Sounds like screwed up developing to me. In reality, no user
actually needs to use forms for data entry. They can simply pound
in data into the tables. What happens then is that there is no
data verification and that's where problems occur.
Well, consider this scenario then:
An application is built to enter data into a database. There is no
RI in the database.
Later, for different users, a different application is build for
manipulating the same database.
Now, consider that data integrity was enforced in the first
application's design, but to insure data integrity, the developer of
the second app has to re-implement the same features in the new
front end application.
This is extra work that engine-level RI enforcement makes completely
unnecesary.
Secondly, if the second developer misunderstands the data structure,
she may introduce invalid data that works fine in *her* application,
but that breaks the original application.
Using RI insures that any application running against the same
database will be completely unable to write or edit invalid data.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
salad wrote: Sounds like screwed up developing to me. In reality, no user actually
needs to use forms for data entry. They can simply pound in data into
the tables.
In a flat file, maybe, yes. In a relational database with many to many
relations (not RI relationships)? No. Not even if one follows the
natural key philosophy. UNless, of course, your user thoroughly
understands the relational concept and thoroughly understands your
structure.
What happens then is that there is no data verification and
that's where problems occur.
Assuming you have a user who meets the criteria of my last sentence,
above *AND* a _properly_ designed schema with appropriate foreign key
(what relationships in Jet are), check (limit to specific values),
unique (require unique values) and primary keys, then YOU HAVE DATA
VERIFICATION.
Note that when I'm talking about an admin here, I'm not talking about
the original developer or a normal user. I'm talking about someone
who has inherited the application and data for looking after it. This
happens when you sell your app to more than one customer.
Sounds like screwed up developing to me. If I had customers doing that
behind my back I'd let them know they are on their own.
If you sell large enough applications you will have customers doing this
"behind your back". It's their data, they own it. It is unrealistic in
the extreme to think that a large organization's financial, inventory,
and/or work accounting system (for three examples) is going to be well
enough designed to meet the needs and vargaries of *ALL* customers
without admins requiring access outside of the provided application.
Ask any well established company that deals with the three examples I
just gave, such as SCT (Banner), the Peoplesoft (MS, I think), Maximo,
Archibus and so on.
Ultimately, a database application's most important asset is the data,
not the forms.
The need for adjustments should be referred to me. I create a
form to do so and voila, no problem anymore.
I won't harp on the point that you just said the user doesn't really
need forms other than to point it out. 8)
You will quickly find that the type of adjustment needed is going to
vary greatly by customer and if you have 100s or even 1000s of customers
for a single product that your solution above is only practical if a
large enough number of your customers think it necessary. And, you will
find, that for every one who thinks it necessary, there will be others
who think such is not appropriate and don't want anything to do with it.
Indeed, all of my everyday Access work has to do with creating reporting
apps (mostly) on the data of other systems that are powered by Oracle or
even sometimes, Jet. Because our organization is one of 1000s for two
major and very expensive DB apps, it is much, much easier and more cost
effective for us to get at the data ourselves than wait for a db
development team in another country to write the forms/reports that we need.
It is precisely this sort of thing that Crystal Reports, Brio and other
third party report writers are geared toward and what MS Access is used
for quite a lot. ODBC is a wonderful thing, that's why there are
standards of compliance.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
On Sat, 29 Apr 2006 23:47:22 GMT, salad <oi*@vinegar.com> wrote:
Agreed, but that's no argument not to use relationships.
Incomprehensible error messages occur in any development system. If
it's worth the client's investment, the developer can map them to more
comprehensible ones.
In my code I always use a central error handler. I could write a
Select Case statement to provide the mapping.
-Tom.
<clip> The ugly error messages Access produces and displays to the end user.
What a lousy interface.
<clip>
Yes, I saw your screenshot that demonstrated the problem, but have not be
able to repro it, no matter what I do.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..
"Allen Browne" <Al*********@SeeSig.Invalid> schreef in bericht
news:44***********************@per-qv1-newsreader-01.iinet.net.au... I depend so heavily on engine-level integrity that I modified the
relationship report so I can see the field types, indexes, and the
properties that affect the relational integrity:
Relationship Report with extended field information
at:
http://allenbrowne.com/AppRelReport.html
Yeh, great report Allen!
Btw: Still having the problem with wrong links shown when the *first* fields
in the various tables are not visible in the relationships-view.
(when there are vertical scrollbars in the rel-view).
Arno R
Hi, Stephen.
Cool! You have the niftiest tools on your Web site! Thanks for making them
available to the rest of us.
I look forward to your next release. The sooner the better!
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Stephen Lebans" <ForEmailGotoMy.WebSite.-WWWdotlebansdot...@linvalid.com>
wrote in message news:rq*****************@ursa-nb00s0.nbnet.nb.ca... Gunny I also have another Relationship window tool in the works. I nearly
finished it several weeks ago but work in the real world caught up to me.
The solution was originally built for developers who defined a
Relationship window view that would not fit onto the max page size width
supported by the Access report object(22.75 in).
Basically, I recreated the view in the Relationship window within a PDF
document. I chose a PDF doc as you can easily send a PDF to a printing
center to be rendered on a plotter or the use the native scaling props of
a PDF reader app to output the doc to the page size of your choosing.
Allen Browne's code is used to show the properties of each relationship,
table field size etc.The two most difficult issues were the logic behind
the rendering of the Relationship Lines and creating logic to move/place
and size individual Table windows so that all fields are visible(even if
they are not in the Relationship Window view). These two issues are
completely intertwined and took several days to solve.
I should find time to finish Beta testing next week.
--
HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote
in message news:vo********************@adelphia.com... Hi, Ken.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database
into a new one (which is one way of fixing corrupted databases)
It doesn't seem like it would be that difficult to program this into
Access, does it? Oh, well. At least we have the built-in Print
Relationships tool, so Microsoft has been making some effort into making
database documentation a little easier.
If you don't have access to professional E-R diagram software, then the
Relationships window is the only way to see the table and query
relationships. When you have your tables arranged correctly, use Stephen
Lebans's tool to save the current Relationship View in a table. That way
you can restore that same view to the current database or to any other
database you import these tables into. As Allen Browne mentioned in a
previous post, you'll find Stephen's tool on the following Web page:
http://www.lebans.com/saverelationshipview.htm
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:c7******************************@comcast.com. .. "'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote
in message news:4s******************************@adelphia.com ...
Hi, Salad.
> Over time I'd go into the window and see relationship
> spaghetti....tables/queries all overthe place with lots of
> relationship lines between here and there.
It's up to you to organize the positions of the tables in that window.
If it looks like a mess of spaghetti, then take a few minutes to
organize it better. As the database designer, you're in charge of
this.
One of the "features" that I wish ACCESS would do is to remember the
organization of the Relationships window when one imports a database
into a new one (which is one way of fixing corrupted databases)....
after doing this many times during development, I've just "given up" on
redoing the window each time, until I have "finished" the database (of
course, with my "biggest" application, it seems I'm continually adding
new features as soon as one is done, so...... < g >)
--
Ken Snell
<MS ACCESS MVP>
"Allen Browne" <Al*********@SeeSig.Invalid> schreef in bericht news:44***********************@per-qv1-newsreader-01.iinet.net.au... Yes, I saw your screenshot that demonstrated the problem, but have not be
able to repro it, no matter what I do.
I guess I just have not been able to explain this well enough...
Just emailed you a zipped nothwind2003.mdb (the file I got from you) with the problem shown.
I saved the view as 'RepWrongRelationsNorthwind2003'
Some relationships that are shown are 'shifted' here due to the fact that in some tables in the saved view I scrolled down.
Only when the *first* field of every table is visible in the *saved* window the 'shifted issue' is solved.
I guess you must be able to repro this now by only running RelReport()
Arno R
'69 Camaro wrote: I don't mean this disparagingly, but this tells me that you are not building
complex databases.
Interesting. My most current database app I have designed is unlike any
I have worked with/on in 20 years. I've been able to push the
envelope on Access that I'm more than content. If it were the typical
database app I used to work with, I would have gone into another line of
business, I was so jaded with database financial apps
The apps that I write/written run businesses. Small businesses. I tend
to work with companies that do about 10 million dollars in sales. I've
written and designed apps for companies and government to manage 10-15
billion dollars but that kind of application get's boring. You do one
once or twice and then you start wondering if working at McDonalds would
be fun. Dealing with small companies lets you work with top brass, make
decisions, and you have less politics.
One can get away with a lot of bad habits in simple databases that would cause immense problems in larger, complex databases.
You haven't encountered the ill effects of a lack of referential integrity
because:
1.) You have designed your applications so that tables with one-to-many
relationships have forms/subforms for data input that prevent child records
from being created without a parent, and
2.) Either your code and queries never create records in the child tables,
or if they do, then they don't create the child records without first
checking that there's a matching record in the parent table, and
3.) Either your code and queries never delete parent records, or if they
do, then they don't delete the parent records without first checking that
there are a matching records in the child table and deleting them first, and
4.) If you don't have Table Datasheet Views locked, then your users are
behaving in that they don't edit the tables in Datasheet View.
This scenario can be successful in a small IT environment if the database
developer keeps a tight lid on things, but it requires extra work.
I would never want to work with a large IT department. Too much
politics, too much grandstanding, too much egos, too much BS.
And when this application becomes complex, or is handed off to another database
developer for application maintenence, the lack of referential integrity on
your tables is going to become obvious, due to anomolies in the data and the
time-consuming difficulties to code for operations that the database engine
should be handling.
You work in an environment I decided I detest years ago. If I had to
work in that type of environment I'd find another line of business.
And when there are 25 to hundreds of tables in a database, it's very helpful
to have a diagram of the relationships between the tables. If you aren't
using diagrams, then either you have an excellent memory, or you're building
simple databases without very many tables.
Excellent memory. Not to be facitious, but since I designed it I know
it. :-)
Rick Brandt wrote: At the risk of piling on
:-)
let me give you my take on this. You seem to treat your database tables as just other "pieces" of *your* Access application just
like the forms, reports, etc..
Why not? As far as I am concerned, data is the most important part of
an application to the client. For the developer, the method of
input/output and data manipulation is most important.
In fact the database should be considered a separate and independent thing. It
should be set up such that it that could be given to another developer who could
build a different front end using the tool of his choosing. If the db was used
thusly for an extended period of time that other developer should be able to
give that db back to you and you would find nothing wrong with the data.
Your front end is only one doorway to the db. If all the rules exist only there
then they don't exist at all. As for "ugly error messages" you can (and should)
put redundant checking in your front end so you can circumvent those and replace
them with your own "less ugly" messages, but that does NOT mean that those
mechanisms in the back end should not be in place.
Good response.
Land of Hope and Glory
Mother of the Free
How shall we extol thee
Who are born of thee?
Wider still and wider
Shall thy bounds be set
God, who made thee mighty
Make thee mightier yet...
God, who made thee mighty
Make thee mightier yet.
salad <oi*@vinegar.com> wrote in
news:N4*****************@newsread1.news.pas.earthl ink.net: '69 Camaro wrote:
[]
And when this application becomes complex, or is handed off to another
database developer for application maintenence, the lack of
referential integrity on your tables is going to become obvious,
due to anomolies in the data and the time-consuming difficulties
to code for operations that the database engine should be
handling.
You work in an environment I decided I detest years ago. If I had
to work in that type of environment I'd find another line of
business.
I work with 75% very small businesses (1-10 employees), and the
remainder with larger but still small businesses (up to 50
employees). And I have never designed a single Access application
without RI. That is not because of the IT environment, but because
it's the only right way to do it.
Sometimes the "other application developer" turns out to be *me*, 3
or 4 years after the original app was developed, when I don't recall
much about how it was implemented. RI in the back end helps prevent
me from screwing up the data because I've forgotten what I had put
into the front end.
There is no application so small that it doesn't need RI as long as
it models entities that have real-world required relationships. If
there are two tables that are related where the child table cannot
have records that are not attached to a parent record in the parent
table, then RI is *required*. It's not optional -- it's part of the
basic system you are modelling in your database.
Failure to implement RI is a failure to use the tools properly,
whatever the reason. It makes for unnecessary work, and risks the
integrity of the data stored in the database.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Tom van Stiphout wrote: On Fri, 28 Apr 2006 22:09:54 GMT, salad <oi*@vinegar.com> wrote:
<snip> Might as well define all fields as varchar(255) and convert data types
yourself. Or enforce unique indexes or required fields in code rather
than have the DBMS do it for you. Ahh, a Notepad database!
I do have the occasional place where I enforce a uniqueness on a field
via a non-unique index and code. It isn't in Jet, but in SQL Server. Jet
handles a unique index with Nulls handily, but I haven't found a way to
do it in SQL Server. (ie. a field that doesn't always have a value, but
when it does it must be different than all the other records.)
--
Bri
Bri wrote: Tom van Stiphout wrote: On Fri, 28 Apr 2006 22:09:54 GMT, salad <oi*@vinegar.com> wrote:
<snip> Might as well define all fields as varchar(255) and convert
data types yourself. Or enforce unique indexes or required fields in
code rather than have the DBMS do it for you. Ahh, a Notepad
database!
I do have the occasional place where I enforce a uniqueness on a field
via a non-unique index and code. It isn't in Jet, but in SQL Server.
Jet handles a unique index with Nulls handily, but I haven't found a
way to do it in SQL Server. (ie. a field that doesn't always have a
value, but when it does it must be different than all the other
records.)
CREATE UNIQUE INDEX [IndexName]
ON [dbo].[TableName]([FieldName])
WITH IGNORE_DUP_KEY
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Rick Brandt wrote: Bri wrote:I do have the occasional place where I enforce a uniqueness on a field
via a non-unique index and code. It isn't in Jet, but in SQL Server.
Jet handles a unique index with Nulls handily, but I haven't found a
way to do it in SQL Server. (ie. a field that doesn't always have a
value, but when it does it must be different than all the other
records.)
CREATE UNIQUE INDEX [IndexName]
ON [dbo].[TableName]([FieldName])
WITH IGNORE_DUP_KEY
Hmm, I have been just using Enterprise Manager to create the Indexes and
never saw that option in the UI. I guess I need to get under the hood
and look at the engine documentation directly.
....
OK, I just looked this option up in BOL and I see why I didn't notice it
before. This option ignores (does not insert) any duplicates being added
rather than rolling back the whole append. I'm not sure how this will
address the problem. As new records are added, if the field in question
contains a Null and there is already a field with a Null, will this not
still generate the duplicate value error?
From BOL:
"A unique index cannot be created on a single column or multiple columns
(composite index) in which the complete key (all columns of that key) is
NULL in more than one row; these are treated as duplicate values for
indexing purposes." "If an attempt is made to enter data for which there
is a unique index and the IGNORE_DUP_KEY clause is specified, only the
rows violating the UNIQUE index fail."
What I need is the equivelent of the Ignore Nulls option in Jet.
Thanks. (and sorry for hijacking this thread into a different topic)
--
Bri
"salad" <oi*@vinegar.com> wrote What stops data being added to a secondary table when there is none in the parent?
Common sense.
That's a cute quip, Salad, but it really doesn't address the issue.
Even if you are the only user, you need some programmatic way to assure that
you do not accidentally, or inadvertently, enter a child record for which
there is no parent (appropriately enough, called an "orphan"). Functionality
of the database will not be valid, if that record is orphaned, and the
proper record not entered.
Like me, you may well include code, and try to ensure that your user
interface avoids such problems, but it is comforting to have that
Referential Integrity enforced at the database engine level (e.g., in a
Relationship) just in case we don't plug all the holes with our enlightened
UI design and outstandingly sophisticated code.
There was a time, when the tools we used did not provide that "safety net,"
but it was an excellent advance when we could move from implementing
"databases" using a file system to doing so with real database software that
gave us the safety net and ability to catch our omissions and errors at the
DB Engine level.
Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Max |
last post by:
Hi. I really hope someone can help me. Going slowly insane with this
problem.
I have a two Access 2000 databases. One is the backend containing
tables and some admin queries. The other is the...
|
by: Squirrel |
last post by:
Hi everyone,
I have an Access 2002 database, split into frontend and backend, both files
on one PC for some initial test data entry. Data entry person is an
intelligent college student with no...
|
by: davegb |
last post by:
I'm totally new to relational database design. My boss has asked me to
create a database of information on the employees in our group. Seemed
to me like a simple application to learn the ropes. A...
|
by: Dixie |
last post by:
I need to delete some relationships in code. How do I know what the names
of those relationships are?
|
by: ARC |
last post by:
Hello all,
Prior to going live with my app, I have questions on relationships theory.
My prior app was done in Access 97, and I did NOT use relationships at all.
I have 65 tables in my...
| | |
by: netnewbie78 |
last post by:
Hello All,
I don't have a problem (but maybe I will after I explain). I have a
question with regards to something I saw in Access 2007. But first, a
little backstory:
I'm writing a very small...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
