Database Design- My First Attempt

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:
Screen Shot 2014-01-03 at 1.38.51 PM

I was not happy with this table because it is too repetitive. In order to reduce redundancies, I improved the table a bit:
Screen Shot 2014-01-03 at 1.43.18 PM

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.

After some tweak, the design started to look thus:
Screen Shot 2014-01-03 at 1.47.13 PM

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.

Game table
Screen Shot 2014-01-03 at 1.49.59 PM

Console Table
Screen Shot 2014-01-03 at 1.52.51 PM

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s