Every database project is likely to have different needs in terms of table designs. There are often common approaches, however, to how types of data is organized. This includes the need for strategies such as self-referencing and interim tables. Parent-child tables are another common design.
A self-referencing table is which contains a foreign key to itself. An employees is a common example, where the table might contain a managerid so that a manager can be defined for employees.
Notice in this case that the entity relationship on the managerid side is zero-or-one; in other words, not all employees have a manager, but if they do, they are only assigned to a single manager.
The T-SQL code for configuring the foreign key and referential integrity for a self-referencing table is the same as for any other table. The differences in the FK constraint are that the same table is defined as the target.
The JOIN statement is also similar to a standard JOIN, although the same table is included twice, each instance assigned a different table alias. Notice, too, that column aliases are used to name the column headings of the FK table, so it is clearer where each column is pulled from.
An interim table is one that serves as a connector between two other tables. This is most often the result of a many-to-many relationship. A common example is something like courses and students, where a student may be enrolled in multiple courses.
It would not be possible to associate a student with multiple courses if there were only two entities, such as course and student. The interim entity - named coursestudent in this case - is required to permit a manu-to-many relationship. (The same type of interim entity would be required to allow multiple teachers to teach multiple courses.)
When considering the database design, it is common practice to name an interim table to reflect the other tables that are linked, such as CourseStudents. Although that might work in this case, a name such as Enrollment better captures both the intent and the usage for such an interim table. Also, where interim tables often only include the FK columns for the related tables, this case is a good example where other columns might be useful (dateenrolled, howenrolled, etc.).
Essentially a parent-child set of tables is nothing more than two related tables. A common example might be Orders and Order Items. In this case, when a customer places an order, there is a set of information associated with the "order" itself (store, date ordered, customer, payment method, order status, etc.), and then each item on the order is tracked using a separate table. A typical receipt illustrates this concept. Orders really exists only as part of the combined orders-orderitems set.
The difference between a parent-child relationship and other relationships is that the child records only exist as a subset of the parent record. Consider an Order record that also contains an FK for StoreID, for example. The Stores table exists for many reasons. Although the Orders table is linked to it, the Stores is not a "parent" table. The Stores would be needed, even if no orders existed.
OrderItems is a child table because it only exists as part of the orders-orderitems set of tables. There would never be an OrderItem record that exists without a corresponding Order.
Issues for Parent-Child Tables
Depending on the business rules of the organization, it is possible that the relationship should be defined such that there should not be an Order record without at least one associated OrderItem record.
The Order-OrderItems parent-child table is a good example to point out that designers should always be careful about including columns that maintain unneeded calculations. It is not uncommon to find examples where an Orders table contains columns for NumberOfItems or TotalPrice. This is almost always a normalization error, in that the values of those columns can be easily out-of-sync with the actual data.
If an OrderItem record can be inserted, updated or deleted without the Orders table also updated, the data would be corrupt. Keeping the data in sync would rely on coding practices that ensure the data is always correct.
If it is desired to maintain a calculated column such as TotalPrice, one strategy is to force the table to be maintained by a trigger, so that the database automatically manages the data, instead of relying on middleware code, for example. This does not eliminate the possibility of errors, since triggers may also fail - but it may be a practical way to automate data updates in consistent manner.