MySQL last table alteration time
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:
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.
> 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.
> 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