MySQL last table alteration time

Brandon Stout bms at mscis.org
Wed Mar 28 14:47:11 MDT 2007


Dave Smith wrote:

> Normalization rules can be fall down in the real-world. I double 
> there's any normal form that dictates putting table names as strings 
> in another table. :)

OK, I must be a geek, because I'm enjoying this refresher on 
normalization.  You're right - no normal form dictates putting table 
names in a table.  However, what I suggested actually does violate 2nd 
normal form.  If you wante the last time a record was updated, what I 
suggested works.  If you want the last updated record, or the last time 
an entire table got updated, it no longer satisfies 2nd normal form.  
Using this Wikipedia example:

  http://en.wikipedia.org/wiki/Database_normalization#Second_normal_form

It's like putting an employee birthdate in the department table.  If the 
employee can work in multiple departments, then you have to list both 
the employee and the birthdate twice for each department, creating a 
margin of error.  What if you add a new department and put the wrong 
birthdate?  Have you ever had an employee with 2 birthdates?

Using a timestamp value for every record to get the table's last updated 
time is even worse... _every_ record has a timestamp.  When normalizing 
data, one must also consider what they are using it for.  If you want 
the last time a record was updated, use a timestamp field in the same 
table.  If you want the last time a table was updated, use another 
table.  If you want the last updated record, use another table, and add 
a RecordID field.

Brandon Stout
http://mscis.org

>
> I remember studying normal forms in college and thinking, "What? This 
> is just common sense. Why do we need a text book for this?" I think I 
> applied the same reasoning to this problem as well.
>
> --Dave
>
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */




More information about the PLUG mailing list