Database Design: Art Business

Although I'm a web developer now, I started out as an artist and still enjoy drawing and telling stories as a hobby. When I learned about database design, I thought it would be interesting to develop a database that could track all the information I'd need if I were truly running my art practice as a business, and that could track data for future needs as well as current ones.

My concept for the database was in three parts: creative projects, cash flow, and client information. Each project would have portfolio pieces, some of which would be turned into products with various formats. Each piece would have a category, tags, and image files.

Cash flow would be made up of commissions, online orders, products and inventory, incomes, and expenses. Customer information would track their names, including preferred name, as well as address, contact information, Patreon and email subscriptions, and password to enable user accounts.

After defining my concept and goals, I created a list of mission objectives and business rules for the finished project to meet.

I then thought up fields I would need, then grouped them into tables, added relationships between them, and finally built a Crow's Foot Entity Relationship Diagram (ERD) to finalize my database structure.

I then ran each table, field, and relationship through normalization, and made changes to improve data integrity.

After that, I made a list of different users, what tasks they would perform, and what data they would be able to access. I then created a security diagram.

After making some final changes, I built the database using SQL code, including tables, data, users, and views. I also tested the database against my mission objectives to make sure it fulfilled the goals of the project. Finally, I logged in as each user and made sure their access levels were correct.

Once I was sure the database was sound, I generated an ERD from the actual database and compared it with my original design to be sure they matched.

I then exported the database, documented the project thoroughly, and declared the project finished. In creating this database, I learned a lot about the process of database design, business-specific considerations, how to create and use a database that's efficient and useful, and how to write SQL. I'm looking forward to my next database project!

If you'd like to see the full documentation for this project, feel free to view it here!