Table Lookup, One-to-Many Relationships, Primary Key

[JohnHarkins]

I've been over this a thousand times in my own mind, but I can't quite get it straight. It's got to be simple, doesn't it?

I want to validate project phases for each item in a master record (tblMaster); for example, project phases can assume several values such as "startup", "design", "test", "implementation", and "retired".

Seems simple enough. Just have those five phases as acceptable values through a validation rule. Good enough.

If I expand from five to six and I have the data in a back-end database, then I have to change the back-end table definition to include the new phase.

But, if I want to add greater flexibility to the application, I want to create a small table in a one-to-many relationship with the master records (one on the tblLookup side and many on the tblMaster side, right?).

However, the database examples I see generated by the wizards create the lookup table with an autonumber unique ID as the primary key. So far so good, but that technique allows the user to add two phases with the same name, I believe. I want to avoid duplicates, don't I? So, why not make the phase name the primary key instead of the autonumber? Doesn't that technique (phase name as primary key) afford me the chance to change the phase name in one place; for example, if instead of the word "design" I now want it to be "imagine" I'd just change the primary key of "design" to "imagine" in tblLookup by using cascading updates.

I probably don't want to have cascading deletes, because that would wipe out master records if I "dropped" a particular project phase, wouldn't it?

Please help clarify my thinking and offer some pro's and con's if you think that would help.

Many thanks,

John Harkins

 

[cmmrfrds]

There is really no advantage to using a description as the primary key in the lookup table, but there are some disadvantages. The Auto Number primary key is probably an integer which takes up 2 bytes - all you need for most small tables. You could go to a Long which takes 4 bytes and gives you up to 2 million plus values. Now these foreign keys are quite small compared to a string of up to 10-15 bytes. The change of "design" to "imagine" only requires a change to the lookup table no need for any cascading. If you want unique value in the description then set to not allow duplicates in the field.

 

[fneily]

Just have a table with your phase names and have that field as the primary key. Don't use the wizards. Build your table in design view. Type in your field name (phase) hit tab to see the default value "Text", at the bottom you may want to adjust the default 50 characters to 20, then click the button at the top with the Key on it to make this field a primary key.

Yeah, then if you set this up in a one-many relationship, turn on the referential integrity. This way every project must have a phase associated with it. R.I. also protects people changing a phase name that has associated records (parent to child) which could leave "orphans".

Only turn of the cascades when you need them and then turn them immediately off or you'll defeat the purpose of R.I.

Neil

 

[JohnHarkins]

Hi, Neil!

I see you believe it's easier and better to create the lookup table as I described than to use an autonumber. From your experience, do you see much of a performance hit to using a primary key that's not an integer?

Am I right about the one-to-many relationship? Does the infinity-symbol indicate that the many side has cascaded updates/deletes or simply that RI has been specified?

I assume your last sentence should read "Only turn on the cascades when you need them and then turn them immediately off or you'll defeat the purpose of R.I." If so, I'm not sure I understand why you would turn it on momentarily. So, do I turn it on/off in order to make a change that does or doesn't affect the value of the looked-up data.

Regards,


John Harkins

 

[fneily]

Speed wise, computers are so fast nowadays and your table is so small, for any human senses, there is no performance lost. Primary keys are automatically "indexed" ( a mathematical system for finding records).

Yes, when you set R.I., visually you see the 1 - omega (infinity) sign. This just shows R.I. is set. It has nothing to do if the cascades are checked.

If you turn on "cascade updates" for example and left it on, then anyone can go into your phase table (the one or parent side), make a change which would then propagate through the many side. If this was done accidentally or on purpose, your tables are now corrupted. Or, worse, someone deletes a phase. Then ALL the related records with that phase would be deleted. You only puts checks in the cascades when you're going to change something yourself. Then after the change, take the checks out. You should be the only one touching your tables.

Remember, you can add to the one side and have no related records. You CAN'T make a record in the many side without a connection to the one side. Think in terms of Parent and Child. No orphans allowed. So you can have parents with no kids, but you can't have kids without parents.

Neil

 

[danvlas]

A table with one text field where you enter the values should be OK in this case.

However, remember that a text field allows you (and anyone) to enter special characters, such as space, comma, dot and so on. Therefore, you may end up with more values describing the same thing:
'startup' vs 'start up'

That's why text fields are not used so often as primary keys. And one of the reasons to avoid user meaningful information as primary key...

But it doesn't violate the theory...it just messes up your data...so it's up to you.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html