CPSC340A
Database Systems

Design

• Begin much like in creating an object oriented program
• List the things that will be represented
• In an object oriented program these are the classes and their attributes
• In a relational database these are the tables and their fields, or columns
• Don’t forget to add types to each field
• Don’t be afraid to include other tables as a type (we’ll fix this later)
• Don’t be afraid to include lists as fields (we’ll also fix this later)
• Don’t be afraid to include enums (we’ll also fix this later)

Relationships

• Add primary keys to all the tables
• Replace fields that are the type of another table with foreign key columns

Multi-part Fields

• Once you have everything listed, refine it to be relational
• Consider breaking fields into multiple fields
• For example, name could be first name and last name
• Having them separate makes it easier to perform some operations like sorting by last name

Redundant Fields

• Try to Remove redundancy
• If a value exists in multiple fields or multiple tables, pick just one place for it to be
• Data in multiple locations can lead to trouble if they become out of sync
• References can be used to make sure the information is where it is needed

Multi-valued Fields

• Remove lists by creating a new table that stores all the lists
• Use references (primary and foreign keys) to link the list
• If two tables have a many to many relationship, create a linking table
• Also create a separate table to enum values