Relationships Question

[SaraMegan]

Hi!

I am about to start up a new database for HR tracking their positions, vacancies, applications, etc. It's a bit more complex than my first database, so I'll probably be poking around a lot. (You all have been so great so far, and my first database went over quite well!)

Anyway, my question is this:

Any time I have data which repeats, should I create a separate table for it? I have a table with four fields and only one completely unique value - should all the other fields get their information from another table?

I'll just take a part of the db and give an example that way:

tblPosition:

PositionNumber (Primary Key)
CostCenter
ClassCode
Location

tblTitle:

ClassCode (Primary Key)
Title

tblLocation:

LocID (Primary Key)
LocalOffice

tblCostCenter:

CostCtrID (Primary Key)
CostCenter

Is that what I want to do? Even though the CostCtrID and LocID fields will just be random numbers that are never used for anything?

Thanks for your help in advance. I just want to make sure I know what I am doing so I only have to do it once this time.

--Sara

 

[Graham T]

Sara

The design looks to be fine.

The ClassCodeID, LocID and CostCtrID will be stored in your tblPosition table as Foreign Keys and related via a one-to-many relationship.

If you need to return actual textual values for these then you would create a query to do this.

HTH

Graham

 

[Cosmos75]

Helpful links, hopefully...

Database Normalization Tips
by Luke Chung
President of FMS
http://www.fmsinc.com/tpapers/genac...tabasenorm.html

Primary Keys
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30370

 

[RichMorrison]

Sara,

You wrote
<<
Any time I have data which repeats, should I create a separate table for it?
>>

In general, yes.

In your example, I think you want to change tblPosition. It should contain the "ID"s of the other tables. Like this:

tblPosition:

PositionNumber (Primary Key)
CostCenterID,
ClassCode,
LocID

So if we change Local Office "West Treestump" to "North Rabbittail", you make ONE change in tblLocation.

RichM

 

[SaraMegan]

Thanks, Graham. I thought I was going in the right direction, I just wanted to be sure.

If I want to return other values, could I just use the lookup wizard when designing my table?

--Sara

 

[SaraMegan]

Wow, I got a lot of replies really quick and didn't even realize it.

Cosmos: Thanks for the links! I can't get the first one to work, but the second was quite helpful.

RichM: I was planning to use a lookup to the other tables, so that tblPosition showed the actual data instead of an ID number that the user wouldn't be able to decipher. I thought using the names of the ID numbers would confuse, so I was planning on just using the name for the data the user is viewing. Does that make sense? Is it a bad idea?

--Sara

 

[Graham T]

Sara

The simplest way would be to create the form, using combo boxes for these fields.

The value visable in the combo would be the actual text, however the value bound and stored in the table would be the actual ID number for the particular field.

Returning the textual values via a query if necessary.

HTH

 

[Cosmos75]

Sara,

Here's the link again! Sorry!

http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Hope this helps!

 

[RichMorrison]

You wrote
<<
RichM: I was planning to use a lookup to the other tables, so that tblPosition showed the actual data instead of an ID number that the user wouldn't be able to decipher. I thought using the names of the ID numbers would confuse, so I was planning on just using the name for the data the user is viewing. Does that make sense? Is it a bad idea?
>>

I think GrahamT has already addressed this. You store the foreign keys in tblPosition but you display the Title, LocalOffice, CostCenter from the linked tables.

RichM

 

[SaraMegan]

Thanks, Cos, that was a helpful article.

And thanks, RichM and Graham. I think I get it now. I really appreciate everyone's help.

--Sara

 

[Cosmos75]

Glad to be of service.

You're very welcome, Sara.

Am glad that I could help (which is a RARE treat for me).

 

[SaraMegan]

Okay, should there also be a separate table only if there are a few options that will never change?

I havea field in the tblApplications table that asks for the application source. There are only three options: In-House, Outside, or From Register. These won't change. There's nothing else to add... should these still be in a separate table anyway, not just in a custom combo box?

I feel like they should, but I'm still learning, so I want to KNOW if they should.

Oh, and what does it mean (in dummy terms) to enforce referential integrity? I know it has to do with relationships, and I even know where to go to do it, but I'm not sure what it is and when to enforce it and all that...

Thanks again, all!

--Sara

 

[Cosmos75]

From Access Help file


"When referential integrity is enforced, you must observe the following rules:

You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.


You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.


You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table."

Not in "dummy terms" but that's what I could find really quick! I always use it, not ever sure if there is a case when I shouldn't use it?! Does anybody know when you wouldn't want to enforce referential integrity?

EDIT2:
When NOT to enforce referential integrity?
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33531

 

[The_Doc_Man]

You asked

Okay, should there also be a separate table only if there are a few options that will never change?


Depends. How often do you need those options in forms and/or reports? If the text length of some option is rather long, you might consider storing a key and then doing a lookup or JOIN on that code between your main and lookup tables.

Also, consider this. Today you have three options. But do you know for a fact you'll never have two? Or four? If you put your common lookups in a separate table, then on the day that you need to add one more option to 500 reports, all you have to do is add one row.

OK, consider this: Right now you are just using this as a lookup. But suppose that down the road you discover some validation rules that depend on this value. You could add fields to the lookup table to help you store the values associated with your validation rules.

Finally, suppose that your business rules change such that each choice has to be sent to a different place or department. What better way to store that destination than in the lookup table?