Re: Relationships. Does anyone use them?

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

.

Relevant Pages

  • Re: Relationships. Does anyone use them?
    ... If I have EmpId in Employees, and EmpID in table Orders, if I add ... Relationship window and set relationships? ... Sounds like you've never done any formal database design? ...
    (comp.databases.ms-access)
  • Re: Relationships. Does anyone use them?
    ... If I have EmpId in Employees, and EmpID in table Orders, if I add ... Sounds like you've never done any formal database design? ... 20 years of designing database applications, ... I set it there in the QB, not in the relationship window. ...
    (comp.databases.ms-access)
  • Re: Relationships. Does anyone use them?
    ... You have the niftiest tools on your Web site! ... See http://www.QBuilt.com for all your database needs. ... I recreated the view in the Relationship window within a PDF ...
    (comp.databases.ms-access)
  • Re: Associating records in same table.
    ... How big is your database? ... Open the Relationship window, put the table twice in the window (Access ... It sounds like both you and Gina are suggesting that, ... that I would need to set up a junction table between two ...
    (microsoft.public.access.forms)
  • Re: Relationships. Does anyone use them?
    ... I recreated the view in the Relationship window within a PDF ... organization of the Relationships window when one imports a database into ... database documentation a little easier. ...
    (comp.databases.ms-access)