When starting out with building an API for your custom tables, one of the very first things you need to do is determine what data you need to store in your tables, and what format that data will be stored in. This is a fundamental aspect of creating your API as it is one of the defining factors for how your tables are structured.

Determining the structure of your data can be difficult, but taking the time to lay out the kind of data you are storing and how you are going to store it can significantly reduce headaches, and allow for easier enhancements later on.

Along with determining what each table looks like, you will also need to decide how the relationships with data between tables is structured.

Since everyone’s table structures are different, I cannot provide a concrete set of steps to help you create your database schema, but there are numerous things to keep in mind. Keep these in mind and creating your structure will be a much easier process.

Determine your key object(s)

To begin structuring your tables, you need to identify what the primary data sets are that you are going to store. Typically each data set is a “kind” of data. In eCommerce, for example, you might have the following objects:

• orders
• customers
• products

Each of those data sets would have its own table that stores the relevant information for the object type.

Once you have determined the primary data sets, you can move on to determining exactly what each of those data sets looks like.

Know the data you need to store

This may seem obvious but if you have ever created a table, you have probably realized that anticipating the exact data you need is more difficult in practice than in theory.

To know what the data you need to store looks like, you first need to know what sort of features are being built on top of and rely on the data in the database. This requires that you have a (mostly) complete roadmap of features you intend to build. You don’t need to know every feature that will be built over the next five years, but it is important to have a pretty solid idea of what you will build for the first version that is released. If possible, try and anticipate future features as well so you can avoid having to make changes to your schema in the future (more on this later).

While not always a one-to-one relationship, there is a very strong relationship between the features your plugin includes and the data structure that the features are built on top of. Features can be built around a myriad of data structures, but the more closely the structure is built around the feature itself, the easier the feature will be to build.

Once you know the data you need to store, you can start laying out the basic columns for the table.

Let’s continue with the eCommerce examples of part 1 and assume we are storing order data. For an order record, we will have something like the following fields:

• Order ID
• Total
• Taxes
• Discounts
• Billing Address Line 1
• Billing Address Line 2
• Billing City
• Billing State / Province
• Billing Country
• Billing Postal Code
• Shipping Address Line 1
• Shipping Address Line 2
• Shipping City
• Shipping State / Province
• Shipping Country
• Shipping Postal Code
• Customer ID (customers stored in separate table)
• Status
• Purchase Method
• IP Address
• Date

For this information, our table might look something like this:

• order_id
• total
• taxes
• discounts
• billing_address_1
• billing_address_2
• billing_city
• billing_state
• billing_country
• billing_postal
• shipping_address_1
• shipping_address_2
• shipping_city
• shipping_state
• shipping_country
• shipping_postal
• customer_id
• status
• payment_method
• ip
• date

Laying out the column names in a simple list is a nice way to get a better overview of the information you are storing and where you are storing it.

Know the format of the data you need to store

Data formats are important when structuring your table. They can be adjusted later on so it’s not critical that you get them right on the first try, but you should take ample time to determine the formats you need, or at least the formats you think you need.

When determining formats, it’s good to first look at the data to be stored. Let’s go through each of the fields we set above and determine the type of data being stored:

• Order ID – int
• Total – float 
• Taxes – float
• Discounts – float
• Billing Address Line 1 – string
• Billing Address Line 2 – string
• Billing City – string
• Billing State / Province – string
• Billing Country – string
• Billing Postal Code – string
• Shipping Address Line 1 – string
• Shipping Address Line 2 – string
• Shipping City – string
• Shipping State / Province – string
• Shipping Country – string
• Shipping Postal Code – string
• Customer ID (customers stored in separate table) – int
• Status – string or int
• Purchase Method – string
• IP Address – string
• Date – date time string

Determine the column formats

Once we know the basic type of data that we need to store, we can start on determining the type of each column in the database. MySQL has quite a few supported data types for columns available to choose from.

The column format should be a direct representation of the kind of data that is being stored in the column.

For example, for the total column, we might set a data type of float when creating the column since we are storing real numbers that will often include decimals (called floats or doubles).

The date column will likely get a format of datetime as it is storing a complete date and time string for the order.

Other columns have multiple options. The status column, for example, could be a number of formats:

• char
• varchar
• text
• int (int, smallint, bigint)

The exact format depends on how you choose to store the status. Some developers like to use numbers to represent certain status codes, such as 1 = pending, 2 = complete, 3 = refunded. You will have to decide for yourself what to use here as there is no one right or wrong way to do it. Do try and choose a column type that most closely matches your data.

It is also important to consider the size of the data you are storing. For example, if you are storing integers in a column, how large of integers do you need to support? The tinyint type, as an example, supports from -128 to 127, whereas the bigint type supports -9223372036854775808 all the way to 9223372036854775807. If your numbers will never go beyond 10, it would be best to use a tinyint.

After you have laid out the basic blueprint for your database table, it is time to create the table itself, but that’s for the next part in this series.