Connecting Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please give me some information about the following :
I have always some doubts about table connections in msaccess. Even if I
know to do some works in msaccess always coming pu some doubts in my mind.
1. What is the use of one to many relations?
2. What is the use of many to many?
2. What is the use of connecting tables?
Once I get the clear answer and I have a good concept on that, then I can do
things in access with more confidence. I know what is the use of Primary key
and Foreign key. Would appreciate for greate favor in this regard.

(e-mail address removed)
 
Kutty,

The questions you ask get to the heart of what is meant by "database
normalization". By normalizing your tables, you make the application easier
to develop with and maintain. As it is a very important topic, although not
terribly difficult to master, I have included links to websites that can
provide more detailed information. The basics:

1. Each table should describe a single entity, and have no duplicate rows,
i.e., there is a primary key.
2. Every non-key column is fully dependent on the primary key.
3. All non-key columns are mutually independent. This prohibits storing a
calculated field such as Extd Price. Instead, this field would be calculated
in an unbound form control or in a query using the expression = [Qty] *
[UnitPrice].

A one-to-many relation is a natural relationship between different entities
(tables). For example, since a customer will generally place many orders,
Customers is the one side of a one-to-many relationship with Orders.
One-to-many relationships are typically implemented by a main form based on
the one side, with an embedded continuous subform based on the many side.
The subform control has two properties, LinkMasterFields and LinkChildFields,
which are the fields in the main and subform that contain the matching data
(the primary key and the corresponding foreign key--in this example, the
CustomerNumber). When these properties are set, the foreign key needn't be
included on the subform, Access will enter this data automatically into the
underlying table.

Many-to-many is another natural relationship--for example, a customer can
order many products, and a product can be ordered by many customers. It is
implemented by a "junction" table, which is in a one-to-many relationship
with each of the other tables.

By "connecting", I'm not sure what you mean. If you mean "define
relationships between", this enables Access to enforce referential integrity,
such that no "child" records can be entered with a parent. You may also mean
a "join" as in a query, that enables you to return a recordset with data from
more than one table, where the records match in the field by which they're
joined.

Hope that helps.

Sprinks

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Glossary of database terms:
http://www.dhdursoassociates.com/database-glossary-3.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
 
Back
Top