Building an Entity-Attribute-Value System - Part II - Creating the Database

Posted on January 10, 2014 in database, sql, php, codeignitor, eav

##Introduction In the previous post, we talked a bit about what EAV is and how it functions on a database level. In this post we're going to take the time to build our necessary database tables and understand how each one functions in the grand scheme of our application.

Word of caution: These articles should be taken with a grain of salt. The series should serve as a demonstration and practical learning example and is not recommended to be used in any production environment.

##Getting Started Start by creating the following database tables (SQL masters be warned, there is nothing optimal about the following tables, we're simply going to look at this from a high level.)

CREATE TABLE `attributes` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `type_id` INT(11) NOT NULL,
    `attribute_label` VARCHAR(255) NOT NULL,
    `attribute_code` VARCHAR(255) NOT NULL,
    `attribute_comment` TEXT NULL DEFAULT NULL,
    `attribute_filterable` TINYINT(1) NOT NULL DEFAULT 0,
    `input_type` VARCHAR(255) NOT NULL,
    `input_source` TEXT NULL DEFAULT NULL,
    `input_required` TINYINT(1) NOT NULL DEFAULT 0,
    `sort_order` INT(4) NOT NULL DEFAULT 0,
    `input_hidden` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE `attributes_types` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `attribute_model` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE `attribute_values_float` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `attribute_id` INT(11) NOT NULL,
    `model_id` INT(11) NOT NULL,
    `value` FLOAT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE `attribute_values_int` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `attribute_id` INT(11) NOT NULL,
    `model_id` INT(11) NOT NULL,
    `value` INT(11) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE `attribute_values_text` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `attribute_id` INT(11) NOT NULL,
    `model_id` INT(11) NOT NULL,
    `value` TEXT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE `attribute_values_tinyint` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `attribute_id` INT(11) NOT NULL,
    `model_id` INT(11) NOT NULL,
    `value` TINYINT(1) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
);

CREATE TABLE `attribute_values_varchar` (
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `attribute_id` INT(11) NOT NULL,
    `model_id` INT(11) NOT NULL,
    `value` VARCHAR(512) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT NULL
)

Now that we've created our tables, let's take a quick look at what each table is responsible for.

  • attribute_types - This table's job is relatively simple. All we're doing is storing the model types our application utilizes and giving them a unique index id (id). If, for example, we want to tell our application that we have the EAV model "User", we could write a SQL query like INSERT INTO `attribute_types` (attribute_model) VALUES ('user');. This will insert a new row and return us the unique id field in order to store attributes for this model.
  • attributes - Not surprisingly this is probably the most important table of the whole system. The job of this table is to store all the attributes of all the models in our application. At the very minimum we give each attribute a code (a unique identifier such as "username"), a type (or model, e.g. "user" or "post"), an input type (text, varchar, int, tinyint), and a source if applicable (JSON encoded array for selects and multiselects). In this example I also make plans to allow attributes to be filterable, hidden, required, and sortable. If we look at an example, to give the model "User" an attribute "username" you could write a SQL query such as INSERT INTO `attributes` (`type_id`, `attribute_label`, `attribute_code`, `input_type`) VALUES ('1', 'Username', 'username', 'varchar'); where '1' is the "User" model we created in the attribute_types table above. Fields such as labels and comments are nice to have if you're going to be managing attributes in some sort of backend.
  • attribute_values_* - These tables store the values for the given attribute and model. To reiterate what these tables ultimately do, if the column attributes.input_type is set to 'varchar', we'll be storing that attribute in the table attribute_values_varchar.

##Taking a step back Now why did we decide to plan our tables this way? Is there another way we could have approached this? Absolutely.

Taking an example from Magento, attributes can be stored across multiple tables. It's not uncommon to create a new attribute_types table based on a particular model, especially one that possibly could get a lot of bandwidth. In Magento, a customer model and it's attributes are stored in the tables customer_entity and customer_entity_*, respectively. This means that instead of having a table full of different model names, we can have a new table created for each model. If we applied the same methodology to our "User" model, we would end up with tables such as user_entity, user_entity_text, user_entity_varchar, and so on. Of course this also brings big changes in the way we've formatted our tables, but I'm not about to dive into the differences. Just know that there are a dozen ways to implement this system, the concept is all the same.

##Conclusion While seemingly barebones, this should give you enough to get you started creating an EAV system database. In the next post, we'll discuss how to write sensible code to retrieve, store, and delete models and their associative attributes.

Stay tuned!

Cheers

Thomas Lackemann :)

About

Tom is the founder of Astral TableTop. He's a homebrewer, hiker, and has an about page. Follow @tlackemann on Twitter for more discussions like this.

Comments