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) |
+-------------------+---------------------------------------------------+

 

[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