Question dBase Design Question and Ideas

[wildcat]

G'Day:

I am working on a dBase that will allow me to track multiple users and allow me to place them in categories based upon work sites.

The users can select the people that they want to be with at these sites (essentially their team).

What would be the best way to set up and eneter this information so that it can be managed and assigned efficiently?

I currently have a table that has all the people's names in it. I am thinking of another table that has the name, and selection 1, selection 2, etc. with each selection being another person.

I just am not sure the best way to proceed to have the most efficient and capable tracking program.

Thanks for your assistance!

 

[Atomic Shrimp]

If you place the selections across the table as fields, you're limiting the number of people that can be in a team - the users will always find a way to want one more than you have allowed, and you will be faced with a programming job to fix what is a data entry problem.

I would do it something like this:
tblStaff:
StaffID: Autonumber, primary key
(other fields describing the person - name, DOB, etc - as appropriate)

tblSites:
SiteID: Autonumber, primary key
(other fields describing the site - site name, address, etc - as appropriate)

tblJobs:
JobID: Autonumber, primary key
SiteID (foreign key - i.e. containing a link to SiteID in tblSites)
(other fields describing the job - date, description of work, etc - as appropriate)

tblJobTeams:
JobID (foreign key - i.e. containing a link to JobID in tblJobs)
StaffID (foreign key - i.e. containing a link to StaffID in tblStaff)
(other fields describing this person's role for this specific job, etc - these could be links to further tables of roles, but that might be too much complexity at this stage)

So the workflow (assuming you have some people in the staff table and some sites in the sites table) would be:

Create a new record in tblJobs, defining the site at which it will happen, etc
For each member of staff on that job, create a record in tblJobTeams, specifying the JobID, their staffID, their role on this job...

Hope this helps.

BTW, when you say 'dBase', you do just mean 'database'? - I ask because there is a specific database tool with the brand name 'dBase', which is different from Microsoft Access...

 

[wildcat]

oops - yes I meant database - not dBase.

Thanks for the insight. I will try working on this and see how well it all works.

 

[Atomic Shrimp]

One other tip...

Create tblSites and tblStaff first - then tblJobs (using the lookup wizard to create the foreign key reference to SiteID), then finally create tblJobTeams, again, using the lookup wizard to create the foreign key fields.