A Relational Data Table stores data in rows and columns. For example, every Contact in the system is stored in the Contact table with Email address, First Name, Last Name, and address information. In a Customer Key enabled Realm, 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 Contact or another Regular Table, identified by a Foreign Key (FK).
The Regular Table below stores the information for Contact Pets. One Contact 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 contacts. 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 Contact.
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 Contact'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 Design for more information on relationships between the table types.