Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

13.3. Translating Vague Specifications

This problem was posted in mid-2007 on a newsgroup. The poster wanted to set up a system for controlling access to articles in some context. Here are his specifications. (Note: this example was used in Chapter 11, but we will be discussing different aspects of it here and in more depth.)

• The Users table stores information about users.

• The Articles table stores information about articles.

• Each Article belongs to a User.

• Each Article can be marked private. When an article is marked private, only the article owner and his or her friends can see them.

• The ArticleFavorites table stores information about user’s book-marked articles.

• The UserFriends tables keeps track of which users are friends.

The schema for the tables ...