Better way to create tables?

[BIGGY]

I am developing a database which will track some of my companies clients bonus trips. Basically everyone is going to the same place but folks are of different levels. Level A is a 14 day trip with X number of activities, Level B is a 10 day trip with Y number of activities and so on. The major things I'll have to track are all of their personal information as well as 'Air Travel', 'Hotel Stay', and 'Recreational Activities' information.

I can put all of this information in to one table (tblTraveler), or am I better off with creating tblTraveler holding only personal information and then linking to other tables such as tblAir, tblHotel, and so on? I'm not entirely new to Access, but no pro yet for sure. Only problem with creating one table is that it would have one whole lotta fields. Not sure if it's better developing technique to split everything up.

Also, the only thing I can come up with as a primary key is the people's last names. The client numbers are the same on many of them, so that's the only thing I can come up with. The PK really is only a factor if it's best to create multiple tables for the traveler. At first I thought it was best to split everything, but now I'm seeing less reason to take all of the travelers information and dump in seperate tables since most of the time on the master reports the trip planners want everything lumped together. Perhaps I'm better off just keeping the forms clean and seperate and just one table? The reports will do the rest for me. Any input greatly appreciated.

 

[ScottGem]

The first thing you need to do is read up on Normalization. This is an all important design methodology for relational databases. You want to get to at least "3rd normal form" to eliminate as much redundancy as you can. This means that data should only exist once in your database with the exception of key fields that join the tables. So you would have tblTravelers with just their info. You would use autonumbers as your PKs. The only thing from the tblTravelers table in any other table would be the TravelerID value.

Its not clear what the rest of your tables should be. Is this database going to be used one time for one travel event or might their be multiple events over time?

 

[BIGGY]

The first thing you need to do is read up on Normalization. This is an all important design methodology for relational databases. You want to get to at least "3rd normal form" to eliminate as much redundancy as you can. This means that data should only exist once in your database with the exception of key fields that join the tables. So you would have tblTravelers with just their info. You would use autonumbers as your PKs. The only thing from the tblTravelers table in any other table would be the TravelerID value.

Its not clear what the rest of your tables should be. Is this database going to be used one time for one travel event or might their be multiple events over time?

Ok that makes sense...this TravelerID value, that could be the autonumber since the company is not assigning any numbers of it's own?

 

[The_Doc_Man]

Autonumbers are excellent for record-linking via PK/FK relationships - as long as you do NOT try to assign meaning to the numbers. Remember that for autonumbers, there is NO GUARANTEE that you will get consecutive numbers. Gaps will exist anytime someone starts to make an entry and cancels it before saving. If you can live with that, use autonumbers for TravelerID.

As to separate tables,... in a word, Yes.

The interesting thing for one-to-many relationships is that sometimes, "many" means "none." If, for example, a particular traveler DRIVES rather than flies, there will be no air travel record. Access will correctly allow this situation and will not save an empty record for that traveler. Saves space.

 

[ScottGem]

Ok that makes sense...this TravelerID value, that could be the autonumber since the company is not assigning any numbers of it's own?

With the caveats that DocMan listed, yes. The Access autonumber datatype was designed specifically to be used as a PK and only for a PK.