Designing database

[Kristin]

I'm designing a database that I think will require some
many-to-many relationships. Can you help me with how to
set those up?

For example, the main table will be called Projects, with
fields for Project Name, Project Type, Priority, Due
Date. I haven't decided whether to use Project Name as
the primary key or an autonumber.

A second table would be called Employee. Right now it
would have First Name, Last Name, Initials, Email, Phone.
I'm using Initials as the Primary Key.

In this case, one Project can have multiple employees
working on it and each employee works on multiple
projects. To create the many-to-many relationship, do I
have to set up a Junction table? If so, how do I do that?

Thanks,
Kristin

 

[Immanuel Sibero]

Hi,
See comments below

Hope this helps

Immanuel

I'm designing a database that I think will require some
many-to-many relationships. Can you help me with how to
set those up?

For example, the main table will be called Projects, with
fields for Project Name, Project Type, Priority, Due
Date. I haven't decided whether to use Project Name as
the primary key or an autonumber.

Project Name is probably not a good choice here. I would use autonumber or
any numbering/coding system that is currently being used within your
organization. For example: ProjectNo

A second table would be called Employee. Right now it
would have First Name, Last Name, Initials, Email, Phone.
I'm using Initials as the Primary Key.

As in the Projects table, initials may not be a good choice for primary key.
I would use autonumber or some other unique identifier (i.e. Employee
Number, SS#, etc.) For example: EmployeeNo

In this case, one Project can have multiple employees
working on it and each employee works on multiple
projects. To create the many-to-many relationship, do I
have to set up a Junction table? If so, how do I do that?

Correct, you would need a junction table (i.e. a third table). This table
would at least contain two fields: ProjectNo and EmployeeNo (i.e. the two
fields which are designated as primary keys of the Projects and Employee
tables). Once this table is created, you would create the relationships
under Tools>>Relationships.

 

[John Vinson]

I'm designing a database that I think will require some
many-to-many relationships. Can you help me with how to
set those up?

For example, the main table will be called Projects, with
fields for Project Name, Project Type, Priority, Due
Date. I haven't decided whether to use Project Name as
the primary key or an autonumber.

Sort of depends on whether the project name is likely to change. A
Primary Key should ideally be unique, stable, and short. Names that
are useful for humans often fail on all three criteria!

A second table would be called Employee. Right now it
would have First Name, Last Name, Initials, Email, Phone.
I'm using Initials as the Primary Key.

Ummm... and if you have to decide whether KJ is Kristin Jones, Kevin
Jenkins, Keith Jarret or Kali Jandaharrian...? Names are BAD primary
keys, initials are even worse. I'd strongly suggest using an
employeeID that's known to be unique, or an Autonumber.

In this case, one Project can have multiple employees
working on it and each employee works on multiple
projects. To create the many-to-many relationship, do I
have to set up a Junction table? If so, how do I do that?

Exactly. The fact that an employee is assigned to a project is a
real-life fact with its own attributes, just as an Employee is a
real-live person with a name, and a Promect is a real-life entity with
a name and description. You'ld create a table ProjectAssignments
(say), with fields Initial (or as I suggest above, EmployeeID; use a
Long Integer if EmployeeID is an autonumber), ProjectID (ditto), and
whatever information you need to know about this employee's assignment
to this project - e.g. date assigned, role within the project, etc.

It would be convenient to have a Form based on the employee table,
another based on the Project table, and a Subform based on the
ProjectAssignments table on each.

 

[Kristin]

Thank you for your advice. I created Autonumbers for
employee ids because I don't have available ID numbers.
I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Also, the role field will not accept entries.

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Thanks,
Kristin

 

[Matthew Mark]

First and very important. I've seen many developers create
primary or foreign keys from text or string columns. This
is not a good choice. While it makes it easier for those
inexperienced in writing queries it makes updating and
clumsy and slows down the operation.

You almost always want an autonumber as the primary key.
Then you make the text column to not allow duplicates. The
big reason, if later down the road, (it will happen) a
user or even if you decide (Oh, I shouldn't have called it
for example "Project Name" but I should have called
it "ProjectName" to avoid spaces), if you have used the
text value, you need to update it everywhere. However, if
you only used the name in one table and in every other
table you used a long integer or autonumber as
foreign/primary keys, you can do it in one change in one
table.

Regarding Employee. Do not, I repeat do not use initials
as a primary key. The idea is to use something that is
unique. John Allan Smith, Judy Alice Sands both have the
same initials.

9 years ago I started out doing all those things I just
told you not to do. That's how I know now not to do them.

It will do you a lot of good to read up on coding
standards. I know it's hard knowing what you shouldn't do
before you do it. But if I knew then what I know now, I'd
have designed much more efficent, easy to understand
databases. Use frm before forms, mcr before macros, qry
before queries etc. Do not use spaces in Table or column
names. The first word of each new word is capitalized.
FirstName, LastName, HomeAddress etc. It makes it easy to
understand and when you start writing code, you will be so
pleased with yourself for not using names with spaces in
them. In access you can put what the user will see in the
caption property when designing the tables.

By the way, now I work mainly with Sql server, visual
basic and access. Previously, I've worked with Oracle for
a couple years. In Oracle the names are in Caps and they
use under scores to differentiate the beginning and ending
of words such as FIRST_NAME, LAST_NAME, HOME_ADDRESS etc.

 

[John Vinson]

On Tue, 4 Nov 2003 10:50:44 -0800, "Kristin"

Answers inline.

Thank you for your advice. I created Autonumbers for
employee ids because I don't have available ID numbers.
I'm now trying to create a main data entry form. The form
will be based on the project, as the list of employees
won't change often and can be done in datasheet view at
this point.

Sounds reasonable...

I've designed the project form to contain all of the
necessary descriptive pieces, but I'm now trying to create
a subform that will allow me to set the employees from
that form. I created a subform that has the following
Record Source:

SELECT Employee.Last, ProjectAssignments.Role,
ProjectAssignments.[Project ID] FROM Employee INNER JOIN
ProjectAssignments ON
Employee.EmployeeID=ProjectAssignments.[Employee ID];

You don't need to - and shouldn't - include the Employee table itself
on the subform. Just base the Subform on the ProjectAssignments table.
The only controls that you need to put on the Subform are a Combo Box
based on the Employee table; it should be bound to
ProjectAssignments.EmployeeID but should display enough of the
employee's name (I'd say last and first) to uniquely identify the
employee. You'ld then have either a textbox or another combo box to
select or enter the role.

This has given two combo boxes on the subform that seem to
allow me to enter multiple individuals. However, when I
attempt to enter employee names, I get a message "Field
cannot be updated". It does let me add the individuals
but updates the employee table, not the Project
Assignments table.

That's because you have the combo bound to the EmployeeID from the
Employee table - you want it bound to the EmployeeID from
ProjectAssignments instead!

I'd rather have the subform use the "Last" field (or
perhaps the initials field) because I don't know the
employee number.

Why not both, or all three? The nice thing about a Combo Box control
is that it can *store* a numeric ID, while it *displays* something
else. You could, for instance, create a Query

SELECT EmployeeID, [LastName] & ", " & [FirstName]
FROM Employees
ORDER BY LastName, FirstName;

and use it as the rowsource for the combo. If you use the combo box
wizard it will offer to make the EmployeeID invisible (by setting its
column width to zero in the ColumnWidths property of the combo);
you'll see "Jones, Kristin" and the computer will see 1 and you'll
both be happy.

Also, the role field will not accept entries.

What's its datatype? Text? Does the subform just have a textbox or
what?

I created the form and subform with a Wizard, which isn't
very helpful, as I'm sure you're aware.

Well... they're not very clever, I'll grant you.