Table Design

I am setting up a database for clients who has to taken 19 different
seminars and I want to list them all under the clients name alng with the
date completed. They also need to be able to put on a report that will be
used like a transcript of classess taken. Would a many to many relationship
be better than a one to many or any sugguests you may have.
Thanks

If each client attends many (i.e. more than one) seminars, and each seminar
is attended by many clients, that is a many-to-many relationship, and a third
(junction) table is needed to resolve the relationship. The junction table
contains foreign key fields to correspond to the primary key fields from the
main tables. Foreign keys are established by building relationships between
the tables.
If the seminars each client attends are entirely separate from seminars
other clients attend, or if youa re keeping track of information for only one
client, those are different situations. A more specific response would
require more information about what exactly you need to do.

"A. Williams" wrote:

I am setting up a database for clients who has to taken 19 different
seminars and I want to list them all under the clients name alng with the
date completed. They also need to be able to put on a report that will be
used like a transcript of classess taken. Would a many to many relationship
be better than a one to many or any sugguests you may have.
Thanks

Hi Bruce
I have three table main table- CustomerInformation, which has all of the
information I need to keep track of. The PK is CustomerID- autonumber. The
second table is the table with the information about the date they attended
the seminar and title of seminaris where I want the seminar name to appear.
The Pk for it is Seminarid-outnumber. The third table is SeminarTitle, which
has the name of all 19 seminars, Pk is Customer-autonumber, FK is
SeminarID-Text. I can not get a many-to-many relationship, what am I doing
wrong.
Need help, Thanks


"BruceM" wrote:

If each client attends many (i.e. more than one) seminars, and each seminar
is attended by many clients, that is a many-to-many relationship, and a third
(junction) table is needed to resolve the relationship. The junction table
contains foreign key fields to correspond to the primary key fields from the
main tables. Foreign keys are established by building relationships between
the tables.
If the seminars each client attends are entirely separate from seminars
other clients attend, or if youa re keeping track of information for only one
client, those are different situations. A more specific response would
require more information about what exactly you need to do.

"A. Williams" wrote:

I am setting up a database for clients who has to taken 19 different
seminars and I want to list them all under the clients name alng with the
date completed. They also need to be able to put on a report that will be
used like a transcript of classess taken. Would a many to many relationship
be better than a one to many or any sugguests you may have.
Thanks

hi,
you need a junction table for the many to many relation ship. Here is how to
achieve it.

In the Database window, click Database Diagrams under Objects, click the
database diagram you want to open, and then click Design on the Database
window toolbar.
In your database diagram, add the tables that you want to create a
many-to-many relationship between.
How?

You can add a table to your database diagram to edit its structure or relate
it to other tables in your diagram. You can either add existing database
tables to a database diagram or insert a new table that has not yet been
defined in the database. Alternatively, you can create a table or modify an
existing table with the Table Designer.

Insert a new table in a database diagram

Adding a new table to the database diagram means that you are defining a new
table that does not already exist in the database. To create a new table, you
must define the individual columns that make up the table. The table is
created in the database when you save the table or the database diagram.

In the Database window, click Database Diagrams under Objects, click the
database diagram you want to open, and then click Design on the Database
window toolbar.

To create a table in your current diagram, click New Table on the Insert menu.

–or–

Right-click in the diagram and select New Table.

To create a table in your current diagram, click New Table on the Insert menu.

–or–

Right-click in the diagram and select New Table.

Modify or accept the system-assigned table name, in the Choose Name dialog
box, and then choose OK.
A new table appears in the diagram in column properties view.
In the first cell of the new table, type a column name. Then press the TAB
key to move to the next cell.
Under Data Type, select a data type for the column. Each column must have a
name and data type.
You can set the column’s other properties, such as Scale, by using the
Columns property page.
Repeat steps 3 and 4 for each column you want to add to the table.
When you save your database diagram, the new table will be added to your
database.

Note If you create a new table, then remove it from the database diagram
before saving it to the database, the table name remains in memory until you
close the database diagram. Also, if you delete an existing table from the
database, the table name remains in memory. To use the table name again,
close and restart the Database Designer.

Add existing tables to a database diagram

Adding an existing table means that the table you want to appear in your
diagram already exists in your database. If you add a group of tables to a
diagram, any relationships that exist between the tables are also added to
the diagram.

Right click on the database diagram, and then click Add Table .

Select the table you want in the Tables list, and then click Add.

Repeat step 2 for each additional table you want to add.

Click Close.

If relationships exist between the selected table and other tables in your
database diagram, relationship lines are automatically drawn.

When you add a table to a database diagram, the definition of the table (not
the data that is stored in the table) is loaded from your database into
memory. At that point you can edit the table’s definition. For example, you
can add new columns or modify its indexes, key, relationships, or constraints.

Add related tables to a database diagram

For tables with existing foreign key constraints, you can easily add the
related tables to the database diagram.

Select one or more tables with foreign key constraints in the database
diagram.
Right-click on any of the selected tables and choose Add Related Tables.
Both those tables referenced by a foreign key constraint from the selected
table(s) and those referencing the selected table(s) with a foreign key
constraint are added to the database diagram.

Create a third table by right-clicking the diagram and choosing New Table
from the shortcut menu. This will become the junction table.
In the Choose Name dialog box, change the system-assigned table name. For
example, the junction table between the titles table and the authors table is
now named titleauthors.
Copy the primary key columns from each of the other two tables to the
junction table. You can add other columns to this table, just as you can to
any other table.
How?

You can copy columns from one table to another table in the same database
diagram or in different database diagrams. Copying a column involves only the
column definition. The data itself is not automatically transferred to the
second table as part of this process.

Tip

You can use queries to copy the column's data from the original column to
the new column. Use the Query Designer to run an update query to add the data
to the table to which you copied the columns.

In the Database window, click Database Diagrams under Objects, click the
database diagram you want to open, and then click Design on the Database
window toolbar.
In your database diagram, select the columns that you want to copy.
Click the Copy button on the toolbar. This action copies the selection from
the table and places the column and its current set of properties on the
Clipboard.
Position the cursor in the table at the location where you want to insert
the columns.
Click the Paste button on the toolbar. The column and its properties are
inserted at the new location.
When you copy a database column that has a user-defined data type from one
database to another, the user-defined data type may not be available in the
destination database. In such a case, the column will be assigned the nearest
matching base data type available in that database.

In the junction table, set the primary key to include all the primary key
columns from the other two tables.
How?

Define a primary key to enforce uniqueness for values entered in specified
columns that do not allow nulls. If you define a primary key for a table in
your database, you can relate that table to other tables, thus reducing the
need for redundant data. A table can have only one primary key.

In the Database window, click Database Diagrams under Objects, click the
database diagram you want to open, and then click Design on the Database
window toolbar.
In your database diagram, click the row selector for the database column you
want to define as the primary key. If you want to select multiple columns,
hold down the CTRL key while you click the row selectors for the other
columns.
Right-click the row selector for the column and select Primary Key. A
primary key index, named "PK_" followed by the table name, is automatically
created; you can find it on the Indexes/Keys tab of the P roperties page.
Warning If you want to redefine the primary key, any relationships to the
existing primary key must be deleted before the new primary key can be
created. A message will warn you that existing relationships will be
automatically deleted as part of this process.

A primary key column is identified by a primary key symbol in its row
selector.

If a primary key consists of more than one column, duplicate values are
allowed in one column, but each combination of values from all the columns in
the primary key must be unique.

If you define a compound key, the order of columns in the primary key
matches the order of columns as shown in the table in your database diagram.
However, you can change the order of columns after the primary key is
created. In the Column name grid, remove the columns from the primary key.
Then add the columns back in the order you want. To remove a column from the
key, simply remove the column name from the Column name list.

Define a one-to-many relationship between each of the two primary tables and
the junction table. The junction table should be at the “many� side of both
of the relationships you create.
How?

You create a relationship between two tables when you want to associate rows
of one table with rows of another.

Create a relationship in a database diagram

In the Database window, click Database Diagrams under Objects, click the
database diagram you want to open, and then click Design on the Database
window toolbar.
In your database diagram, click the row selector for the database column or
combination of columns that you want to relate to a column in another table.
While the pointer is positioned over the row selector, click and drag to the
related table.
Release the mouse button. The Create Relationship dialog box appears and
attempts to match the columns you selected with columns of the same name and
data type in the related table.
In the Create Relationship dialog box, confirm that the columns you want to
relate are shown in the Primary key table and Foreign key table lists.
Choose OK to create the relationship.
On the diagram, the primary key side of the relationship is denoted by a key
symbol. In one-to-one relationships, the table that initiated the
relationship determines the primary key side. For example, if you create a
relationship from the pub_id column in the publishers table to the pub_id
column in the pub_info table, then the publishers table is on the primary key
side of the relationship.

Create a relationship in Table Designer

Click Tables under Objects, click the table that is on the foreign key side
of the relationship, and then click Design on the Database window toolbar.
Right-click in the Table Designer and choose Relationships.
Click the New button.
From the drop-down list in Primary key table, choose the table that will be
on the primary-key side of the relationship. In the grid beneath, enter the
columns contributing to the table’s primary key. In the adjacent grid cell to
the left of each column, enter the corresponding foreign-key column of the
foreign-key table.
The table designer suggests a name for the relationship. To change this
name, edit the contents of the Relationship name text box.
Choose Close to create the relationship.
Note The creation of a junction table in a database diagram does not
insert data from the related tables into the junction table.


------------
Haris Rashid
http://www.manage-systems.com
---------------------------------------


"A. Williams" wrote:

Hi Bruce
I have three table main table- CustomerInformation, which has all of the
information I need to keep track of. The PK is CustomerID- autonumber. The
second table is the table with the information about the date they attended
the seminar and title of seminaris where I want the seminar name to appear.
The Pk for it is Seminarid-outnumber. The third table is SeminarTitle, which
has the name of all 19 seminars, Pk is Customer-autonumber, FK is
SeminarID-Text. I can not get a many-to-many relationship, what am I doing
wrong.
Need help, Thanks


"BruceM" wrote:

If each client attends many (i.e. more than one) seminars, and each seminar
is attended by many clients, that is a many-to-many relationship, and a third
(junction) table is needed to resolve the relationship. The junction table
contains foreign key fields to correspond to the primary key fields from the
main tables. Foreign keys are established by building relationships between
the tables.
If the seminars each client attends are entirely separate from seminars
other clients attend, or if youa re keeping track of information for only one
client, those are different situations. A more specific response would
require more information about what exactly you need to do.

"A. Williams" wrote:

I am setting up a database for clients who has to taken 19 different
seminars and I want to list them all under the clients name alng with the
date completed. They also need to be able to put on a report that will be
used like a transcript of classess taken. Would a many to many relationship
be better than a one to many or any sugguests you may have.
Thanks