Getting my hands dirty in database design, or at least trying to as this is a brand new concept for me. But I am learning on the go. I have read recently a few articles on database normalization and relational databases.
In my first attempt to design a database about my favorite video games of all time and their consoles, I came up with this:
I was not happy with this table because it is too repetitive. In order to reduce redundancies, I improved the table a bit:
Heh… I did not like the console column denoting multiple items, either. What if I wanted to find out all my favorite games from SNES? Or what if I wanted to add games that had more than two consoles. This smelled like a maintenance nightmare.
Somewhat better, but not totally satisfied as I would still have to search by each row in order to find all games for a particular console. It was then that I read “Beginning Database Design” by Clare Thunder and realized that I could create two separate tables for games and consoles and assign items in those tables an id so they can be easily referenced.
By separating out the data into two different tables, Game Table and Console Table, I can find much more effectively by name of console or games. The table may be further redundancy improved but my point is that one can improve search speed by making iterative adjustments to the data. This database can also be scaled better.
And that’s all folks.