table design

[spinkung]

hi

i am creating a training db where people can be trained against roles. a role has a version no.

my question is would i create a role table and create a new record for each time a role is issued a new version. or do i create a new table of versions where i store the role id and version id (meaning the role table only ever has one role record)??

Thanks.

 

[llkhoutx]

Use one table with an autonumber primary key.

 

[RayMilhon]

A little more detail would get a better answer.
But assuming I understand.
You have 1 Role with multiple version numbers. I'm also assuming you have a people table for each person to be trained. I would have 3 tables

table 1: Person-All info related to the person
Person ID Autonumber primary key
name, whatever else is directly related to the person

Table 2: Role table
Role ID. Autonumber primary key
Role Description

Table 3: version table
Role ID. Long Number Foreign key to Role Table Also part of the Primary key
Version type depends on how the version is structured part of the Primary Key

Table 4: Linking table
Person ID Long number Foreign key to person table part of the primary key
role ID Long number Foreign key to role table part of the primary key
Verson See table 3 info for this line