Relationship(s) Help - Newbie Here!

[bigcoop411]

Hey guys!

This is my first post here on Access World. While researching database design related material, I stumbled across this place, which seems to have a wealth of knowledge. Hopefully, you guys will be able to lend me a hand!

So here's a brief introduction about me and my project:

I recently graduated from college with a degree in engineering; I got a job straight out of college for an oil and gas company. Currently, my employer has a program that aquaints college graduates with the company, products, processes etc. by "rotating" them through different departments.

There are two seperate phases of the program; the program in its entireity lasts for 2 yrs. Phase I lasts for 6 months where the engineers are on the same "playing field" per say, i.e. everyone is out working in the shop. In Phase II however, engineers are sent to different departments throughout the organization based on their availability / interests / skills / experience etc., doing "real" engineering work. An engineer's Phase II rotations consist of time spent in (1) manufacturing dept. (2) engineering dept. and (1) quality dept.

I've taken on a side project to create a database that helps manage & measure every facet of the program. Initially, the program only had about 20 engineers; the program is now nearly 200 engineers and growing, so managment is having a hard time keeping up, efficiently at least. When a new group of engineers are hired (typically after the end of each semester) they are given a group #. Management has been using Excel up to this point. However, I've never worked with Access before, so I'm going through a learning curve stage.

I've already read several documents on the theory behind building rational databases. I'm just getting caught up on the complexity. With y'alls help, maybe I can gain some clarity. Bringing in new eyes is always beneficial.

(whew! that wasn't brief at all)

Here are my tables:

RE Info - general information about the REs; each emp. ID is unique so it will be used as the primary key

University - where the REs graduated from

University/Degree - linking table

Degree - Degree & education info.

Committee - Every RE must be involved in a committee while in the program; members serve on these committes for 6 month durations

Experience - This is previous work related experience the RE has prior to starting with the company

RE/Training - linking table

Training - Our company is huge on continual learning. We have both mandetory online and class-room based training.

RE/Job History & Job - my intention of this linking and main table was to track when they were hired AND when they make a transition to a different rotation dept.

By this time, you all either a.) are super confused or b.) didn't read or don't care to invest this much time into it (I wouldn't blame ya). Any help would be appreciated; a picture of the relationships is attached.

-bigcoop411

 

[spikepl]

Welcome to IRL.

Now, "any help would be appreciated" is an open ended call for commitment to god-knows-what, and you might not find many takers.

Break your problem down into bits, define - or guess - what it takes to solve each bit, and call for specific help when stuck with something - one bit at a time.

 

[bigcoop411]

Haha! You're right, asking a specific question would help.

I guess I'm asking you guys to review the relationships as they are and if they make sense from the project's description. I had a really hard time deciding what the primary keys should be, especially for the linking tables. I want to make sure these are correct before working on reports and queries.

From what I understood, the primary key in one table should be the foreign key in another to complete the link. However, in all the linking tables, I had to use both the 'xxDate' and 'Employee ID' fields as primary keys because the data types didn't match; Employee ID is strictly a # while Date, is well, a date.

Please note the updated screen shot.

 

[FoundOil]

Well, that's a decent start. You are right to focus on getting your table structure right before moving on to anything else (though, I'm not saying your table structure is right yet).

Now do some reading about table naming conventions and field naming conventions. One good source is Jon's post about the Ten Commandments of Access in the General Forum. Getting your names right will save a lot of programming overhead and headache as you continue developing your database.

Also, going along with what spikepl told you, you should read Jon's "Tips on posting questions and searching for answers" thread in the General forum.

 

[stopher]

Here's a couple of points to consider...

I can't see why you have an Education/Link table. Surely the relationship between RE Info and Education is 1 "RE Info" has Many Education. So you get rid of the intermediary table and the put EmployeeID in the Education table as a foreign key. You may want to consider the other linking tables too as I suspect some of them are also redundant.

The use of the word "info" in table naming is superfluous. Table naming should simply reflect what each record represents. So "Graduate" might be appropriate (since I don't know what RE means).

You have a 1:1 relationship which is fine. However, note that in the given model, a graduate can easily exist without the corresponding Checklist. You would have to force a corresponding record to be created it that was your intention. It's much easier to just incorporate the 1:1 in the same table i.e. within "RE Info". It really doesn't matter that you have to use more columns since users will only see the relevant columns they need.

hth
Chris

 

[jdraw]

Further to the comments already offered, I would say your off to a good start by setting up a model and asking for critique/criticism. I would suggest some additional information regarding database design and relational data base principles.

For design principles including Normalization see this It's short and a very good read.

For taking some facts and building a database design with Primary and Foreign keys and why, this is a good tutorial

For some free video tutorial on the concepts of modelling, normalization, cardinality etc
see the links posted here

Since you are a recent graduate -- still in the math mode - here is a video on relational database by Chris Date. This is a free part 1 video -- go to the site and click on the video

Good luck.