A Relational Data Table stores data in rows and columns. For example, every Subscriber in the system is stored in the Subscriber table with Email address, First Name, Last Name, and address information. In a Customer Key enabled Account, the customer_key identifier is also stored.
In Relational Tables for this system, each row of each table must include a column containing a Unique identifier, or Primary Key (PK). Relational Tables support three types of data storage:
A Regular Table stores data in columns and rows sharing the same attributes. It is data uniquely relevant to and/or historical for each Subscriber or another Regular Table, identified by a Foreign Key (FK).
The Regular Table below stores the information for Subscriber Pets. One Subscriber may have multiple Pets, but each Pet has unique information, such as Name.
A Flat Table stores data that is not unique but still relevant to data in another table, where the same data may be used for many subscribers. Flat Tables eliminate the potential for redundant data. For example, a list of songs that may appear on multiple play lists, vendor information, or product details.
A Flat Table does not require a Foreign Key, but does require a Unique identifier (Primary Key) for each row. In order to use the data in a Flat Table, it must be associated to a Regular Table via a Join Table.
The Flat Table below stores the contact information for Veterinarians. One Veterinarian may service several Pets, and each Veterinarian's information is the same regardless of Pet or Subscriber.
A Join Table stores data that defines the relationship between a Regular Table and one to three Flat Tables. The join table must have it's own Primary key, a Foreign Key to a Regular Table, and then keys to join any Flat Tables.
The Join Table below stores the the Pet Identifier (petID) to the Subscriber's Pets, and links each Pet to the Pet's current Veterinarian in the Flat Table. One Veterinarian may service several Pets, so while each Pet appears once in the Join Table, the Veterinarian may appear several times.
See the Data Schema Examples for more information on relationships between the table types.