Turn CSV Files Into An Online MySQL Database
Five is a great tool for building an online database. Inside Five, you can simply turn a CSV or Excel file into an online, MySQL database in just a few clicks. Simply follow these three steps:
1️⃣ Create your database model (and its table(s)) inside Five,
2️⃣ Import your CSV and let Five take care of the field mapping and the primary keys,
3️⃣ Build a form to create, read, update or delete data inside your database.
Done!
Generally, Five gives you several powerful features to create and manage your MySQL database. Inside Five, you can:
✅ create and manage a MySQL database from scratch,
✅ define data and display types for each database field from a wide variety of commonly used types, such as strings, floats, Booleans, radio buttons, ratings, etc.
✅ create relationships between tables without writing any code,
✅ import data from a CSV or Excel file into a MySQL database in just a few clicks,
✅ add new fields to existing databases and pre-fill these fields based on fixed values, existing fields, or SQL queries.
All of this happens in an intuitive, point-and-click interface that doesn’t require writing SQL. Five even generates the primary keys for you, so that each row inside a table can be uniquely identified. This ensures data integrity, even for large datasets.
Turning A CSV Into An Online Database With Five
There are multiple ways to turn a CSV into an online database. Usually, before you begin, you also have to select which database to work with (for example: MySQL, PostgreSQL or SQLite). To see how Five helps software developers turn csv files into a web-hosted MySQL database, including an entire front-end that’s running under a custom URL, watch the video here:
When creating an online database, Five handles primary and foreign keys for you. Creating and maintaining these keys can be a daunting task when building a relational database, but Five handles keys entirely automatically and without developers having to do any extra work.
First, instead of having to create a dedicated field for a primary key manually, Five inserts this field automatically, giving it the default name of “TableNameKey”, such as “OrderKey” or “NameKey”. For every new row, a primary key is automatically inserted. Five uses GUIDs as primary keys.
Five then uses these primary keys to create table relationships. Relationships between two tables can be easily identified by checking whether a table contains more than one GUID. Through Five’s standardized naming convention for keys, relationships are easily identifiable:
Last, the Indices tab shows which GUID serves which purpose. In the image below, there is a one-to-many relationship between the Property and the Suburb table: one suburb contains many properties. This is why the Property table contains the SuburbKey as a foreign key.
Ready to give this a try? Sign up on https://five.co for a free download of Five’s development environment! Not sure how to get started? Check out our documentation on help.five.org for helpful tips and a quick start guide.
Five Reasons Why You Should Replace Your CSV With An Online Database
If you’re new to programming, MySQL online databases might seem intimidating at first: their way of storing data is different from your typical Excel or Google Sheets spreadsheet.
However, they are extremely powerful, easy to set up, and will not break, even when your dataset grows in size. They are also extremely popular, powering most of the world’s largest websites. Though not visible to the end-user’s eye, relational databases, such as MySQL, PostgreSQL, or Microsoft SQL Server are a technology that is truly tried and tested and proven. If someone were to wager a bet if any of their personal data is stored in some relational database, the answer would most certainly be yes.
There are plenty of advantages of a MySQL online database over an Excel or CSV file. Just to give a few:
Size: A MySQL database can store much more data than regular spreadsheets (Excel or Google Sheets). As of the time of writing, the maximum number of cells in a Google Sheet is 10 million. And once you’re nearing 10 million, expect performance to deteriorate quickly. A relational database stores data much more efficiently and can deal with much larger datasets than a spreadsheet can.
Speed: Because of the way SQL databases are set up, data retrieval is much faster in a relational database than in a spreadsheet. Data retrieval and storage are extremely efficient through the use of (primary and foreign) keys that serve as unique identifiers for each row and that help connect related tables.
Data Integrity: Inside Five, you can easily assign Create / Read / Update / Delete (CRUD) permissions for different user roles and per database table. Let’s say, management is only allowed to read data, but not create, update or delete it — this can be easily set up inside Five. This also helps ensure data integrity. Neither Excel nor Google Sheets allows for such granular management of access rights and CRUD permissions.
Data Types: online databases use data types that define the permissible input for each cell. Five offers a wide range of standard data types, such as strings, floats, integers, etc. In addition, Five can help validate end-user input through custom display types. These can be regular expressions that help check whether an input is a valid email address. for example. Through the use of data and display types, it’s easy to avoid a “garbage in” type of situation where end-user input does not correspond to the expected input.
Data Sciences: nowadays, most data is used for further analysis, be it through dashboards or more advanced data science concepts. Through the use of an online database, you have the right foundation to support your application’s growth from day one.
Last, Excel is simply not a safe way to store, retrieve or share data. For a full overview of all of the things that can go wrong, check out the horror stories website of the European Spreadsheet Risk Interest Group (yes, this is a real website)!
Originally published at https://five.co on December 11, 2022.
Top comments (0)