Table Relationship Design

[JockVSJock]

I've searched previous post on here for Table Relationship design. There are some things that I understand and I think that I would have a better understanding for the whole scheme of things, if I posted what I am working on here and had some help/input on it.

I work in HR, and we keep track of information on when we bring a new employee into the company (we have our own lingo, like any other company, so you may not understand the names of the fields ) Right now, we are using a spreadsheet, which isn't any good. I created an Access DB, but it is only in one table, which I know isn't good. So what I'm trying to do is to make the tables into a relationship.

Here are the fields that I need:
Name
HS#
Position Type
Offer Type
Tent. Start Date
Create Offer Type
Sent Offer Letter
Offer Letter Reminder
Received Offert Letter
Mark Template Accepted
Aptrack
Background Check Sent
Background Reminder
Background Received
Filed I-9 Reminder
I-9 Reminder
CC
CC Leased
CC Email Changed
NAPS/PS
H/M Disp Reminder
H/M Follow Up
Close RFP
Dist 1st Day Packet
Relocation Sent
1st Bonus
1st Bonus Amount
2nd Bonus
2nd Bonus Amount
Comments

This is how I would like to organized these different tables

Name (Primary Key)
Position Type
Offer Type

Tent. Start Date
Name (Foreign Key)
Create Offer Type
Sent Offer Letter
Offer Letter Reminder

Received Offer Letter
Name (Key?)
Mark Template Accepted

Background Check Sent
Name (Key?)
Aptrack
Background Check Sent
Background Reminder
Background Received

Name (Key?)
Filed I-9 Reminder
I-9 Reminder

Name (Key?)
CC
CC Leased
CC Email

Name (Key?)
NAPS/PS

Name (Key?)
H/M Disp Reminder
H/M Follow Up
Close H/S

Name (Key?)
Dist. 1st Day Packet

Name (Key?)
Relocation

Name (Key?)
Bonus

Name (Key?)
Comments

I've marked key nexted to the name because these is one name relations or would it be a one to one relationship?

Any feedback would be great.

thanks

-Chris

 

[LittleSmudge]

Okay Chris - I think you have picked a challenging project for your first dive into relational databases.
And when, with the help of this board, you get it sorted then you really will have learned a lot!

I fear there will be few answers for you in my first reply - just lots of questions / clarification ( & some advice )

First off the usual advice for beginners:
Avoid 'like the plague' space characters in ANYTHING that you give a name to.
In some cases spaces make life difficult and in other case spaces stop certain things working all together.

Also avoid using system works for field names.
( The usual offenders for this are Date, Time, Number, Item )
I only notice "Name" in your list above.

Right then - to the questions
You seem to suggest that you are new to Relational Databases - How well do you understand 'Normalisation' and do you understand the importance of the Codd rules ?

Is this database intended to track a PERSON or a PROCESS ?

If you 'bring them in' to the Position of (say) 'bottle washer' will the same record need to track their promotion to 'Chief Cook' at some stage - or do they get deleted from the database as soon as their probation period is finished?

Is there a fixed list of options for PositionType ( note no space! ) or can the person filing it in put any text they want?
If limited list then is there ANY possibility that this list could be extended at some time in the future?

Same questions for OfferType

Do the gaps in the list imply NEW tables ( ie 12 tables ? )
I process on that assumption.

Given the PersonName in the first table is the unique object of consideration, how many TentStartDates will there ever be for that one person ? ( Choose from: 0, One, Many )

For CreateOfferType Same how many options question.
Same how many occurances question.

For SentOfferLetter Same how many occurances question.

For OfferLetterReminder Same how many occurances question.

ReceivedOfferLetter ( Is this a Yes/No or a Date ? )
MarkTemplateAccepted ( Is this a Yes/No or a Date ? )


BackgroundCheckSent ( Is this a Yes/No or a Date ? )

Aptrack What data type is this ?

For all of the rest: clarify what is Data type and is o, one or many entries per person.

BackgroundCheckSent
BackgroundReminder
BackgroundReceived

FiledI-9Reminder
I-9Reminder

CC
CC Leased
CC Email

NAPS/PS

HMDispReminder
HMFollowUp
CloseHS

Dist1stDayPacket

Relocation

Bonus

Comments


ALL of those where the MAX number of entries per person is ONE should go into the same table as the person's name - there is no reason not to. One-to-one relationships between not 'unknown' but really needing them is RARE.

You also need to consider the PRIMARY KEY of the 'main' table carefully. You cannot use the PersonName field because that would mean you could never employ two Tom Smith s. A staff Id number or something similar may do, if available. Otherwise - we might need to consider further based on the answers to a lot of the above.


I think that's enough for now.

Reply when you can and I'll dig over your responses and start helping you make some sense of all this.


G LS

 

[JockVSJock]

Acutally this isn't my first dive into a relational table DB. I used to do this alot, but that was a year ago, and I've forgotten alot of it, and plus our spreadsheet is unmangeable, so now is the time.

Understand somewhat Codds rules, but again, it has been more than one year since I have done this.

This DB is used to track a proces for a person. What was the date the offer letter was created? What was the date it was sent back in? Or did we ever receive it (query that null value)?

Position type will not be a fixed entry or drop down box. There are new positions that are created all of the time.

Yes, there will be 12 tables, I think. Unless you can see another way for me to strip it down. We also track who gets relocation and how much they get, but right now I want to nail this down.

See, I get confused what field I connect to what field and how do I want to define that relationship. Is it a 1 to 1, or a 1 to many? I think that I have the tables set up right, but again, I'm not sure how to link them, what to make my primary/foreign key and what type of relationship to set up between them.

Again, thanks for the help, and hope to hear from you real soon.

thanks

-Chris

 

[LittleSmudge]

Right on Chris.

If the field is a one-to-one match with the person then put that field in the person table DO NOT put it in a separate table just for the sake of it.

Certainly - unless you have a good reason why not I think the following makes ONE table

tblPerson
[bPersonId[/b] (Primary Key)
PersonName
PositionType
OfferType
TentStartDate
CreateOfferType
SentOfferLetter
OfferLetterReminder
ReceivedOfferLetter
MarkTemplateAccepted
BackgroundCheckSent
Aptrack
BackgroundCheckSent
BackgroundReminder
BackgroundReceived


Another table could be

tblBonus
BonusId Prime Key
PersonRef Foreign Key to Person table
BonusDate
BonusAmount
BonusReason
etc..

This give you the option of recording None, One or Many bonuses for an individual and recording the Date, Amount and reason for each bonus.

The other field will need judgement based on their meaning and their One or Many link requirement.

So you are now down to TWO tables and a lot less fields to go.


'ope this is making sense.

G LS

 

[DonQuichote]

Let me give you an advice. Use autonumbers for the primary key of any table and long integers for the foreign key fields (an autonumber is a long integer by default). Never use strings as keys. In particular, it is a mortal sin to use a key that has a meaning (even vaguely).

Let me give you an example. Say you have a table containing person data and (

tblPerson

) and a spent hours table (

tblHours

). If the foreign key in

tblHours

is name of the employee, say 'Marian Brown', you'll have a problem whenever the employee changes name (by marriage, divorce or otherwise). Because if you change the name in

tblPerson

, the data in

tblHours

is pointing to nothing and your database is corrupt.
Maybe you think it's unlikely, but it is bound to happen. And unlikely-problems are always harder to track down and therefore to solve.

So again, never ever use a key that has a meaning. Use autonumbers. And if anyone sees a meaning in that (say a sort order), use random autonumbers.

Good luck

 

[LittleSmudge]

So what ever happen to Codd's rules then DonQuichote ?

What you advise is TOTALLY against all the Relational Database Theory, Normalisation Rules .. .. et. al.

The Primary Key of a table should be the field or fields that UNIQUELY define the Relation that all the other fields have to each other.
So "NAME" is NOT a good Prime Key as I said earlier, but a random number has NO relation to ANY of the other fields in the table so it cannot possibly "uniquely define" it.

I didn't want this thread to get bogged down in the Autonumber Prime Key debate because views on this have been aired so often in the past and it won't help Chris to solve his problem ( and it's his thread after all ) - but I had to come back on that last post of DonQuichote's.


G LS

 

[Darrylles]

Hiya,

Littlesmudge - I've got to disagree with you. You can spout Codd's theory all day long, we can go to 5th normal form aswell, but how relevant is it?

DonQuichote is absolutely right - a long int auto pk for EVERY table should be used, a fk for EVERY 'many' should be a long int relationship field to the PK.

We no longer have the restrictions that necessitate the need to make a decision on which combination key fields to use to uniquely identify a record SIMPLY to save data space.

We can add a long int PK field with no loss of data access speed AND save the design time of deciding on the best unique combination of fields.

Littlesmudge - I guess your age at 50 - am I near?
I also guess that you have been either in the same place for a long time, or are very mobile.

And no, this ain't bogging Jock down - this is all debatable material, which he wanted an answer for.

Regards,

Darylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[LittleSmudge]

No Darylle - NOWHERE NEAR 50 thanks !

But I have a very healthy respect for data integrity and reliability even if that is at the expense of a few minutes of development time.

As for mobility - I'm a contractor DBAnalyst. - Hired Gun if you like. Usually brought in to sort out the mess that other people have created ( or left behind ). Which goes a long way to explain why I'm a documentation and comments addict, as well as being keen on correct structure etc.

Learnt RDBMS from Access V1 originally through trial and error, and yes that was on a 25MHz PC with a 120MegaB hard drive, so memory usage was important in those days.
However, even though memory is now virtually 'free' there are plenty of other data integrity reasons for maintaining a good structured design.



G LS

 

[JockVSJock]

Thanks for all of the input so far, here is my final results, let me know what you all think:

tblBonus
BonusID(AutoNum/PrimaryKey)
1stBonus
1stBonusAmount
2ndBonus
2ndBonusAmount
Comments

tblI-9/CC
PaperworkIDNum(AutoNum/PrimaryKey)
FiledI-9Reminder
I-9Reminder
CC
CCLeased
CCEmail
RelocationSent

tblNaps/HRID
NAPS/HRIDNum(AutoNum/PrimaryKey)
H/MDispReminder
H/MFollowUp
CloseH/S
CloseRFP
Dist1stDayPacket

tblPerson
NamdID(AutoNum/PrimaryKey)
Name
HS#
PositionTitle
OfferType
TentStartDate
CreateOfferType
SentOfferLetter
OfferLetterReminder
ReceivedOfferLetter
MarkTemplateAccepted
Aptrack
BackgroundCheckSent
BackgroundReminder
BackgroundReceived


-Chris

 

[Darrylles]

Hiya,

LittleSmudge - no disrespect intended! You're pretty active in Tek-Tips and your solutions usually hit the nail, your experience is obvious.

Also, Steve101 has given me cause for reflection after I had jumped in with 2 feet and hopefully I can keep my gob shut from now on.

I'll leave you to this Smudge - you've got your teeth into it - I would like to put my 'penneth worth' in though.
I usually call all primary key auto fields: PK, some call 'em 'tablename_PK' e.g. Bonus_PK. But the PK part makes it obvious what it is. (Same for FK, except I ALWAYS prefix FK with the table it links to, eg. Bonus_FK).

Regards

Darrylle
"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[LittleSmudge]

I use a very similar approach Darryl in that

Give a table called tblNoun
then ( for sime PK fields ) the PK field is called NounId
This is for all PKs regardless of data type

Then in other tables the FK field is NounRef
on the basis that it "Ref"ers to the PK in tblNoun


Thanks for your comments.

G

 

[LittleSmudge]

Okay Chris - back to your issue:-

So we now have FOUR table - thats okay.

What we need to sort out now is how we combine these tables together so that - given one PERSON, we can get to all of the other data that we need.

Regarding tblI-9/CC
Will this be one Person have many records in the tblI-9/CC table or will one record in the tblI-9/CC table relate to many people ?


Same question for tblNaps/HRID


As for tblBonus
I would prefer to see something like

tblBonus
BonusId(AutoNum/PrimaryKey)
PersonRef - Ref to the Person receiving the Bonus
BonusReason
BonusAmount
BonusDate - If this will be useful to you
Comments

Then you can give any individual None, One, Two - or as many bonuses as you like without having to change the design of the database/tables



We're getting there Chris.

G LS

 

[JockVSJock]

Sorry for the slow reply, but here are what I think are my final results:

TBLBonus
SSID (Primary Key)
1stBonus
1stBonusAmout
2ndBonus
2ndBonusAmount
Comments

TBLI-9/CC
SSID (Primary Key)
FiledI-9
I-9Reminder
CC
CCLeased
CCEmail
RelocationSent

TBLNaps/HRID
SSID (Primary Key)
H/MDispReminder
H/MFollowUp
CloseH/S
CloseRFP
Dist1stDayPacket

TBLPerson
SSID (Primary Key)
Name
HS#
PositionTitle
OfferType
TentStartDate
CreateOfferType
SentOfferLetter
OfferLetterReminder
ReceivedOfferLetter
MarkTemplateAccepted
Aptrack
BackgroundCheckSent
BackgroundReminder
BackgroundReceived

I think is is the best that I can get it. The SSID will link all tables together.

I only wish I had more time each day to go thru all of the Access forums and read them all and help others out...but thanks everyone for the help so far.

thanks again.

-Chris