Database Principles
The database structure of reason is based upon the principle of flexibility. There are a few principles behind which reason’s database is designed. First, there is the idea of a type. The idea behind a type is that there will be many different kinds of data that we may wish to put into the database. Each type should be able to have its own separate fields.
Even though each type is different, in some sense, they are all similar. We wish to create a database structure which allows us to have flexibility in what types of data we use, while centralizing all the data.
The third principle idea behind the database structure of reason is the concept of associations. Although each entity in the database contains its own information, we may wish to relate it to other entities in the database. For instance, if we have an athletics player as one type, we may wish to relate it to an image (hopefully an image of that player).
With these ideas in mind, I will describe the setup that we have. The first and most important table is the entity table. Every item that is an entity in reason has an entry in the entity table. This table contains information that is common to all entities in the database such as id, name, last_modified, type, etc. There are other tables that may also contain data for each entity which I will describe in more detail later, but for now, we’ll just worry about the entity table. Also, a type is a specific kind of entity in reason, so all types also have an entry in the entity table. The very first entity is the type type. It is the only entity in reason whose id is the same as its type.
The next to important tables in reason are the relationship and allowable_relationship tables. The allowable relationship defines the different kinds of relationships in the database. The most important fields are relationship_a and relationship_b which are types. For instance, the player_to_image allowable relationship would have relationship_a set to the type id of player, and relationship_b set to the type id of image. The allowable relationship table defines what sort of relationships are allowed, but the relationship table is the table that holds all the individual relationships. This table have 4 fields. First is the id field, which isn’t really used but is there to basically make sure every relationship is unique. Then we have the type. The type should be the id of the relationship defined in the allowable_relationship table. Finally we have entity_a and entity_b, which are the two entities being related. Now if a player and an image are related, the id of that player should be in entity_a, the id of the image in entity_b and the id of the “player_to_image” allowable_relationship should be in type. Similarly, if ther is an entry in the relationship table of the “player_to_image” type, if you looked up the id of entity_a in reason, it should be of the player type and entity_b should be of the image type.
Now that we’ve been over the basics of relationships, we are ready to talk about types some more. I mentioned about that there are other tables in the database that can more specific information about the entities than the entity table. Each type can be associated with multiple content tables. For instance, the athletics_player type is associated with the athletics_player table. Content tables are also entities in reason, so we can grab them out of the database as well. So in order to get the information we must first do a query to see which tables are associated with that type. Now, that we know all the tables that are associated with the given type, we can grab all the information about that entity. The query for exampe for the athletics player would look something like “SELECT entity.*, athletics_player.* FROM entity, athletics_player WHERE entity.id = athletics_player.id…”. Of course we have made many tools to make writing these queries easier.
A quick recap. The reason database is composed of entities of different types. Each type is associated with different entity tables. An entity of a given type will have entries in the entity table as well as all the other tables that that type is associated with. The ids in each of these tables will be the same, so we can grab all the info about one specific entity. In addition to all the information about specific entities, we can have relationships between different entities. The different relationships that we can have are defined in the allowable relationship table and all the individual relationships are stored in the relationship table.