Winners' Circles (Designing the Database)
Getting down to the nitty-gritty. If you need a quick introduction to the basic principles of database design, this is the place to look.
The database in which the credits data used in the social network analysis are stored was developed in Filemaker Pro. In Filemaker Pro the data are stored in tables and displayed and manipulated in layouts in which data from multiple, related tables can be combined. This database has three primary tables: Ad, Creator, and Role, which currently contain information from the TCC annuals from 1981, 1986, 1991, 1996, 2001, 2006, and 2007. In total the database contains records on 4536 ads, 8579 creators, and 34907 roles linking creators with ads.
But why these three tables, and how do they function? The following discussion is a bit technical but deserves close attention from readers who wish to know how the analysis reported here was done as well as the conclusions drawn from it. It may also be useful for those contemplating similar research to know that working out these details took me several months of intermittent hacking before I got them clear.
In this type of database, the data are stored in fields, with the fields referring to a single case combined in a single record. To readers familiar with spreadsheets, it suffices to say that if a table is represented in spreadsheet format the fields are the the columns; the cases are the rows. But why, in this database are three separate tables required?
It is a basic principle of database design that there are three and only three fundamental relationships between the data contained in separate fields: one-to-one, one-to-many, or many-to-many. If the relationship is one-to-one, the two fields can be combined in a single table. If the relationship is one-to-many, two tables are required, with a unique identifier that appears in both tables used to link one to the other. If the relationship is many-to-many, three tables are required. Two of the tables have a one-to-many relationship to the third, called the join table, that provides a bridge between them. To see how this works in practice, we now take a closer look at the data contained in the Ad, Creator, and Record tables.
Each ad that appears in the TCC annual is a unique case, with several attributes, each of which stands in a one-to-one relationship with the ad in question. Each ad has only one sponsor, one lead agency, one industry category, one year and one medium. These basic items all appear with the ad in its record in the Ad table. Calculation fields (a special kind of field that displays the result of a calculation) are used to code the raw data in the basic fields; so that, for example, ad agencies can be coded as Dentsu=1, Hakuhodo=2, ADK=3, and Other=4.
Each creator has a personal name and surname. But here we have a problem. Different individuals may have the same names. The solution is to assign each creator a unique serial number. A calculation field is used to combine the first three letters of the surname with the serial number to create the name used in the social network analysis. Thus, for example, a copywriter named Maki Jun (where “Maki” is the surname and “Jun” the personal name), whose unique serial number is 65, becomes Mak65.
While calculation fields are used for coding information in both the Ad and Creator tables, the basic one-to-one relationship between the fields in each table remains undisturbed. In the Role table, which functions as a join table and provides links between ads and creators, things are a bit more complicated.
The problem here is that creators and ads have many-to-many relationships. The same creator may be credited with several different roles in the production of an ad: the same individual may, for example, have played three roles, creative director, copywriter, and planner. In addition, two or more individuals may be credited with the same role: thus, for example, multiple copywriters may have worked on the same ad. Records in the Role table each contain three vital pieces of information: a unique identifier for an ad; a unique identifier for a creator; and a category label for the type of role in question: copywriter, art director or film director, for example.The unique identifiers are automatically generated serial numbers assigned when new ads or creators are added to the database, and the pair of unique identifiers specify precisely the particular role in question. It is the roles specified in this way, as unique combinations of ad and creator that become the links analyzed by the social network analysis software.
In sum, the ads and creators are nodes in the networks analyzed. The roles are the links which connect them.
Previous entry: Winners' Circles (Assembling the Data)