design question (repeating fields)

[Bruce Bowler]

I'm a database newb, so please forgive the following, potential stupid,
question.

We have research trips that we participate in, collecting data every few
minutes. That part of the database is fairly simple. One thing that we
would like to keep track of is who was on the trip. There is a pool of
about 10 people who can go, with some trips having 1 person, and some
trips having 5.

Some options I've considered.

In the "trip" table, which contains meta-data about the trip, have 5
columns called P1, P2, ..., P5, putting one persons name in each (or null
if there was no Px).

In the "trip" table having 10 columns, 1 for each member of the pool,
simply putting a Y/N in their column for each trip.

Both of those "solutions" have problems. What if we take 6 people on a
trip? What if the pool of 10 grows to 12 and/or people leave and we hire
new people?

Is there a better (ie more flexible) method for storing data like this?

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | We stumble over pebbles, not mountains. -
| Anonymous
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Allen Browne]

If one trip can involve many people, it seems you will need 3 tables:
- Employee: one record for each person, with an EmployeeID primary key.
- Trip: one record for each trip, with a TripID primary key.
- TripEmployee: one record for each person in each trip.

So, if Trip 18 contains 6 people it will appear on 6 records in the 3rd
table.

Another example:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

 

[Bruce Bowler]

Thanks!

Mind if I ask a semi-related question?

Is there an "authoritative" tome (preferably short that talks about
the ins and outs of database design? Pitfalls and pratfalls the should be
avoided? Etc etc etc.

and an unrelated question (about this news group). In general is this a
"top posting", "bottom posting" or "what ever floats your boat" group?
I've seen examples of both in the group, without a lot of subsequent
flaming for 1 or the other so I assume "what ever" is the norm, but want
to make sure before I piss people off. (this is top posted because that's
what you did, in case I'm wrong

Bruce

If one trip can involve many people, it seems you will need 3 tables: -
Employee: one record for each person, with an EmployeeID primary key. -
Trip: one record for each trip, with a TripID primary key. -
TripEmployee: one record for each person in each trip.

So, if Trip 18 contains 6 people it will appear on 6 records in the 3rd
table.

Another example:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

--
+-------------------+---------------------------------------------------+
Bruce Bowler | Lose an hour in the morning and you will be all day
1.207.633.9600 | hunting for it. - Richard Whately
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Evan Keel]

I haven't found a short, authoritative tome. But if you understand
normalization at least to 3rd normal form you will have a pretty good
design. The rules are simple:

1. No repeating items. This means no columns like Month1, Month2, Month3,
etc. And each column represents one concept. So no column data like
1,"Wilson", "Michigan"

2. In the case where a table has a composite key, all non-key columns must
be dependent on the full key

3. No non-key dependencies

If you get this far you will eliminate insert, update, and delete anomalies.

Google "Normalization"

Evan

 

[John W. Vinson]

Is there an "authoritative" tome (preferably short that talks about
the ins and outs of database design? Pitfalls and pratfalls the should be
avoided? Etc etc etc.

"God must love standards, She made so many of them..." <g>

Here are some links to tutorials and resources. Note that they don't all agree
with one another on all details.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

and an unrelated question (about this news group). In general is this a
"top posting", "bottom posting" or "what ever floats your boat" group?
I've seen examples of both in the group, without a lot of subsequent
flaming for 1 or the other so I assume "what ever" is the norm, but want
to make sure before I piss people off. (this is top posted because that's
what you did, in case I'm wrong

There's not a strong group culture on the subject, and some regulars top post;
some bottom post; and some (like me) Do It Right, trimming and interposting.

 

[Armen Stein]

Is there an "authoritative" tome (preferably short that talks about
the ins and outs of database design? Pitfalls and pratfalls the should be
avoided? Etc etc etc.

I like Database Design for Mere Mortals by Michael Hernandez. I don't
agree with everything (for example I name all my tables in the
singular, not the plural), but it's a great explanation to start out.
It's easy to read, too - not like the insomnia cure many database
design books offer.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com