Assistance with my tables and relationships pls, should be fairly simple

[Brent]

I hope some one can help me with this, i need assistance
designing the tables and their relationships for a
database

being built around a driving school.

What i need (or at least i think i need) is a table with
information for the driving instructors, the students and

the appintments. What i was hoping was to have a students
form and an instructors form, both with subforms

containing information on the appointments. I.e. the user
would select and instructors form, and the subform would

list the appointments they have, and likewise a student
may be selected and the relevant appointments would be

shown. One other stipulation is that the students would
have to be able to entered into the database, with
sometimes

limited information, ie, one field relates to the license
number, but if a friend or parent rings to book the

appointment the number may not be known (that was the
primary key for the table, but since dropped for this
reason),

also appointments would obviously have to be able to be
deleted without causing harm to the data.....

i am fairly new to access but i was astonoshed at the
community spirit of these newsgroups and the willingness
of

people to help, so my request is, given the fields i have
listed below, what keys should i make primary and how

should i organise the relationships...?

many many thanks

the tabels are

Instructors

Title
Surname
Firstname
Address
Suburb
Postcode
Home Phone
Mobile
Email
InstructorId
Mobile
Email
Car Registration
Transmission
Vehicle Make/Model
Number of Clients
Date Employed
Date Left
Messages
Auto Number - unique identifier


Students

Title
Surname
Firstname
Home Phone
Work Phone
Mobile
License Number
Address
Suburb
License Class
Cost
InstructorID
Pickup Address


Appointments

Tite _student
Surname - Student
Firstname - Student
Appointment Date
Appointment Time
Instructor ID
Pickup Address
License Number - Student
Comments

 

[Armen Stein]

I hope some one can help me with this, i need assistance
designing the tables and their relationships for a
database

being built around a driving school.

What i need (or at least i think i need) is a table with
information for the driving instructors, the students and

the appintments. What i was hoping was to have a students
form and an instructors form, both with subforms

containing information on the appointments. I.e. the user
would select and instructors form, and the subform would

list the appointments they have, and likewise a student
may be selected and the relevant appointments would be

shown. One other stipulation is that the students would
have to be able to entered into the database, with
sometimes

limited information, ie, one field relates to the license
number, but if a friend or parent rings to book the

appointment the number may not be known (that was the
primary key for the table, but since dropped for this
reason),

also appointments would obviously have to be able to be
deleted without causing harm to the data.....

i am fairly new to access but i was astonoshed at the
community spirit of these newsgroups and the willingness
of

people to help, so my request is, given the fields i have
listed below, what keys should i make primary and how

should i organise the relationships...?

many many thanks

the tabels are

Instructors

Title
Surname
Firstname

Hi Brent,

You're on the right track.

First, a couple of recommendations.

1. Don't use spaces or hyphens in your field names. The best
convention is to push all the words together, like AppointmentDate or
FirstName. If you want to use a suffix, use an underscore (_) to add it
on.
2. Use an AutoNumber key as the primary key for every table, and use it
to connect tables together using relationships to a Long Integer field
of the same name in the related table. This field is called a foreign
key. For example, your Appointment table should have the InstructorID
and the StudentID as foreign keys, but no other fields from those
tables, since they can all be displayed using joins.

Another thing to consider: Can an Instructor ever teach more than one
Student at a time? If so, your structure may need to be enhanced with
another level, perhaps a Session table. An Instructor would have many
Sessions, and each Session could have one or more SessionStudents.

Remember, any time you see the same data fields (other than primary keys
or the foreign keys used to join to them) in multiple tables, you
probably have a design error.

Hope this helps,

 

[Mike Sherrill]

2. Use an AutoNumber key as the primary key for every table,

Oh, please don't do that. Autonumbers aren't a suitable substitute
for thinking.

 

[Duane Hookom]

I agree that there are no "set in concrete" rules regarding primary keys...
however, every table that I have created in the past 5 years has a primary
key of an autonumber. This includes both Access and SQL Server tables that
are used both on local networks and through web pages. I have never
regretted using autonumbers.

If being consistent with table structure conventions isn't thinking then
consider my actions ill-advised.

To each his/her own.

 

[Armen Stein]

Oh, please don't do that. Autonumbers aren't a suitable substitute
for thinking.

Hi Mike,

There has been quite a lot of debate over the years about AutoNumber
primary keys (Identity key in SQL Server). My conclusion is that *most*
of the time, every table should have a primary key consisting of one
AutoNumber field.

Here are some reasons:

1. It is rare to find a meaningful primary key that will never change.
Although Access does have a cascading update feature, it is not a good
feature to rely on for a robust multi-user database.

2. Meaningful keys require multi-field primary keys for child tables.
In complex databases, these get unwieldy in a hurry. AutoNumbers
require only one primary key field in each table, regardless of how many
parent tables are above it.

3. Tables with AutoNumber keys can still have other fields defined as
unique indexes to prevent inappropriate duplicates. Using the
uniqueness of a primary key is not a good reason to store meaningful
data in it.

An exception to the primary key "rule" that we use in our shop:

There are tables that hold static values (e.g. OrderStatus). When these
values are referenced in code, we prefix the table with "tval" and omit
any user interface for adding or deleting records. In these tables, we
set the keys to values that we want, instead of using AutoNumbers. This
ensures that during development we don't end up with non-intuitive keys
that must be used directly in code.

The bottom line is that I've seen far more errors in database design
caused by *not* using AutoNumber keys than by using them. I don't mean
to substitute them for thinking - but I do *think* that they are
appropriate most of the time.

Thanks for giving me an opportunity to clarify,

 

[Duane Hookom]

Amen, Armen

 

[Mike Sherrill]

I agree that there are no "set in concrete" rules regarding primary keys...
however, every table that I have created in the past 5 years has a primary
key of an autonumber.

Why? We know there are no autonumber attributes at the conceptual
level of design, and there are no autonumber attributes at the logical
level of design.

The physical level of design is the only one left. What problem at
the physical level are you trying to solve by putting an autonumber
primary key in /every/ table?

If being consistent with table structure conventions isn't thinking then
consider my actions ill-advised.

Can't everybody on the planet do exactly what you're doing without
thinking?

 

[Mike Sherrill]

There has been quite a lot of debate over the years about AutoNumber
primary keys (Identity key in SQL Server). My conclusion is that *most*
of the time, every table should have a primary key consisting of one
AutoNumber field.

Why should anyone listen to your conclusions? Or my conclusions?
Simply because we have them? Surely not.

1. It is rare to find a meaningful primary key that will never change.

Are you saying that values in primary key columns *must* never change?
If so, what do you base that claim on?

Are you implying that values in candidate key columns must never
change? If so, on what basis do you make that claim?

Although Access does have a cascading update feature, it is not a good
feature to rely on for a robust multi-user database.

Are you saying we can't *rely* on cascading updates? If so, what do
you base that claim on?

Are there other features we can't rely on? Cascading deletes?
Foreign key references? Validation rules?

Should we avoid cascading updates on SQL Server, DB2, Oracle,
Interbase, or Mimer SQL? Or only on Access/Jet?

2. Meaningful keys require multi-field primary keys for child tables.
In complex databases, these get unwieldy in a hurry. AutoNumbers
require only one primary key field in each table, regardless of how many
parent tables are above it.

"Unwieldy" is fuzzy. A bag of dog food (about 40 pounds) is unwieldy
for a five-year-old child, but not for me.

If you're using Access's graphical interface, you can drag and drop
multiple columns; having done that, you pick matching columns from
combo boxes. If that's too much trouble for a developer, maybe some
other line of work would be a better choice.

3. Tables with AutoNumber keys can still have other fields defined as
unique indexes to prevent inappropriate duplicates.

*Must* have other columns declared unique. And when you define a
unique constraint on one or more columns, you have a candidate
key--another possible target for a foreign key constraint.

Using the
uniqueness of a primary key is not a good reason to store meaningful
data in it.

I've never heard *anyone* say that you shouldn't store meaningful data
in a primary key because values in a pk column are unique. I'm not
even certain that *you're* saying that.

Are you?

An exception to the primary key "rule" that we use in our shop:

There are tables that hold static values (e.g. OrderStatus). When these
values are referenced in code, we prefix the table with "tval" and omit
any user interface for adding or deleting records. In these tables, we
set the keys to values that we want, instead of using AutoNumbers. This
ensures that during development we don't end up with non-intuitive keys
that must be used directly in code.

Are these tables part of an application or part of the database?

The bottom line is that I've seen far more errors in database design
caused by *not* using AutoNumber keys than by using them.

I've seen far more errors in database design caused by *not* thinking
about keys and constraints than by thinking about them.

I don't mean
to substitute them for thinking - but I do *think* that they are
appropriate most of the time.

You seem to be saying

a) Your shop has rules that determine when to use autonumber primary
keys.
b) The rules say that every table gets an autonumber primary key, with
one exception.
c) The one exception--tables whose contents don't change.

Did I misunderstand you?

 

[Duane Hookom]

Apparently you have an issue with others' opinions. I think both Armen and I
have suggested a strategy that works well for us and many others. I have
never regretted creating a single primary key field using an Autonumber in
Access or an Identity field in SQL Server. Users never see the values and
they create excellent primary and foreign keys.

I have inherited applications that used a multiple key primary key or an
inappropriate natural primary key. Some of these were disasters.

If you have had better luck, fine.

 

[Guest]

Mik
If you use the autonumber can that be the joining field in every tabl

----- Mike Sherrill wrote: ----

On Thu, 22 Jan 2004 07:22:17 -0800, Armen Stei

2. Use an AutoNumber key as the primary key for every table,

Oh, please don't do that. Autonumbers aren't a suitable substitut
for thinking

 

[John Vinson]

Mike
If you use the autonumber can that be the joining field in every table

Well, you can't join autonumber to autonumber - the foreign key must
be a long integer.

Mike's valid point is that making a record unique by adding an
Autonumber provides NO protection against entering the same record
with identical (or partially identical) data multiple times.

 

[Mike Sherrill]

Apparently you have an issue with others' opinions.

Not at all. I just recognize that opinions differ in content and
value, and we need to choose among differing opinions.

If I understood correctly, Armen expressed these opinions. In some
cases, I had to to read between the lines.

1. Values in primary key columns must never change.

2. Cascading updates are unreliable.

3. Multi-column keys are "unwieldy".

4. Tables that have autonumber primary keys can have other candidate
keys.

5. But tables can have no candidate key besides the autonumber.

6. "Using the uniqueness of a primary key is not a good reason to
store meaningful data in it." I'm pretty sure this is one of his
opinions, but I'm not sure what he means by it.

7. When values from tables of valid values are referenced in code,
the table names should have a prefix that identifies them as tables
(not views) and as containing validation values (I guess).

8. When values from tables of valid values are referenced in code,
the tables should have no interface for changing rows.

9. He has seen far more errors in database design caused by *not*
using AutoNumber keys than by using them.

(I might have missed an opinion or two.)

Most of these opinions imply a factual basis. I just want to know
what it is. If cascading updates are unreliable, I want to know why,
when, and how.

In case you've forgotten, the opinion I expressed was "Autonumbers
aren't a suitable substitute for thinking."

I think both Armen and I
have suggested a strategy that works well for us and many others.

Usage doesn't imply value.

You've both recommended a convention that is used widely. That
doesn't mean it works well for everyone. The fact that it's used
widely doesn't even mean it works well for you. Years ago, global
variables and implicit type declarations were used widely in code.
Even when a programming platform supported nothing *but* global
variables and implicit typing, programmers couldn't build complex
applications without thinking hard about their variables and types.

How might your strategy fail? First, every additional table requires
an additional join. If you need data from ten tables, you need nine
joins. Second, inexperienced database designers often get the
referential constraints wrong (they don't enforce referential
integrity), sometimes leaving orphan rows; errors like that are
relatively easy to fix when you have real data stored in the table's
key columns, but damned hard when all you have is "2137". Third,
inexperienced database designers often think that the only key their
tables need is an autonumber pk; this leads to things like invoice
number 105 (or names like "Smith, John") being entered five times,
each referring to the same invoice (or person), but each having a
different autonumber pk value.

I have
never regretted creating a single primary key field using an Autonumber in
Access or an Identity field in SQL Server.

Then your experience might be too narrow to support your opinions. I
have regretted a great many things, myself.

I have inherited applications that used a multiple key primary key or an
inappropriate natural primary key. Some of these were disasters.

I've inherited databases that used autonumber primary keys on every
table. All of them were disasters.

What's your point? That careless database design can lead to
disaster? If that's your point, then I fully agree with you.

If your point is that everyone can avoid careless database design by
hanging an autonumber primary key on every table, then I think I'd
like a little of whatever you're smoking.

 

[Duane Hookom]

You have many good points. I draw mine from my experience and you draw from
yours. I try not make my opinions seem like they are the only acceptable
alternative even though I rarely stray from my basic "design rules".

When I find a method/solution that always works for me every time, I am not
bashful about recommending it to others.