Why schemas and not code?
dave at dave.org.uk
Fri Jun 30 09:58:07 BST 2006
Quoting Tim Sweetman <ti at lemonia.org>:
> Creating a simple table with "artist" as a varchar is a doddle.
> Turning "artist" into a foreign key on an artists table is simply more
> work, with no immediate return on investment. More work still, if you
> want the "artist" table to automatically populate itself when a new
> artist appears; still more if you want to be able to rename an artist
> (say, from "Alanis Morisete" to "alanis morissette") and have the
> change cascade, or refer to abstract numeric keys instead.
> And before you know it, you've got tables & triggers and maybe views
> and INSTEAD OF INSERT triggers, and INSTEAD OF UPDATE triggers, and
> you're having to name every field explicitly every time, and change
> three things instead of one whenever you change your table. Can you
> explain the purpose of that to a pragmatic technical lead? "It's four
> or five times the work, for no gain".
I'm not sure I follow your argument here.
I have an artist table and a song table. The artist table contains a
record with an id of 1 and the name "Alanis Morisete". I also have a
song table which contains a number of rows containing songs by Alanis,
all of which contain an artist id of 1 which links them to the "Alanis
Morisete" row in artists. When I select details of these songs, I link
to the artist table to get the artist's name.
Now when I need to correct the spelling of "Alanis Morissette" I just
need to update one row in the artist table and it all just works. That
seems far easier than searching for all possible mispellings of the
name and correcting them all individually.
Or am I misunderstanding.
Normalisation is DRY for databases. Each piece of information has
exactly one source in the database. It just makes life easier.
More information about the london.pm