# Database: Design: Patterns

Several issues with this unfinished list:

- Conceptual issues. Many problems, eg hierarchical structures are also simply parent attributes. Or M:N relationships with bridging table can simply be turned around so that the reclationship is primary, and the two sides are simply attributes (Restaurant-Comment-User) could regard Comment as central and Restaurant/User as attributes. Similarly with Enrollment. Figure-ground issues.
- Maybe there should be only a couple of categories, 1:1, 1:M, ... and each of the relevant patters categorized under these broad implementa/ patterns.

1. Attribute, single implemented as a field.
2. Attribute, single in another table. If an attribute occurs in another table, link to it. Implemented as 1:1 or 1:0/1 (entity:attribute). Foreign key in entity pointing to entity.
   Example: Manager of unit.
3. Attribute, multiple, implemented as 1:M (entity:attributes) relationship. Foreign key in attribute.
   Example: ...
4. Hierarchical structure (single hierarchy, different entities) implemented as 1:M (parent:child). Foreign key in child.
   Example: State-county-district-school
   Example: Many organizations.
5. Hierarchical structure (single hierarchy, same entity) implemented as 1:M (parent:child). Foreign key in child. Ie, each element points to parent. Here the entities point back to same table.
   Example: Assembly-Part
   Example: Tree structure.
6. Superset - Subset is a 1:0/1 (super:sub) relationship to solve the problem that some members of a table have additional char\\acteristics. Primary keys are defined in the superset table (eg Employee). The subset table (eg, Instructor) contains additional information about those employees who are instructors. The primary key for the subset table is the same as the primary key in the superset table.
   Example: Instructor-Employee.
7. Abstract - Concrete is a 1:M relationship in which the concrete item contains the key of the abstract description which applies to it. Like OOP Class-Instance, Description-Object, .... Examples: Course-Section, DVD-Disk, Title-Book.
8. Split table for security / performance / distribution, connected by 1:1 relationship (common primary keys).
9. Bridging tables for M:N relationships
   - Unique (eg, Enrollment) relationships can use compound key of two tables. Only one relationship.
   - Multiple (eg, Restaurant-User comments) should have addtional key element (maybe?) to make records unique. Eg, the number of the relationship (nth) or arbitrary key.
10. Itemization order requires adding a field that allows putting items in order (eg, simple count, timestamp)
    Example: Invoice-LineItems. Line items in an invoice should be numbered,
    Example: Trip-City. cities visited in a trip.
11. Inventory Quantity and Serial Number Relationships. Given Location/Owner and a ItemDescription, there might be two bridging tables for the two possible relationships.
    - For items with a serial number, the serial number should be in the bridge table.
    - For items without unique identification, the quantity could be there.

## Snapshop denormalization

- The actual price should be in the invoice line item instead of getting it from the product. This is so that the price the customer actually paid is recorded.

## Interpreting foreign keys

Foreign keys might be read as "here is one of my attributes" or "I belong to x".