in reply to Indexing Sublists

In an othorgonal world, you would never encode information like that into the user table. Instead, you should create a third table that looks like: 
ID	MonkID	   HobbyID
1	89	    1
2       89	    2
3       89          3
...
where ID is an auto-incremented key, MonkID is the ID of the Monk in question and HobbyID is a hobby. It may not show in this case, but I have found doing it this way keeps a lot of flexibility in your code and saves some serious rewrite time when your client decides they want the database to work in a radically different way then you had intended.

Mik
Mik Firestone ( perlus bigotus maximus )

Replies are listed 'Best First'.
RE: Re: Indexing Sublists
by httptech (Chaplain) on May 15, 2000 at 17:44 UTC
    Very good, I like this. What is the reason for the auto-incrementing primary key? Couldn't you just make an unique index of the MonkID and the HobbyID?
[reply]
      As you suggested, you index both MonkID and HobbyID because you're planning to search for all monks with a particular hobby and all the hobbies of a specific monk.

      But that doesn't mean you won't want a single primary key rather than a composite one. The db theory book I've got here says "When possible we should try to avoid composite primary keys for entities..." although it isn't kind enough to offer a compelling reason, or really any reason at all. If you ever need to refer any of the tuples in the third table within another table, you'll probably want a single key.

      But I seem to recall that you can use the internal tuple identification (in Postgres, at least, where I believe it is the oid) rather than a sequence for such cases. I can't access the postgres site currently, so I'm unable to verify that, and I don't know if that's possible or recommended with other databases.

[reply]
      I get nervous doing that - most of my database design is from reading one book many, many moons ago and then a lot of work.

      I think auto-increment keys like that are cleaner. By using a database, you have kind of already said you are not *that* concerned with size. An extra INT between friends shouldn't matter. And composite keys, especially when referring to foreign keys, just sound very dangerous to me.

      But I have no theory to back any of this up - it is your database to design as you wish.
      Mik

[reply]

In Section Seekers of Perl Wisdom