Jerome said:
I am new to Database Design and I just can't grasp the whole relationship
thing...maybe I'm trying to think too much into it. Can someone point me to
some reference or material or some suggestions to make the whole thing click
for me.
Thanks I appreciate it.
Hi Jerome,
Access creates Relationships when you join tables (draw the lines
between tables) in the Relationships window. But it's more involved than
that...in a larger sense, it's up to you to develop & set up your db
schema in such a way that the relationships between tables are clearly
understood by Access, and therefore clearly defined (not of the dreaded
"indeterminate" type) when you create them. Access then uses the
relationships to find associated information in your db quickly and
efficiently.
As with anything, you have to start at the beginning. What you
essentially have to do is chop your data down to their most atomic and
logical components (aka "decomposing"), then figure out how to put them
all back together again in meaningful ways. Ask yourself, what are your
entities (real-life persons, places, things, or events that your db will
keep track of) and their attributes (categories of related information
relevent to each entity)? Entities & attributes = tables & fields. Once
you have determined what tables and fields your db need, then you have
to set your primary keys (using one or more fields) to uniquely identify
each record in a table.
Then once your pk's are in place, you have to tell Access how to bring
all the information together again using criteria that you will set when
you query the db (e.g. "All orders for fiscal year 1999", "All streams
in the Shenandoah River watershed", "Only those programs in the states
of Minnesota and Idaho before 1995", etc.). This is what relationships
do. To set up relationships between tables, you add one table's pk to
the other table (in which case it's known as a foreign key). To decide
which table's pk should go where, you have to figure out how entities
are related to each other:
- In a one-to-many relationship (1:m), a record in Table A can have more
than one matching record in Table B, but a record in Table B has no more
than one matching record in table A. To set up this relationship, add
the field(s) that make up the pk on the "one" side of the relationship
to the table on the "many" side. Check out the Northwinds example
database included with most versions of Access for an example of such a
relationship between the Suppliers and Products tables; one supplier can
supply more than one product, but each product has only one supplier.
- In a many-to-many (m:n) relationship, Table A can have more than one
matching record in Table B AND tblB can have more than one matching
record in tblA. This type of relationship cannot be specified to Access
as such;
you have to break up a m:n relation into TWO 1:m relationships by using
a junction or "resolver" table. You put the pk's from each of the two
tables into the resolver table, which then acts as sort of a traffic cop
between the other two tables. Again, refer to the Northwinds db for an
example...the OrderDetails table resolves a m:n relationship between the
Orders and Products tables (each Order can include one OR MORE Products,
AND each Product can be ordered one OR MORE times).
- One-to-one (1:1) relationships are rare, and generally (but not
always) mean you need to rethink your table design. In this relationship
type, tblA can have only one matching record in tblB, and tblB can have
no more than one matching record in tblA. If entities are related 1:1,
that usually (but not always) means their attributes can be combined in
a single table.
This MS support site contains links to many excellent resources on
relational database design:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;289533
Hope this helps. Good luck!
LeAnne