Build The Database Structure
Once the HTML and design have been completed, the next step is to build out the data structures (the database). I like building out the HTML and design first because it shows how the data will be used and often makes it easy to figure out how the data should be structured.
There are many different databases to choose from. At some point I would like to use SQLite as the underlying database but for now, I typically use MySQL. I've been working with MySQL for years and I'm very comfortable using it (I'm not in any way an expert, but I know enough to get work done).
There are many different ways to structure a database but I typically think about how the data is going to be used and then figure out what would be the most obvious way to group that data.
A database table is basically a group of data. The web framework I'm building comes with three tables already setup:
- Users
- Password Resets
- Usernames
Generally, when I'm create a database table, I will always include an id column, a created_at column, and an updated_at column. If the column ends in _at, then it is a datetime field.
If a columns ends in _int2 that means the decimal place has been shifted two places to the right so that the data can be saved as an integer. When using that data, the value will need to be divided by 100 to shift it back to the original value. It is simpler to run math operations on integers than to deal with floating point math. I haven't seen anyone else use the _int2 suffix. It is just something I've found helpful. If the data I was using needed three decimal places, I would save it with a suffix of _int3. That last number indicates how many places the number has been shifted.
You generally want to add indexes to a database to speed up the responses, especially when there is a lot of data. There are generally pretty obvious places for adding indexes (like on the relationship ids), however when I'm starting out with a new project, I usually prefer not to add any indexes and only add them as the need arises (when the responses for certain queries start to slow down).
Here is a breakdown of the initial tables built by the system:
Users
- id
- name
- password (hashed)
- data (for any extra data that needs to be saved)
- encrypted (indicates whether or not encryption is being used when saving data)
- created_at
- updated_at
Password Resets
- id
- user_id
- token
- created_ip (the IP that requested the password reset)
- used_ip (the IP that completed the password reset)
- used (whether or not the password reset has been completed)
- expires_at
- created_at
- updated_at
Usernames
- id
- user_id
- name
- primary
- created_at
- updated_at
The tables listed above are the starting point. After examining the HTML pages and the data I'm going to need, I've come up with the following database tables that I need to create:
- Settings
- Categories
- Shared Feeds
- Shared Items
- Feeds
- Items
- Tags
- Colors
- Items Tags
- Auto Ratings
- Default Tags
- Default Colors
Here is a breakdown of how each of those tables are going to be structured in the database:
Settings
- id
- user_id
- name
- key
- value
- editable
- created_at
- updated_at
Categories
- id
- user_id
- parent_id
- name
- show_tags
- show_ratings
- show_colors
- save_ratings
- sort_order
- created_at
- updated_at
Shared Feeds
- id
- real_url
- last_updated_at
- created_at
- updated_at
Shared Items
- id
- shared_feed_id
- title
- url
- description
- type
- published_at
- created_at
- updated_at
Feeds
- id
- user_id
- category_id
- shared_feed_id
- original_url
- real_url
- title
- last_updated_at
- created_at
- updated_at
Items
- id
- user_id
- feed_id
- shared_item_id
- auto_rating_int2
- rating
- archived
- status
- published_at
- created_at
- updated_at
Tags
- id
- user_id
- category_id
- name
- created_at
- updated_at
Colors
- id
- user_id
- category_id
- score
- color
- created_at
- updated_at
Items Tags
- id
- item_id
- tag_id
- created_at
- updated_at
Auto Ratings
- id
- category_id
- word
- use_count
- sum_score
- avg_score_int2
- locked_score_int2
- locked
- created_at
- updated_at
Default Tags
- id
- user_id
- name
- created_at
- updated_at
Default Colors
- id
- user_id
- score
- color
- created_at
- updated_at
If you take a look at the Github repository, you'll see that I've added a migration for each of those tables. A database migration is just a file that tells the database what tables or data needs to be added or set up.
Setting up the database structures is one of the most important parts of building an app so now that we have that in place, the next focus is going to be on connecting everything together.